統計資訊，對於效能非常重要。
但能人為參與的部分比較少，只有一些地方可以去做到干涉
所以主要還是去懂它的原理
這邊會基礎介紹統計資訊，效能調教的時候會再次具體演示一遍怎麼用，這邊沒辦法演示是因為還需要牽涉到擴充事件、優化器、執行計畫等等。
但可以事先說一下為什麼會重要，任何查詢從頗析到最後一部產生執行計畫執行，都會經過一個固定的流程，這個流程裡面有一個步驟叫做估計資料列，SQL Server 必須有這個估計才有辦法去判斷說要用哪一種執行計畫效率最好，而要去估計的數據來源就是這個統計資訊，至於到底怎麼估的，怎要叫做估的好估的爛，會在效能調教篇章講解。
所以現在只需要先知道，統計資訊這個平常看不見的東西他到底是什麼。
Cardinality 指的是查詢優化器預期某個查詢會回傳多少 rows。這是優化器為指定查詢選擇最佳執行計畫時的一個關鍵因素。優化器用來估算 cardinality 的主要方法，就是 statistics。
SQL Server 會維護 statistics，statistic 是用來描述某個 column 或一組 columns 內部資料的分布情況。這些 columns 可以位於 table 裡，也可以位於 nonclustered index 裡。
當 statistics 是建立在一組 columns 上時，statistics 也會包含這些 columns 之間資料分布的 correlation statistics。接著，查詢優化器就可以根據它預期查詢會回傳的 rows 數量，也就是 cardinality，使用這些 statistics 建立有效率的執行計畫。
如果缺乏 statistics，可能會導致產生效率不佳的 plan。舉例來說，Query Optimizer 可能會決定執行 index scan，但實際上使用 seek operation 會更有效率。
可以允許 SQL Server 自動管理 statistics。資料庫層級的選項 AUTO_CREATE_STATISTICS 會在 SQL Server 認為建立 single-column statistics 可以幫助產生更好的 cardinality estimate、進而改善查詢效能時，自動建立 single-column statistics。
不過，這個功能有一些限制。例如，filtered statistics 或 multicolumn statistics 無法被自動建立。
唯一的例外是建立 index 的時候。
當建立 index 時，SQL Server 一定會產生 statistics，用來涵蓋 index key。即使是 multicolumn statistics 也會建立。
如果是 filtered index，也會包含 filtered statistics。
這個行為不受 AUTO_CREATE_STATS 設定影響。
這東西很有用，非常有用，建議啟用。
但是用這個 process 會產生一個問題 : 他是同步執行，而且會 blocking。
因此，當某個 query 執行時，SQL Server 會檢查 statistics 是否需要更新。如果需要，SQL Server 會更新 statistics；但這會 blocking 該 query，以及其他任何需要相同 statistics 的 query，直到 更新完成為止。
在高讀寫的時候，例如對一張大表執行 ETL process，就會造成效能瓶頸。
解決方法是使用另一個資料庫層級的設定，叫做 AUTO_UPDATE_STATISTICS_ASYNC。
即使這個選項被啟用，它也只有在 AUTO_UPDATE_STATISTICS 同時啟用時才會生效。
啟用 AUTO_UPDATE_STATS_ASYNC 後，statistics object 的更新會改成以非同步後台執行。
這表示觸發 statistics 更新的 query，以及其他 queries，不會被 blocking；前提是該過程不需要 schema stability lock。
不過，這個做法的 trade-off 是：這些 queries 不會受益於已更新後的 statistics。
前面提到的這些選項，可以使用 ALTER DATABASE commands 來設定。
SQL Server 2022 加入了一個新的 database 參數，可以避免 blocking 其他需要 schema stability lock 的交易。
schema staility lock 是個結構鎖，普通的 DML 指令他不會鎖，鎖的是 DDL 指令。
有關鎖的內容會再開一篇去寫。
ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY 選項的運作方式是：將 lock request 放入 low priority queue。只有在該 database 已經啟用 AUTO_UPDATE_STATISTICS 時，這個選項才會產生效果。
從前面的說明已經知道更新統計資訊的時候會發生鎖
鑑於這層原因所以可以開啟 AUTO_UPDATE_STATISTICS_ASYNC，這會讓這個統計更新發生的鎖變成
但問題是，即使已經這麼做了，在後台更新統計資訊了，SQL Server 終究還是要把統計資訊寫回 database，那寫回 database 的時候就要取得 Sch-M 鎖。
那 Sch-M 是一個強的鎖，而且會跟 Sch-S 衝突，就會發生以下這種狀況
Session A：background statistics update，正在等 Sch-M lock
Session B：query compile，需要 Sch-S lock
Session C：query compile，需要 Sch-S lock
Session D：query compile，需要 Sch-S lock
如果 Session A 排在前面等 Sch-M，後面的 Sch-S 就通通被卡住。
我們原本打開 AUTO_UPDATE_STATISTICS_ASYNC ，那麼 Session 就不應該被這個統計資訊更新卡住，因為我們已經開過了，但那是在單一 Session 下，會直接先跳過更新，而現在其他被卡住的 Session 確實也是跳過更新，但是他們是被正在更新的 Sch-M 卡住。
這在頻繁查詢 & 更新統計資訊的工作負載下，async statistics update 會增加鎖的機率。
Sch-S 鎖是，要穩定的讀取 / 使用這個物件結構的鎖，因為只是要讀取，所以確保這時候不會有人來修改這個物件，所以加一個這個鎖防止 DDL 語法去修改這個物件。
Sch-M 鎖是，要修改這個物件結構的鎖，因為是牽扯到修改物件，必須確保沒有人正在使用舊結構，所以會更排他。
因此 Sch-M 會比 Sch-S 還要強。
而 SQL Server 2022 以後，多了一個 ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY 來解決這個問題。
這個功能是，當後台統計更新要去拿 Sch-M 鎖時，不要跟一般的查詢去搶優先權的 lock quene，而是先去旁邊等。
Filtered statisti cs 是針對部分資料建立的 statistics。建立時可以使用 WHERE clause 限定 statistics 涵蓋的資料範圍。因為 statistics 只記錄特定 subset 內的資料值分布，所以 Query Optimizer 在處理符合該篩選條件的 query 時，可以取得更精準的 cardinality estimate，進而產生更好的 execution plan。
例如，在 OrdersDisk table 的 NetAmount column 上建立 filtered statistics，並且條件設定為 OrderDate > '2019-01-01'，則該 statistics 只會涵蓋 2019 年 1 月 1 日之後的訂單，不會包含較舊的 orders。這對查詢 recent orders 或 large recent orders 這類資料分布不均的查詢特別有幫助。
這東西可以降低大型分割表在更新 statistics 時造成的 table scans。
啟用之後，SQL Server 就會以每個 partition 維單位去更新。
可以減少更新所需時間，尚未過期的 partition 也不會被動到，減少不必要的負載。
但並不是所有情境都支援，以下情境 SQL Server 會產生警告並且忽略這個設定
除了 SQL Server 自動建立和更新 statistics 以外，也可以自己建立
示範建立多重欄位 statistics & filtered statistics 語法 :
在建立 statistics 的時候，還可以用以下選項 :
| Option | Description |
|---|---|
| FULLSCAN | 使用 table 中 100% 的 rows 作為 sample 來建立 statistic object。這個 option 會建立最準確的 statistics，但產生時間最長。 |
| SAMPLE | 指定用來建立 statistic object 的 rows 數量或 rows 百分比。sample 越大，statistics 越準確，但產生時間也越長。指定 0 會建立 statistics，但不會填入資料。 |
| NORECOMPUTE | 排除該 statistic object，使其不會透過 AUTO_UPDATE_STATISTICS 自動更新。 |
| INCREMENTAL | 覆寫 database-level 的 incremental statistics 設定。 |
單一 statistics，或某個 table 上的所有 statistics，都可以使用 UPDATE STATISTICS statement 來更新。
也可以用 sp_updatestats system stored procedure 來更新整個 database 的 statistics 。
這個 procedure 會更新硬碟上的 table 所有已過期的 statistitcs；至於記憶體表上的 statistics，不論是否過期，都會全部更新。
更新 statistics 會導致使用這些 statistics 的 queries，在下一次執行時重新編譯。
唯一的例外是：被參考的 tables 和 indexes 只有一種可能的 plan。
例如，假設 MyTable 有 clustered index，則：
SELECT *
一定會執行 clustered index scan
這就是為什麼，很多人都強調，不要 SELECT * 號
一般人的說法是一次撈全部的欄位很耗效能
內行人的說法是這樣會讓最佳化器無從選擇，只有 scan 一途。
SQL Server 2019之後，引入一個額外的 metadata，可以用來協助診斷查詢因為等待 statistics updates 的問題。
新增一個 wait type，稱為 WAIT_ON_SYNC_STATISTICS_REFRESH，這個等待類型就表示查詢等待 statistics updates 完成所花費的時間
sys.dm_exec_requests DMV 新增了一種 command type，叫做 SELECT (STATMAN)。這個command type 表示某個 SELECT statement 目前正在等待 statistics update 完成，完成後才能繼續執行。
因為 cardinality estimation 對 plan optimization 非常重要，所以如果 cardinality estimator 做出錯誤的假設，會對 query performance 造成嚴重影響。
因此，cardinality estimator 不會經常進行重大變更。
這裡說的變更不是像 Statistics update 那種自己去更新變更統計資訊，是在說微軟自己的 Cardinality 估算的邏輯，SQL Server 2014 的時候有改過一次。
補充詳細說明 Statistics 跟 Cardinality 的關係
前面有說過這是一個統計資訊， 是用來描述某個 column 或一組 columns 內部資料的分布情況。這些 columns 可以位於 table 裡，也可以位於 nonclustered index 裡。
例如 Orders table 有一個 OrderData column，statistics 就會記錄
某些日期範圍內有多少資料
哪些值出現比較多
資料分布是否集中或分散
distinct values 大約有多少
這些資訊可以讓 SQL Server 不需要真得先把資料全部掃過一遍，就可以判斷查詢大概會回傳多少 row。
前面也有說過，這是指的是查詢優化器預期某個查詢會回傳多少 rows。這是優化器為指定查詢選擇最佳執行計畫時的一個關鍵因素。
優化器用來估算 cardinality 的主要依賴，就是 statistics。
假設 Orders 有 1000000 rows。
如果 statistics 顯示：
2024 年以後的訂單大約只有 10,000 rows
優化器可能會估算 :
Estimated rows = 10,000
而這個估算就是 cardinality 估算出來的。
但如果 statistics 過期，實際上 2024 年以後已經有 500,000 rows，Optimizer 還以為只有 10,000 rows，就會產生錯誤的 cardinality estimate。
錯誤的 cardinality estimate 會導致錯誤的 execution plan。
SQL Server 在選擇執行計畫的時候，會很依賴預估資料量。
例如 :
如果優化器估計只會回傳 3 行資料，那他就會選 :
Index Seek + Key Lookup
因為資料很少，所以逐筆 lookup 很划算。
但如果實際是會傳 30萬筆資料，這個 plan 就會變得很爛，
如果優化器可以確實的知道會回傳 30萬筆資料，哪他可能會選 :
Index Scan + Table Scan
比 lookup 更有效率。
所以 Statistics 跟 Cardinality 影響查詢的核心就是
statistics 不準
↓
cardinality estimate 不準
↓
cost estimate 不準
↓
execution plan 可能選錯
↓
query performance 變差
我在版上有回答過一個問題，有人2008升級到2016以後，同樣的查詢、同樣的table，突然從 3 秒鐘變成 20 幾分鐘，然後他嘗試各種做法都無法解決，而真正的底層原因就是因為這個估算方式在 2014 有一個重大改版，他會讓整個執行計畫在 2014 前後完全不一樣。
首先，2014以前是假設 columns 彼此獨立的行為，被改成假設不同 columns 中的 values 之間可能存在 correlation。
舉例 :
舊版的 Cardinality Estimator 比較偏向假設 :
A欄條件 跟 B欄條件，彼此是獨立的
WHERE City = 'Taipei'
AND ZipCode = '100'
在舊版中很容易就把城市跟郵遞區號當成兩個獨立條件去估算；
但實際上城市跟郵遞區號很可能有關聯性，城市=台北，會大幅影響郵遞區號可能出現的範圍。
所以 2014開始 新版 Cardinality 估計會比較傾向認為，
不同 columns 之間的 values 可能存在 correlation。
這個改動會影響效率甚鉅因為現實環境中 predicate 和 join column 之間可能有關聯。
這裡說明資料偏斜會對效能造成什麼影響 (多table)
假設有兩張表
Customers 有十萬筆資料
平均每 1 個客人都有 10 張訂單
然後假設
City = 'Taipei' 會有 1000個客戶
最後查詢
優化器就要估算 Taipei 的 customers join Orders 後會有多少 orders？
那問題就是
City = Taipei
和
Orders.CustomerID 的訂單數量
可能不是獨立的，這裡說的獨立是邏輯上的那種
例如台北客戶可能平均訂單數比較多，也可能比較少。
在這個案例中，優化器不知道特殊偏斜的情況下，她很自然的會去估算，
台北有 1000 人，整體平均每個人 10 張訂單，所以 1000 * 10 = 10,000 筆 rows
可是問題是，台北人都是大客戶，每個人平均有100張訂單，造成實際結果是1000 * 100 = 100,000 筆 rows
估計與現實相差十倍，這會讓優化器在選擇執行計畫的時候出現相當大的偏差導致最後效能很差。
而追根究底的原因，就是因為SQL Server 不知道整個資料其實訂單筆數跟城市是有相關的，整體資料是偏斜的。
這種情況無論在 2014 前後，都會發生，因為這個案例是跨 table 的，而且 join 的 key 跟 where 不同，所以一班來說統計資訊不會保存這種跨 table 的關聯資訊，如果是這種情況要優化就要做別的處理，這裡舉這個例子是因為很直觀的可以看出差異比較好理解。
現在換一個新的假設
Customers 有 1,000,000 筆資料
欄位：
資料分布假設
City = 'Taipei' 有 100,000 筆，占 10%
ZipCode = '100' 有 100,000 筆，占 10%
而且 ZipCode = '100' 幾乎都在 Taipei
然後去查詢
顯而易見實際結果一定也是 100,000 筆，因為郵遞區號跟城市台北就是同一種東西
他們不是獨立的
City = 'Taipei' selectivity = 10%
ZipCode = '100' selectivity = 10%
兩個條件一起成立：
10% * 10% = 1%
所以估計 1000000 * 1% = 10000筆
他比較會考慮 City 和 ZipCode 可能存在關聯性
所以不會簡單粗暴的去算 10%*10%，而是會用比較保守的方式估算多條件的選擇性。
所以新版的估計"可能”會比較接近真實的十萬筆。
真實的計算方式是 總筆數 * 第一選擇%數 * 第二選擇%數的二次方根 * 第三選擇%數的四次方根 * 第四選擇%數的八次方根 .... 以此類推
所以在這個案例中，因為剛好都是 10%，他估算的方式就會是
1000000 * 0.1 * 0.1^1/2 = 14142筆
雖然跟以前的一萬筆相差不大，但確實就是更接近真實筆數。
阿這也是為什麼查詢的時候不要寫這種邏輯上一樣的篩選，你篩台北又篩台北的郵遞區號沒有意義還有機會讓 SQL Server 給出一個爛的執行計畫。
如果你今天篩台北會出現不是台北的郵遞區號，那應該要檢討的是為什麼這種邏輯錯誤的事情會發生，要檢討前後端在寫資料的時候為什麼沒有做這類的檢查。
首先舊版的太過樂觀，只估計出一萬筆，那就會導致SQL Server 去選下面這幾總執行計畫
Index Seek
Nested Loops Join
Key Lookup
較小 memory grant
但現在實際是有十萬筆，那麼
Key Lookup 次數會暴增
Nested Loops 成本會過高
memory grant 會不夠
Sort / Hash 會spill 到 tempdb
而新版的估計的比較接近十萬筆的話，SQL Server 會更傾向於選擇
Index Scan
Hash Join
較合理的 memory grant
這種執行計畫對大量資料比較合理。
估算多個 tables join 後是否不會回傳資料的行為也有所改變。
原本的行為是 : 在 sql server 以 join histograms 估算 join selectivity 之前，先估算每個 table 中 predicates 的 selectivity。
新的行為是 : 先根據 base tables 估算 join selectivity，然後再估算 predicate selectivity。這稱為 base containment。
histogram、selectivity 都是統計資訊的東西，實際上是什麼東西在效能調教講
上面寫的很精簡繞口，實際上舊版的流程會是這樣
Customers table
先套用 WHERE c.City = 'Taipei'
估計剩 1,000 rows
Orders table
先套用 WHERE o.OrderDate >= '2024-01-01'
估計剩 50,000 rows
然後再估算：
這 1,000 個 customers
join 這 50,000 筆 orders
會產生多少 rows
也就是 : 先篩選 再 join
而 2014 以後的版本 流程會變這樣
先看 Customers base table 和 Orders base table 的 join 關係
估算整體 join 可能產生多少 rows
再套用：
c.City = 'Taipei'
o.OrderDate >= '2024-01-01'
這些 predicates 的影響
也就是 : 先 join 再篩選
按照前面說的，他會先篩選所以他會變成這樣
Customers:
CustomerID BETWEEN 1 AND 1000
=> 100,000 裡面剩 1,000 rows
Orders:
CustomerID BETWEEN 90000 AND 91000
=> 假設平均每個 customer 有 10 張 orders
=> 約 1,000 客戶ID * 10客戶訂單 = 10,000 rows
Orders table 裡，CustomerID 落在 90000 到 91000 之間的訂單，大約有 10,000 筆。
然後接下來 SQL Server 就會拿這兩個數據開始亂估，反正不管怎麼估，他要估出 0 rows 的概率很低，因為對他而言就是有這麼多筆資料。
他會先看 Join 關係然後才在去篩選
他會先看這個
Customers.CustomerID base domain: 1 到 100,000
Orders.CustomerID base domain: 1 到 100,000
join 條件: c.CustomerID = o.CustomerID
然後再套條件
這時候他就會能理解
使用者可能查到不存在的資料
兩邊 filtered CustomerID range 可能根本沒有交集
join 結果可能非常少，甚至接近 0
所以新版的估計假設，比較能處理使用者查詢的join組合可能不存在的這種情境，因此在兩邊 filtered join key range 沒有重疊時，估算結果可能比舊版更接近實際值，例如接近 0。
如果實際結果接近 0，估算也接近 0，通常能讓 Optimizer 選擇較合理的 join order、index access method 與 memory grant，避免不必要的大量掃描或過大的資源配置。
但是新的 cardinality 估計對我的 workloads 來說，可能不是最佳選擇。
舉例
剛剛說 了他會先去看 Join 的關聯性，所以會得到直接 join 的話會有一百萬筆資料
然後再去篩選，先看客戶資料表 1000 筆是整體 1%、在看訂單表 1000 個客戶大約有一萬張訂單，佔整體 1%
所以他大概率會去估計 1000000 * 1% * 1% = 100 rows
這樣子看新版就會比實際還要低估 100 被。
這都只是舉例 他實際不是這樣算 但是數字具象化會比較好理解
如果發生這種情況，可以使用 plan hint，強制 query 使用 legacy cardinality estimations，切回舊版的估計。
先準備資料
| 項目 | Legacy CE | 新版 CE |
|---|---|---|
| 實際 rows | 2,000,000 | 2,000,000 |
| 估計 rows | 約 19,017 | 約 342,468 |
| 估算準確度 | 低估約 105 倍 | 估約 5.8 倍 |
| Plan | Nonclustered Index Seek + Key Lookup | Clustered Index Scan |
| 實際時間 | 約 18 秒 | 約 20 秒 |
新版 CE 估得比較準，但這次選出來的 Clustered Index Scan 實際比 Legacy CE 的 Index Seek + Key Lookup 慢一點。
因為 Optimizer 不是只看「估計 rows 準不準」，它還要根據估計 rows 去選 access method。
新版 CE 估到 342,468 rows，比 Legacy CE 的 19,017 rows 高很多。
所以新版 CE 可能判斷：
回傳資料量不少
如果用 Nonclustered Index Seek + Key Lookup
可能要做很多次 lookup
所以不如直接掃 clustered index
於是它選了：
Clustered Index Scan
但實際上我們測試條件是 10% 資料符合：
20,000,000 rows 裡面有 2,000,000 rows 符合
在這種情況下，兩種 plan 都有代價：
Clustered Index Scan：
掃整張 clustered index，20,000,000 rows 都要看過
Index Seek + Key Lookup：
先從比較窄的 nonclustered index 找到 2,000,000 rows，
再回 clustered index 拿 Filler
如果資料剛好排列得很集中、cache 狀態剛好、磁碟 I/O 狀態剛好，**2,000,000 次 lookup 不一定會比掃 20,000,000 rows 慢**。
所以這次 Legacy CE 雖然估錯，但它剛好選到比較快的 access method。
在這個實驗中，新版 CE 的 estimated rows 明顯比 Legacy CE 更接近 actual rows，但新版 CE 因為估計回傳資料量較大，選擇 Clustered Index Scan；Legacy CE 雖然嚴重低估 rows，但選擇 Nonclustered Index Seek + Key Lookup。實際執行結果中，Legacy CE 的 plan 反而略快。這表示 cardinality estimate 較準不代表執行時間必然較短，因為最後效能還取決於 access method、資料分布、cache 狀態、I/O、row width、lookup 成本模型等因素。
上面有涵蓋到一點點效能調教的味道，在效能調教的篇章會更深入的說明
下一篇 : 資料庫一致性
iThome鐵人賽
看影片追技術