德瑞克：SQL Server 學習筆記: Use Autostat (AUTO_UPDATE_STATISTICS), 認識自動更新統計資料 (1)

(1) 更新 Statistics

更新過的 Statistics，能夠反映實際的資料特性，幫助 Query Optimizer 選擇合適的 Index。
若 Statistics 尚未更新，導致無法選擇更有效率的 Index。

(2) 啟用 AUTO_UPDATE_STATISTICS

Query Optimizer 會判斷 statistics 何時過期。
statistics 過期後，要等到下次執行 Query(SIUD)，查詢 statistics 時，一併更新 statistics。
如果 statistics 已過期，Query Optimizer 就會先等候並執行更新 statistics，然後再 compile 並執行查詢。

(3) Statistics 更新的 Threshold(臨界值)

包含 SQL Server 2014 (12.x)，以及先前版本，Statistics 更新的 Threshold是：

若 Table Cardinality(資料表基數) 在評估 Statistics 時為 500 或更小的數值，將會在每 500 次修改之後更新。
若資料表基數在評估 Statistics 時為超過 500 的數值，將會在每 20% + 500 的修改次數之後更新。
在 tempdb 資料庫中，如果資料表基數是小於 6，資料表的資料表每 6 修改，就會自動更新。
Table Variables為基數的變更，不會觸發自動更新統計資料。

例如：

資料表有 10 萬筆資料列，若要達到自動更新統計資訊的臨界值
需要有 20,500 筆以上被異動。
計算：(100000 * 0.2) + 500 = 20500

(4) 建立 Index，系統自動建立對應的 Statistics。

Statistics: Use Autostat (AUTO_UPDATE_STATISTICS ), 認識自動更新統計資料

01. 檢查 COMPATIBILITY_LEVEL

確認 COMPATIBILITY_LEVEL = 120
包含 SQL Server 2014 (12.x)，以及先前版本，會使用變更資料列的百分比作為臨界值。

Statistics 更新的 Threshold(臨界值)是：

若資料表基數在評估統計資料時為 500 或更小的數值，將會在每 500 次修改之後更新。
若資料表基數在評估統計資料時為超過 500 的數值，將會在每 20% + 500 的修改次數之後更新。

-- figure 01_COMPATIBILITY_LEVEL = 120

依據預設值，資料庫屬性：Auto Create Statistics 與 Auto Update Statistics 已經啟用。

-- figure 檢視 Autostat

02. 建立資料表 Statistics_100K

建立資料表: Statistics_100K。
建立 Primary Key 與 Clustered Index 在 column: rid。
新增 10 萬筆資料列。

-- figure 02_CREATE TABLE Statistics_100K - Insert

03. 檢視資料特性：

-- figure 03_View_Table_Data

04. 使用 dm_db_stats_properties: 傳回資料表的所有 Statistics 屬性

dm_db_stats_properties: SQL Server 2008 版本提供，可用於傳回資料表的所有統計資料屬性。
建立 Clustered Index 在 column: rid，系統自動一併建立 Statistics: PK_Statistics_100K_rid。

但 Statistics 目前都是 NULL，尚未產生。

-- dm_db_stats_properties: Returns properties of statistics for the specified database object

SELECT sch.name [Schema], t.name [Table], c.name [Column], stat.stats_id, stat.name [Statistics], sp.last_updated, 
 sp.rows, sp.rows_sampled, sp.steps, sp.unfiltered_rows, stat.filter_definition, sp.modification_counter, 
 stat.auto_created, stat.user_created
FROM sys.stats stat CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) sp
 INNER JOIN sys.tables t ON t.object_id = stat.object_id
 INNER JOIN sys.stats_columns sc ON (sc.stats_id = sp.stats_id AND sp.object_id = sc.object_id)
 INNER JOIN sys.columns c ON (c.column_id = sc.column_id AND c.object_id = t.object_id)
 INNER JOIN sys.schemas sch ON t.schema_id = sch.schema_id
WHERE t.name = 'Statistics_100K';  
GO

-- figure 04_dm_db_stats_properties_PK

