(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。
換言之:
- statistics 過期後，要等到下次執行 Query(SIUD)， 查詢 statistics 時，一併更新 statistics。
- 如果 statistics 已過期，Query Optimizer 就會先等候並執行更新 statistics，然後再 compile 並執行查詢。
-- figure 33_何時更新 statistics_ 下次執行 Query(SIUD)
檢視 Execution Plan
- Index Seek
- Reads: 33
- Est Rows: 10
- Actual Rows: 10
-- figure 43_Execution_Plan_已更新_Statistics
04. 檢視 Statistics
Statistics 已經更新
modification_counter: 0
(1) Statistics: IX_Statistics_100K_TransactionID
- Column: TransactionID
- rows: 79490
- rows_sampled:79490
- steps：3
- unfiltered_rows: 79490
- modification_counter: 0
- auto_created: 0
(2) Statistics: PK_Statistics_100K_rid
- Column: rid
- rows: 79490
- rows_sampled:79490
- steps：3
- unfiltered_rows: 79490
- modification_counter: 0
- auto_created: 0
(3) _WA_Sys_00000003_226010D3 仍是未更新
05. 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER
- 檢視 指定 Statistics: IX_Statistics_100K_TransactionID 的統計資訊
Statistics 更新前後的比較
| Before | After | |
| Index | Clustered Index Scan | Index Seek |
| Read | 1,030 | 33 |
| Est Rows | 20,520 | 10 |
更新過的 Statistics，能夠反映實際的資料特性，幫助 Query Optimizer 選擇合適的 Index。
- 若以 Read IO 來看，更新 Statistics 後，幫助 Query Optimizer 選擇合適的 Index
- 效能提升：96.8 %。
Sample Code
20181011_Use Autostat (AUTO_UPDATE_STATISTICS)
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing
Reference
AUTO_UPDATE_STATISTICS Option
https://docs.microsoft.com/zh-tw/sql/relational-databases/statistics/statistics?view=sql-server-2017
Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server
https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server
Changes to automatic update statistics in SQL Server – traceflag 2371
https://blogs.msdn.microsoft.com/saponsqlserver/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371/
Default auto statistics update threshold change for SQL Server 2016
https://blogs.msdn.microsoft.com/psssql/2016/10/04/default-auto-statistics-update-threshold-change-for-sql-server-2016/
Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd535534(v=sql.100)
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
https://technet.microsoft.com/en-us/library/cc966419.aspx
How Statistics in SQL Server determines performance
https://blogs.msdn.microsoft.com/ggaurav/2015/04/10/how-statistics-in-sql-server-determines-performance/
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
Recompilation threshold (RT)
https://technet.microsoft.com/en-us/library/cc966425.aspx
Statistical maintenance functionality (AutoStats) in SQL Server
"change threshold"
https://support.microsoft.com/en-us/help/195565/statistical-maintenance-functionality-autostats-in-sql-server
沒有留言:
張貼留言