RiCo技術農場

SQL Server以前版本都會遇到維護作業後基數估計不準確問題([SQL SERVER]統計值能吃嗎?)

基數估計不準確將導致選錯運算子，好比應該選用索引搜尋而非耗資源的索引掃描，

SQL2014透過新基數演算法來改善此問題，讓開發人員可以更專注在商業邏輯上，

下面我使用SQL2014簡單測試

 

--add 373 rows
set IDENTITY_INSERT SalesOrderDetail on

INSERT INTO dbo.SalesOrderDetail(SalesOrderID, SalesOrderDetailID,CarrierTrackingNumber
,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate) 
SELECT SalesOrderID, SalesOrderDetailID,CarrierTrackingNumber
,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate
FROM Sales.SalesOrderDetail 
WHERE ModifiedDate < '2005-07-06 00:00:00.000';

set IDENTITY_INSERT SalesOrderDetail off

CREATE INDEX idx5 ON SalesOrderDetail(ModifiedDate)


SELECT * 
FROM dbo.SalesOrderDetail 
WHERE ModifiedDate = '2005-07-05 00:00:00.000';

--觀察統計值  
DBCC SHOW_STATISTICS ('dbo.SalesOrderDetail', 'idx5')

該索引資料採樣率100%

 

--新增資料 add 53 rows
set IDENTITY_INSERT SalesOrderDetail on

INSERT INTO dbo.SalesOrderDetail(SalesOrderID, SalesOrderDetailID,CarrierTrackingNumber
,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate) 
SELECT SalesOrderID, SalesOrderDetailID,CarrierTrackingNumber
,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate
FROM Sales.SalesOrderDetail 
WHERE ModifiedDate = '2008-07-04 00:00:00.000';

set IDENTITY_INSERT SalesOrderDetail off

這次條件實際資料列筆數總共53，

可以看到SQL2014查詢優化程式在索引搜尋運算子上估計資料列數目相當接近。

 

--新增資料 add 52 rows
delete SalesOrderDetail  WHERE ModifiedDate ='2008-07-21 00:00:00.000';
set IDENTITY_INSERT SalesOrderDetail on

INSERT INTO dbo.SalesOrderDetail(SalesOrderID, SalesOrderDetailID,CarrierTrackingNumber
,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate) 
SELECT SalesOrderID, SalesOrderDetailID,CarrierTrackingNumber
,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate
FROM Sales.SalesOrderDetail 
WHERE ModifiedDate = '2008-07-21 00:00:00.000';

set IDENTITY_INSERT SalesOrderDetail off

這次新增52筆，估計資料列數目也相當接近，完全不用手動介入更新統計值。