05. 使用 SSMS 檢視 Index 與 Statistics。

-- figure 05_SSMS_Statistics

06. 自動產生 Statistics。

依據預設值，有啟用資料庫選項：AUTO_CREATE_STATISTICS。
WHERE 條件式使用 Column: Cdata，Query Optimizer 將自動為其建立 Statistics。

AUTO_CREATE_STATISTICS

查詢最佳化工具會視需要針對查詢述詞中的單一資料行建立統計資料，以便改善查詢計劃和查詢效能。
這些單一資料行統計資料是在查詢最佳化工具編譯查詢時所建立的。
它只會針對尚未成為現有統計資料物件之第一個資料行的資料行建立單一資料行統計資料。
預設值是 ON。

-- figure 06_Auto Create Statistics Cdata column

07. 檢查資料表上的 Statistics。

自動建立 Statistics: _WA_Sys_00000003_226010D3

Column: Cdata
rows: 100000
rows_sampled: 88612
steps： 131
unfiltered_rows: 100000
modification_counter: 0
auto_created: 1

注意：

Statistics: PK_Statistics_100K_rid 內容值尚未產生，仍是 NULL

-- figure 07_自動建立 Statistics_但PK 還沒有產生 Statistics

08. 使用 SSMS 檢視 Index 與 Statistics。

已經新增第 2 個 Statistics: _WA_Sys_00000003_226010D3

-- figure 08_SSMS_Statistics

09. 檢視 Statistics 的 Details 頁籤。

-- figure 09_SSMS_Statistics_Details

10. 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

DBCC SHOW_STATISTICS 可檢視指定 Index, Statistics, Column 的統計資訊。
WITH STAT_HEADER，僅檢視 STAT_HEADER。
與 dm_db_stats_properties 結果相同。

-- figure 10_DBCC SHOW_STATISTICS_WITH STAT_HEADER

11. 建立 Create NonClustered Index on [TransactionID]

-- figure 11_Create NonClustered Index on TransactionID

12. 檢視 Statistics：

產生第 3 個 statistics。
建立 Index，系統自動建立對應的 Statistics: IX_Statistics_100K_TransactionID。

Statistics: IX_Statistics_100K_TransactionID

Column: TransactionID
rows: 100000
rows_sampled:100000
steps：2
unfiltered_rows: 100000
modification_counter: 0
auto_created: 0

注意：

PK_Statistics_100K_rid 尚未產生 Statistics

-- figure 12_建立 Index_系統自動建立對應的 Statistics

13. 使用 SSMS 檢視 Index 與 Statistics。

已經新增第 3 個 Statistics: IX_Statistics_100K_TransactionID

-- figure 13_SSMS_Index_Statistics

14. 使用 SSMS 檢視 Statistics 的 Details 資訊。

-- figure 14_SSMS_Index_Statistics_Details

15. 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

檢視指定 Statistics: IX_Statistics_100K_TransactionID 的統計資訊

-- figure 15_DBCC SHOW_STATISTICS_Index_Statistics

Statistics 尚未更新，導致 Query Optimizer 無法選擇有效率的 Index

認識 AUTO_UPDATE_STATISTICS 自動更新統計資料

包含 SQL Server 2014 (12.x)，以及先前版本，會使用變更資料列的百分比作為臨界值。
與資料表中的資料列數無關。

臨界值是：

若資料表基數在評估統計資料時為 500 或更小的數值，將會在每 500 次修改之後更新。
若資料表基數在評估統計資料時為超過 500 的數值，將會在每 20% + 500 的修改次數之後更新。

以本案例來看：

Threshold is 20% + 500

Count: (100000 * 0.2) + 500 = 20500

資料表有 10 萬筆資料列，若要達到自動更新統計資訊的臨界值

需要有 20,500 筆以上被異動。

01. 本次案例，所使用的 SQL Query：

資料表有 10 萬筆，回傳 20,520 筆。
Clustered Index Scan, Return 20,520 rows
TransactionID < 120521
Execution Plan: Missing Index

-- figure 21_Clustered Index Scan_20520 rows

02. 有 Missing Index

