共計 4119 個字符，預計需要花費 11 分鐘才能閱讀完成。
行業資訊
數據庫
SQL Server 統計信息更新時采樣百分比對數據預估準確性的影響有哪些
SQL Server 統計信息更新時采樣百分比對數據預估準確性的影響有哪些，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。
為什么要寫統計信息
最近看到園子里有人寫統計信息，樓主也來湊熱鬧。
話說經常做數據庫的，尤其是做開發的或者優化的，統計信息造成的性能問題應該說是司空見慣。
當然解決辦法也并非一成不變，“一招鮮吃遍天”的做法已經行不通了（題外話：整個時代不都是這樣子嗎）
當然，還是那句話，既然寫了就不能太俗套，寫點不一樣的，通過分析一個類似實際案例來解讀統計信息的更新的相關問題。
對于實際問題，不但要解決問題，更重要的是要從理論上深入分析，才能更好地駕馭數據庫。
何時更新統計信息
（1）查詢執行緩慢，或者查詢語句突然執行緩慢。這種場景很可能是由于統計信息沒有及時更新而遭遇了參數嗅探的問題。
（2）當大量數據更新（INSERT/DELETE/UPDATE）到升序或者降序的列時，這種情況下，統計信息直方圖可能沒有及時更新。
（3）建議在除索引維護（當你重建、整理碎片或者重組索引時，數據分布不會改變）外的維護工作之后更新統計信息。
（4）數據庫的數據更改頻繁，建議最低限度每天更新一次統計信息。數據倉庫可以適當降低更新統計信息的頻率。
（5）當執行計劃出現統計信息缺失警告時，需要手動建立統計信息
統計信息基礎
首先說一個老掉牙的話題，統計信息的更新閾值：
1，表格從沒有數據變成有大于等于 1 條數據。
2，對于數據量小于 500 行的表格，當統計信息的第一個字段數據累計變化量大于 500 以后。
3，對于數據量大于 500 行的表格，當統計信息的第一個字段數據累計變化量大于 500 + (20%×表格數據總量) 以后。
做個查詢，觸發統計信息更新，rowmodct 歸 0（繼續累積直到下一個觸發的閾值，觸發更新之后再次歸 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=0while(@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+1endgocreate 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 之間的每個 Id 的數據對應的數據行，預估是 6682.490 行
之前造數據的時候每個 Id 都是 50 行，這里的預估靠譜嗎，這個誤差是無法接受的，
很多時候，對于大表，采用默認（未指定采樣密度）的情況下，默認的采樣密度并不足以準確地描述數據分布情況
指定一個采樣密度的方式更新統計信息（20% 采樣）
這一次用 20% 的采樣密度，可以看到取樣的行數是 15898626 行
1，對于取樣的 RANG_HI_Key 值，比如 216305，他給我預估了 24.9295 行
2，對于 AVG_RANG_ROW，比如 186302 到 216305 之間的每個 Id 的行數，預估是 197.4439 行
觀察比如上面默認的取樣密度，這一次不管是 RANG_HI_Key 還是 AVG_RANG_ROW 得預估，都有不一個非常高的下降，開始趨于接近于真實的數據分布（每個 Id 有 50 行數據）
整體上看，但是這個誤差還是比較大的，如果繼續提高采樣密度，看看有什么變化？
指定一個采樣密度的方式更新統計信息（70% 采樣）
這一次用 70% 的采樣密度，可以看到取樣行數是 55962290 行
1，對于取樣的 RANG_HI_Key 值，比如 1978668，預估了 71.15906 行
2，對于 AVG_RANG_ROW，比如 1124024 到 1978668 之間的每個 Id，預估為 61.89334 行
可以說，對于絕大多數值得預估（AVG_RANG_ROW），都愈發接近于真實值
指定一個采樣密度的方式更新統計信息（100% 采樣）
可以看到，取樣行數等于總行數，也就是所謂的全部（100%）取樣
看一下預估結果：
比如 Id=3981622，預估是 50 行，3981622 與 4131988 之間的 Id 的行數，預估為 49.99874 行，基本上等于真實數據分布
這個就不做過多解釋了，基本上跟真實值是一樣的，只是 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 的庫來說，你敢這么搞一下。
扯一句，這個中秋節過的，折騰了大半天，話說做測試過程中電腦有開始有點卡，
做完測試之后停掉 SQLServer 服務，瞬間內存釋放了 7 個 G，可見這些個操作還是比較耗內存的
通過對于某些場景下，在對較大的表的索引統計信息更新時，采樣密度的分析，闡述了不同采樣密度下，對統計信息預估的準確性的影響。
當然對于小表，一些都好說。
隨著單表數據量的增加，統計信息的更新策略也要做相應的調整，不光要看統計信息是否“過期”，更重要的是注意統計信息更新時究竟取樣了全表的多少行數據做統計。
對于大表，采用 FULL SCAN 或者 100% 采樣往往是不可行的，這時候就需要做出權衡，做到既能準確地預估，又能夠以合理的代價執行。
看完上述內容，你們掌握 SQL Server 統計信息更新時采樣百分比對數據預估準確性的影響有哪些的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！
