[SQL SERVER]統計值能吃嗎?
2013年3月我說的查詢調校不求人總結如下圖
統計值在資料庫扮演很重要的角色，統計值記載資料分布、數量..等資訊，因此會影響執行計畫的好壞，
所以我個人認為執行SQL Tuning過程中，如果沒去評估統計值所帶來的影響，
那就好比使用asp.net mvc專案開發，就真的以為在寫MVC了..
anyway~~我簡單模擬一下前幾天幫朋友調校的過程，這過程你應該可以體會統計值的重要性。
朋友問: 每當我新增一些條件資料後，然後再用該條件查詢該索引，
前面幾次的查詢效能總是很差，但奇怪的是，大概第5次查詢後
，後面所執行的查詢計畫又很好，但也不是一直很固定5次後就會變好
，有時候第一次查詢計畫也很好，有時候卻是第二次查詢後才會變好，
這是SQL2012的bug嗎?
我回:你只有單看執行計畫成本，那你有沒有注意統計值呢?
朋友問:統計值那是啥?能吃嗎?
我回:統計值很重要的....
--建立測試資料表(add 5151 rows)
select * into SalesOrderDetail from Sales.SalesOrderDetail
WHERE ModifiedDate < '2006-01-01 00:00:00.000'
CREATE INDEX idx1 ON SalesOrderDetail(ModifiedDate)
include(CarrierTrackingNumber)
--查詢SQL
SELECT * FROM SalesOrderDetail t
WHERE t.ModifiedDate = '2005-07-02 00:00:00.000'
這裡可以看到實際資料列和估計相同，
這表示SQL SERVER給查詢最佳化一個很讚統計值(符合實際資料，才能選擇更合適執行計畫)。
--新增資料(add 4 rows)
set IDENTITY_INSERT SalesOrderDetail on
INSERT INTO SalesOrderDetail
(
SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID
, UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate
)
SELECT *
FROM Sales.SalesOrderDetail
WHERE ModifiedDate = '2006-01-02 00:00:00.000'
set IDENTITY_INSERT SalesOrderDetail off
--查詢SQL
SELECT * FROM SalesOrderDetail t
WHERE t.ModifiedDate = '2006-01-02 00:00:00.000'
你可以看到只是查詢條件不同，但這次實際資料列卻和估計資料數目(只有1筆)不同，這是SQL2012 bug???
可以想像一下，如果你實際查詢資料有2000筆，但估計資料只有1筆，
這樣可想而知執行計畫一定很差(查詢效能也跑不快)，因為統計值導致查詢最佳化程式選擇不合適的執行計畫。
下面我將使用DBCC SHOW_STATISTICS 讓你知道這並非bug
DBCC TRACEON (2388) DBCC TRACEON (2389)
可以看到目前資料表總筆數只有5151rows，Rows Above is null，Leading column type =Unknown。
我手動執行一下更新統計值來看看會有什麼樣的結果
UPDATE STATISTICS dbo.SalesOrderDetail WITH FULLSCAN
可以看到我第二次新增的資料終於讓SQL SERVER知道了。
在執行一次相同SQL
實際資料列數目和估計資料列數目又相同了。
接下來我繼續新增其他條件資料，並手動更新統計值
--Add 7 rows
set IDENTITY_INSERT SalesOrderDetail on
INSERT INTO SalesOrderDetail
(
SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID
, UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate
)
SELECT *
FROM Sales.SalesOrderDetail
WHERE ModifiedDate = '2006-01-31 00:00:00.000'
set IDENTITY_INSERT SalesOrderDetail off
UPDATE STATISTICS dbo.SalesOrderDetail WITH FULLSCAN
DBCC SHOW_STATISTICS ('dbo.SalesOrderDetail', 'idx1')
Leading column type 還是顯示Unknown(這表示SQL SERVER無法給你比較正確的統計值
，你還是得繼續手動更新統計值)。
--Add 10 rows
set IDENTITY_INSERT SalesOrderDetail on
INSERT INTO SalesOrderDetail
(
SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID
, UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate
)
SELECT *
FROM Sales.SalesOrderDetail
WHERE ModifiedDate = '2006-02-10 00:00:00.000'
set IDENTITY_INSERT SalesOrderDetail off
UPDATE STATISTICS dbo.SalesOrderDetail WITH FULLSCAN
DBCC SHOW_STATISTICS ('dbo.SalesOrderDetail', 'idx1')
在我們手動執行三次更新統計值後，Leading column type有了變化，這次顯示Ascending而不是Unknown。
--Add 6 rows
set IDENTITY_INSERT SalesOrderDetail on
INSERT INTO SalesOrderDetail
(
SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID
, UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate
)
SELECT *
FROM Sales.SalesOrderDetail
WHERE ModifiedDate = '2006-02-19 00:00:00.000'
set IDENTITY_INSERT SalesOrderDetail off
--查詢SQL
SELECT * FROM SalesOrderDetail t
WHERE t.ModifiedDate ='2006-02-19 00:00:00.000'
這次我沒手動更新統計值，但SQL SERVER卻能返回準確估計的資料列??
主要是因為我們執行了三次手動更新，讓SQL SERVER有了行為上的依據，
所以Leading column type也跟者改變，這改變會讓SQL SERVER取得比較好的統計值，
而非永遠都是1(因為Leading column type=Unknown，所以SQL SERVER就統一返回1)，
下面我在新增資料並驗證估計資料列數目是否會更接近實際資料列數目。
--add 34 rows
set IDENTITY_INSERT SalesOrderDetail on
INSERT INTO SalesOrderDetail
(
SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID
, UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate
)
SELECT *
FROM Sales.SalesOrderDetail
WHERE ModifiedDate = '2007-07-23 00:00:00.000'
set IDENTITY_INSERT SalesOrderDetail off
--查詢SQL
SELECT * FROM SalesOrderDetail t
WHERE t.ModifiedDate ='2007-07-23 00:00:00.000'
這次我依然沒手動更新任何統計值，但SQL SERVER估計的資料列數目不在為1，
而是更接近實際資料列數目的28.1087，並且執行計畫直接採取Full Scan，
而不在使用之前的RID Lookup+loop join(該執行計畫I/O較多)，
這一整個行為上的改變，形同查詢效能改善。
而這問題SQL BOL也老早就寫出來了(擷取如下)
參考
[SQL SERVER][Performance]密度和選擇性