-- figure 22_Clustered Index Scan_20520 rows_Missing_Index

檢視 Execution Plan

Cluster Index Scan
Reads: 1,289
Est Rows: 20,520
Actual Rows: 20,520

-- figure 41_Execution_Plan

03. 刪除資料

刪除 20,300 筆。
但仍未達到更新 Statistics 的 threshold(臨界值)

Threshold is 20% + 500，需要超過 20,500 筆。

-- figure 23_Delete 20300 rows

04. 執行 SQL Query，僅回傳 1 row。

-- figure 24_Query_1_row

05. 檢視 Statistics：

Under Threshold (未達臨界值)

(1) Statistics:IX_Statistics_100K_TransactionID 保持不變

累計： modification_counter: 20300
Total: 79,700 rows, (SELECT 100000 - 20300 -- 79700)

(2) Statistics:PK_Statistics_100K_rid 已經自動產生

Column: rid
rows: 100000
rows_sampled:87284
steps：37
unfiltered_rows: 100000
modification_counter: 20300
auto_created: 0

-- figure 25_Statistics PK_Statistics_100K_rid 已經自動產生_未達臨界值_保持不變

06. 再刪除 10 rows。

再刪除 10 rows。
累計刪除: 20300 + 10 = 20,310 筆。

但 Threshold is 20% + 500，需要超過 20,500 筆。

-- figure 26_再刪除 10 rows_Under Threshold_未達臨界值

07. 檢視 Statistics：

仍是 Under Threshold (未達臨界值)，沒有更新 Statistics
累計刪除: 20300 + 10 = 20,310 筆。
Total: 79,690 rows, (SELECT 100000 - 20300 - 10 -- 79690)
modification_counter: 20310

但 Threshold is 20% + 500，需要超過 20,500 筆。

-- figure 27_modification_counter_未達臨界值

08. 檢視資料表

剩下 79,690 rows。

-- figure 28_Table_剩下_79690

09. 執行先前範例 SQL Query:

資料量已大幅刪除，回傳資料量減少，推論可以改用 Index Seek
但卻仍是使用 Clustered Index Scan, 僅 Return 210 rows
Missing Index
回傳筆數，由 20,520 減少至 210 rows

因為 Statistics 尚未更新，Query Optimizer 仍選擇原先的 Index。

-- figure 29_仍是使用 Clustered Index Scan

檢視 Execution Plan

Cluster Index Scan
Reads: 1,030
Est Rows: 20,520
Actual Rows: 210

預估有 20,520 筆，但實際卻僅有 210 筆。

-- figure 42_Execution_Plan_Delete_尚未更新_Statistics

異動資料，超過更新 Statistics 的 threshold

01. 再刪除 200 rows

Over Threshold (超過臨界值)

再刪除 200 rows，Over Threshold (超過臨界值)
TransactionID < 120511
累計：已經刪除 20,510 rows

(20,300 + 10 + 200)

Threshold is 20% + 500，需要超過 20,500 筆。

-- figure 31_再刪除 200 rows_Over Threshold (超過臨界值)

02. 檢視 Statistics：

超過臨界值，但 Statistics 仍未改變 !

Statistics: IX_Statistics_100K_TransactionID

Column: TransactionID
rows: 100000
rows_sampled:100000
steps：2
unfiltered_rows: 100000
modification_counter: 20510
auto_created: 0

-- figure 32_超過臨界值，但 Statistics 仍未改變

03. 再度執行 SQL Query

AUTO_UPDATE_STATISTICS

開啟自動更新統計資料選項 AUTO_UPDATE_STATISTICS 時，Query Optimizer 會判斷 statistics 何時過期，然後在查詢使用 statistics 時加以更新。
在編譯查詢及執行快取 query plan 之前，Query Optimizer 會檢查是否有過期的 statistics 。
在編譯查詢之前，Query Optimizer 會使用查詢述詞中的資料行、資料表和索引檢視表來判斷哪些 statistics 可能已過期。
在執行快取 query plan 之前， Database Engine 會確認 query plan 是否參考最新的 statistics。

換言之: