SQL Server統(tǒng)計信息更新時采樣百分比對數(shù)據(jù)預估準確性的影響詳解

為什么要寫統(tǒng)計信息

最近看到園子里有人寫統(tǒng)計信息，樓主也來湊熱鬧。

話說經(jīng)常做數(shù)據(jù)庫的，尤其是做開發(fā)的或者優(yōu)化的，統(tǒng)計信息造成的性能問題應該說是司空見慣。

當然解決辦法也并非一成不變，“一招鮮吃遍天”的做法已經(jīng)行不通了（題外話：整個時代不都是這樣子嗎）

當然，還是那句話，既然寫了就不能太俗套，寫點不一樣的，本文通過分析一個類似實際案例來解讀統(tǒng)計信息的更新的相關問題。

對于實際問題，不但要解決問題，更重要的是要從理論上深入分析，才能更好地駕馭數(shù)據(jù)庫。

何時更新統(tǒng)計信息

（1）查詢執(zhí)行緩慢，或者查詢語句突然執(zhí)行緩慢。這種場景很可能是由于統(tǒng)計信息沒有及時更新而遭遇了參數(shù)嗅探的問題。

（2）當大量數(shù)據(jù)更新（INSERT/DELETE/UPDATE）到升序或者降序的列時，這種情況下，統(tǒng)計信息直方圖可能沒有及時更新。

（3）建議在除索引維護（當你重建、整理碎片或者重組索引時，數(shù)據(jù)分布不會改變）外的維護工作之后更新統(tǒng)計信息。

（4）數(shù)據(jù)庫的數(shù)據(jù)更改頻繁，建議最低限度每天更新一次統(tǒng)計信息。數(shù)據(jù)倉庫可以適當降低更新統(tǒng)計信息的頻率。

（5）當執(zhí)行計劃出現(xiàn)統(tǒng)計信息缺失警告時，需要手動建立統(tǒng)計信息

統(tǒng)計信息基礎

首先說一個老掉牙的話題，統(tǒng)計信息的更新閾值：

1，表格從沒有數(shù)據(jù)變成有大于等于1條數(shù)據(jù)。

2，對于數(shù)據(jù)量小于500行的表格，當統(tǒng)計信息的第一個字段數(shù)據(jù)累計變化量大于500以后。

3，對于數(shù)據(jù)量大于500行的表格，當統(tǒng)計信息的第一個字段數(shù)據(jù)累計變化量大于500 + (20%×表格數(shù)據(jù)總量)以后。

做個查詢，觸發(fā)統(tǒng)計信息更新，rowmodct歸0（繼續(xù)累積直到下一個觸發(fā)的閾值，觸發(fā)更新之后再次歸0）

關于統(tǒng)計信息“過期”的問題

下面開始正文，網(wǎng)絡上很多關于統(tǒng)計信息的文章，提到統(tǒng)計信息，很多都是統(tǒng)計信息過期的問題，然后跟新之后怎么怎么樣

尤其在觸發(fā)統(tǒng)計信息自動更新閾值的第三個區(qū)間：也就是說數(shù)據(jù)累計變化超過20%之后才能自動觸發(fā)統(tǒng)計信息的更新

這一點對于大表來說通常影響是比較大的，比如1000W的表，變化超過20%也+500也就是200W+500行之后才觸發(fā)統(tǒng)計信息更新，這個閾值區(qū)間的自動觸發(fā)閾值，絕大多數(shù)情況是不能接受的，于是對于統(tǒng)計信息的診斷就變成了是否“過期”

判斷統(tǒng)計信息是否過期，然后通過更新統(tǒng)計信息來促使執(zhí)行計劃更加準確地預估行數(shù)，這一點本無可厚非

但是，問題也就出在這里了：那么怎么更新統(tǒng)計信息？一成不變的做法是否可行，這才是問題的重點。

當然肯定有人說，我就是按照默認方式更新的，更新完之后SQL也變得更加優(yōu)化了什么的

通過update statistics TableName StatisticName更新某一個索引的統(tǒng)計信息，

或者update statistics TableName更新全表的統(tǒng)計信息

這種情況下往往是小表上可以這么做，當然對于大表或者小表沒有一個標準值，一切要結合事實來說明問題

下面開始本文的主題：

抽象并簡化出業(yè)務中的一個實際案例，創(chuàng)建這么一張表，類似于訂單和訂單明細表（主子表），

這里你可以想象成是一個訂單表的子表，Id字段是唯一的，有一個ParentID字段，是非唯一的，

ParentID類似于主表的Id，測試數(shù)據(jù)按照一個主表Id對應50條子表明細的規(guī)律插入數(shù)據(jù)

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


declare @i int=0
while(@i100000000)
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億條的，中間我讓他執(zhí)行我睡午覺去了，醒來之后發(fā)現(xiàn)SSMS掛掉了，掛掉了算了，數(shù)據(jù)也接近1億了，能說明問題就夠了

現(xiàn)在數(shù)據(jù)分布的非常明確，就是一個ParentId有50條數(shù)據(jù)，這一點首先要澄清。

測試數(shù)據(jù)寫入，以及所創(chuàng)建完成之后來更新idx_ParentId 索引上的統(tǒng)計信息，就按照默認的方式來更新，然后來觀察統(tǒng)計信息

默認方式更新統(tǒng)計信息（未指定采樣密度）

表里現(xiàn)在是8000W多一點記錄，默認更新統(tǒng)計信息時取樣行數(shù)是462239行，那么這個統(tǒng)計信息靠譜嗎？

上面說了，造數(shù)據(jù)的時候，我一個ParentId對應的是50行記錄，這一點非常明確，他這里統(tǒng)計出來的多少？

1，對于取樣的RANG_HI_Key值，比如51632，預估了862.212行

2，對于AVG_RANG_ROW，比如45189到51632之間的每個Id的數(shù)據(jù)對應的數(shù)據(jù)行，預估是6682.490行

之前造數(shù)據(jù)的時候每個Id都是50行，這里的預估靠譜嗎，這個誤差是無法接受的，

很多時候，對于大表，采用默認（未指定采樣密度）的情況下，默認的采樣密度并不足以準確地描述數(shù)據(jù)分布情況

指定一個采樣密度的方式更新統(tǒng)計信息（20%采樣）

這一次用20%的采樣密度，可以看到取樣的行數(shù)是15898626行

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

2，對于AVG_RANG_ROW，比如186302到216305之間的每個Id的行數(shù)，預估是197.4439行

觀察比如上面默認的取樣密度，這一次不管是RANG_HI_Key還是AVG_RANG_ROW得預估，都有不一個非常高的下降，開始趨于接近于真實的數(shù)據(jù)分布（每個Id有50行數(shù)據(jù)）

整體上看，但是這個誤差還是比較大的，如果繼續(xù)提高采樣密度，看看有什么變化？

指定一個采樣密度的方式更新統(tǒng)計信息（70%采樣）　

這一次用70%的采樣密度，可以看到取樣行數(shù)是55962290行

1，對于取樣的RANG_HI_Key值，比如1978668，預估了71.15906行

2，對于AVG_RANG_ROW，比如1124024到1978668之間的每個Id，預估為61.89334行

可以說，對于絕大多數(shù)值得預估（AVG_RANG_ROW），都愈發(fā)接近于真實值

指定一個采樣密度的方式更新統(tǒng)計信息（100%采樣）

可以看到，取樣行數(shù)等于總行數(shù)，也就是所謂的全部（100%）取樣

看一下預估結果：

比如Id=3981622，預估是50行，3981622與4131988之間的Id的行數(shù)，預估為49.99874行，基本上等于真實數(shù)據(jù)分布

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

取樣密度高低與統(tǒng)計信息準確性的關系

至于為什么默認取樣密度和較低取樣密度情況下，誤差很大的情況我簡單解釋一下，也非常容易理解，因為“子表”中存儲主表ID的ParentId值允許重復，在存在重復值的情況下，如果采樣密度不夠，極有可能造成“以偏概全”的情況

比如對10W行數(shù)據(jù)取樣1W行，原本10W行數(shù)劇中有2000個不重復的ParentId值，如果是10%的取樣，在1W行取樣數(shù)據(jù)中，因為密度不夠大，只找到了20個不重復的ParentId值，那么就會認為每一行ParentId對應500行數(shù)據(jù)，這根實際的分布的每個ParentId有一個非常大的誤差范圍

如果提高采樣密度，那么這個誤差就會越來越小?！　?/p>

更新統(tǒng)計信息的時候，高比例的取樣是否可取（可行）　

因此在觀察統(tǒng)計信息是否過期，決定更新統(tǒng)計信息的時候，一定要注意取樣的密度，就是說表中有多少行數(shù)據(jù)，統(tǒng)計信息更新的時候取了多少采樣行，密度有多高。

當然，肯定有人質疑，那你說采樣密度越高，也就是取樣行數(shù)越高越準確，那么我就100%取樣。

這樣行不行？

還要分情況看，對于幾百萬或者十幾萬的小表來說，當然沒有問題，這也是為什么數(shù)據(jù)庫越小，表數(shù)據(jù)越少越容易掩蓋問題的原因。

對于大表，上億的，甚至是十幾億的，你按照100%采樣試一試？　

舉個實際例子：

我這里對一個稍微大一點的表做個全表統(tǒng)計信息的更新，測試環(huán)境，服務器沒負載，存儲是比普通的機械硬盤要強很多的SAN存儲

采用full scan，也就是100%采樣的更新操作，看一下，僅僅這一樣表的update statistic操作就花費了51分鐘

試想一下，對一個數(shù)百GB甚至數(shù)TB的庫來說，你敢這么搞一下。

扯一句，這個中秋節(jié)過的，折騰了大半天，話說做測試過程中電腦有開始有點卡，

做完測試之后停掉SQLServer服務，瞬間內存釋放了7個G，可見這些個操作還是比較耗內存的

總結：

本文通過對于某些場景下，在對較大的表的索引統(tǒng)計信息更新時，采樣密度的分析，闡述了不同采樣密度下，對統(tǒng)計信息預估的準確性的影響。

當然對于小表，一些都好說。

隨著單表數(shù)據(jù)量的增加，統(tǒng)計信息的更新策略也要做相應的調整，

不光要看統(tǒng)計信息是否“過期”，更重要的是注意統(tǒng)計信息更新時究竟取樣了全表的多少行數(shù)據(jù)做統(tǒng)計。

對于大表，采用FULL SCAN或者100%采樣往往是不可行的，這時候就需要做出權衡，做到既能準確地預估，又能夠以合理的代價執(zhí)行。

好了，以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持