SQL Server 統計信息更新時採樣百分比對數據預估準確性的影響

為什麼要寫統計信息最近看到園子里有人寫統計信息，樓主也來湊熱鬧。話說經常做資料庫的，尤其是做開發的或者優化的，統計信息造成的性能問題應該說是司空見慣。當然解決辦法也並非一成不變，“一招鮮吃遍天”的做法已經行不通了（題外話：整個時代不都是這樣子嗎）當然，還是那句話，既然寫了就不能太俗套，寫點不 ...

為什麼要寫統計信息

　　最近看到園子里有人寫統計信息，樓主也來湊熱鬧。
　　話說經常做資料庫的，尤其是做開發的或者優化的，統計信息造成的性能問題應該說是司空見慣。
　　當然解決辦法也並非一成不變，“一招鮮吃遍天”的做法已經行不通了（題外話：整個時代不都是這樣子嗎）
　　當然，還是那句話，既然寫了就不能太俗套，寫點不一樣的，本文通過分析一個類似實際案例來解讀統計信息的更新的相關問題。
　　對於實際問題，不但要解決問題，更重要的是要從理論上深入分析，才能更好地駕馭資料庫。

統計信息基礎

首先說一個老掉牙的話題，統計信息的更新閾值：
1，表格從沒有數據變成有大於等於1條數據。
2，對於數據量小於500行的表格，當統計信息的第一個欄位數據累計變化量大於500以後。
3，對於數據量大於500行的表格，當統計信息的第一個欄位數據累計變化量大於500 + (20%×表格數據總量)以後。

觸發統計信息後，rowmodct歸0

關於統計信息“過期”的問題

下麵開始正文，網路上很多關於統計信息的文章，提到統計信息，很多都是統計信息過期的問題，然後跟新之後怎麼怎麼樣
尤其在觸發統計信息自動更新閾值的第三個區間：也就是說數據累計變化超過20%之後才能自動觸發統計信息的更新
這一點對於大表來說通常影響是比較大的，比如1000W的表，變化超過20%也+500也就是200W+500行之後才觸發統計信息更新，
這個閾值區間的自動觸發閾值，絕大多數情況是不能接受的，於是對於統計信息的診斷就變成了是否“過期”

判斷統計信息是否過期，然後通過更新統計信息來促使執行計劃更加準確地預估行數，這一點本無可厚非
但是，問題也就出在這裡了：那麼怎麼更新統計信息？一成不變的做法是否可行，這才是問題的重點。
當然肯定有人說，我就是按照預設方式更新的，更新完之後SQL也變得更加優化了什麼的
通過update statistics TableName StatisticName更新某一個索引的統計信息，
或者update statistics TableName更新全表的統計信息
這種情況下往往是小表上可以這麼做，當然對於大表或者小表沒有一個標準值，一切要結合事實來說明問題

下麵開始本文的主題：

抽象並簡化出業務中的一個實際案例，創建這麼一張表，類似於訂單和訂單明細表（主子表），
這裡你可以想象成是一個訂單表的子表，Id欄位是唯一的，有一個ParentID欄位，是非唯一的，
ParentID類似於主表的Id，測試數據按照一個主表Id對應50條子表明細的規律插入數據

CREATE TABLE [dbo].[TestStaitisticsSample](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [OtherColumn] [varchar](50) NULL
) 


declare @i int=0
while(@i<100000000)
begin

    insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID())
    /*
    中間插入50條，也即一個主表Id對應50條子表明細
    */
    insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID())

    set @i=@i+1
end
go

create nonclustered index [idx_ParentId] ON [dbo].[TestStaitisticsSample]
(
    [ParentId] 
)
go

本來打算插入1億條的，中間我讓他執行我睡午覺去了，醒來之後發現SSMS掛掉了，掛掉了算了，數據也接近1億了，能說明問題就夠了
現在數據分佈的非常明確，就是一個ParentId有50條數據，這一點首先要澄清。

測試數據寫入，以及所創建完成之後來更新 idx_ParentId 索引上的統計信息，就按照預設的方式來更新，然後來觀察統計信息

預設方式更新統計信息（未指定採樣密度）

表裡現在是8000W多一點記錄，預設更新取樣時462239行，那麼這個統計信息靠譜嗎？

上面說了，造數據的時候，我一個ParentId對應的是50行記錄，這一點非常明確，他這裡給我統計出來的多少？

1，對於取樣的RANG_HI_Key值，比如51632，他給我預估了862.212行

2，對於AVG_RANG_ROW，比如45189到51632之間，他給我預估了6682.490行

這靠譜嗎，這個誤差是無法接受的，很多時候，對於大表，採用預設（未指定採樣密度）的情況下，預設的採樣密度並不足以準確地描述數據分佈情況

指定一個採樣密度的方式更新統計信息（20%採樣）

這一次用20%的採樣密度，可以看到取樣時15898626行

1，對於取樣的RANG_HI_Key值，比如216305，他給我預估了24.9295行

2，對於AVG_RANG_ROW，比如186302到216305之間，他給我預估了197.4439行

觀察比如上面預設的取樣密度，這一次不管是RANG_HI_Key還是AVG_RANG_ROW得預估，都有不一個非常高的下降，趨於接近於真實的數據分佈（50行）

但是這個誤差還是比較大的，如果繼續提高採樣密度，看看有什麼變化？

指定一個採樣密度的方式更新統計信息（70%採樣）　

這一次用70%的採樣密度，可以看到取樣是55962290行

1，對於取樣的RANG_HI_Key值，比如1978668，他給我預估了71.15906行

2，對於AVG_RANG_ROW，比如1124024到1978668之間，他給我預估了61.89334行

可以說，對於絕大多數值得預估（AVG_RANG_ROW），都愈發接近於真實值

指定一個採樣密度的方式更新統計信息（100%採樣）

這個就不做過多解釋了，基本上跟真實值是一樣的，只是AVG_RANG_ROW有一點非常非常小的誤差。

取樣密度高低與統計信息準確性的關係

　　至於為什麼預設取樣密度和較低取樣密度情況下，誤差很大的情況我簡單解釋一下，也非常容易理解，
　　因為“子表”中存儲主表ID的ParentId值允許重覆，在存在重覆值的情況下，如果採樣密度不夠，極有可能造成“以偏概全”的情況
　　比如對10W行數據取樣1W行，原本10W行數劇中有2000個不重覆的ParentId值，
　　如果是10%的取樣，在1W行取樣數據中，因為密度不夠大，只找到了20個不重覆的ParentId值，
　　那麼就會認為每一行ParentId對應500行數據，這根實際的分佈的每個ParentId有一個非常大的誤差範圍
　　如果提高採樣密度，那麼這個誤差就會越來越小。
　　

　　因此在觀察統計信息是否過期，決定更新統計信息的時候，一定要註意取樣的密度，
　　就是說表中有多少行數據，統計信息更新的時候取了多少採樣行，密度有多高。
　　當然，肯定有人質疑，那你說採樣密度越高，也就是取樣行數越高越準確，那麼我就100%取樣。
　　這樣行不行？
　　還要分情況看，對於幾百萬或者十幾萬的小表來說，當然沒有問題，這也是為什麼資料庫越小，表數據越少越容易掩蓋問題的原因。
　　對於大表，上億的，甚至是十幾億的，你按照100%採樣試一試？
　　

　　舉個實際例子：

　　我這裡對一個稍微大一點的表做個全表統計信息的更新，測試環境，伺服器沒負載，存儲是比普通的機械硬碟要強很多的SAN存儲
　　採用full scan，也就是100%採樣的更新操作，看一下，僅僅這一樣表的update statistic操作就花費了51分鐘
　　試想一下，對一個數百GB甚至數TB的庫來說，你敢這麼搞一下。