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

從 SQL Server 2016 (13.x) 開始，並且  COMPATIBILITY_LEVEL = 130 (資料庫相容性層級 )， 系統採用 Dynamic auto update statistics threshold (動態 自動更新統計資料閾值)

• 使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。 
• 這是以 1000 乘以目前資料表基數的平方根來計算。 

• 例如
• 如資料表包含 2 百萬個資料列，則計算結果是 SQRT(1000 * 2000000) = 44721.359。 

案例

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

• 需要有 10, 000 筆以上被異動。
• 計算： SELECT SQRT (1000 * 100000) = 10000
• 在具有 100,000 行的表中，觸發更新統計信息的閾值已降至 10 %。
• 在一個包含 1,000,000 行的表中，它只需要大約 3.2 % 的異動，即可觸發自動更新統計信息。

透過這項變更，大型資料表上的統計資料會經常更新。 

不過，如果資料庫的相容性層級低於 130，便會套用 SQL Server 2014 (12.x) 臨界值。

追蹤旗標 2371

• 在資料庫相容性層級 低於 130 之下，從 SQL Server 2008 R2 開始至 SQL Server 2014 (12.x)。或是，從 SQL Server 2016 (13.x) 至 SQL Server 2017，使用 追蹤旗標 2371 時， 
• SQL Server 就會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。

• 對於較小的表格，閾值仍將是大約 20% 的百分比。
• 當每個表超過 25,000 行時，動態規則才會啟動，並且隨著行數的增加，更改的記錄百分比變得越來越低。

更新 Statistics (統計值) 的 Threshold

	SQL Server 2014 (12.x)	SQL Server 2016 (13.x)
Threshold	資料表基數的 20% + 500	以 1000 乘以目前資料表基數的平方根
Example	(100000 * 0.2) + 500 = 20,500	SQRT (1000 * 100000) = 10,000

-- figure 53_Excel_Threshold



-- figure 54_Threshold_Chart

---

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




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

-- figure 51_Autostat




01. 檢查 COMPATIBILITY_LEVEL

• 確認 COMPATIBILITY_LEVEL = 130
• 將 固定 自動更新統計資料閾值 變更為 動態 自動更新統計資料閾值。 
• SQL Server 會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。 
• 這是以 1000 乘以目前資料表基數的平方根 計算。 

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

• 如果您的資料表包含 2 百萬個資料列，則計算結果是 SQRT(1000 * 2000000) = 44721.359。 

透過這項變更，大型資料表上的統計資料會經常更新。
不過，如果資料庫的相容性層級低於 130，便會套用 SQL Server 2014 (12.x) 臨界值。


追蹤旗標 2371

• 將 固定 自動更新統計資料閾值 變更為 動態 自動更新統計資料閾值。 
• 在資料庫相容性層級 低於 130 之下，從 SQL Server 2008 R2 開始至 SQL Server 2014 (12.x)。
• 或是，從 SQL Server 2016 (13.x) 至 SQL Server 2017，使用追蹤旗標 2371 時， 
• SQL Server 就會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。
• 當每個表超過 25,000 行時，動態規則才會啟動，並且隨著行數的增加，更改的記錄百分比變得越來越低。
• 例如
• 在具有 100,000 行的表中，觸發更新統計信息的閾值已降至 10%。
• 在一個包含 1,000,000 行的表中，它只需要大約 3.2 % 的異動，即可觸發自動更新統計信息。

注意事項

• 從 SQL Server 2016 (13.x) 開始及根據資料庫相容性層級 130，此行為由引擎控制，追蹤旗標 2371 沒有任何作用。

-- figure 01_COMPATIBILITY_LEVEL 130






02. 建立資料表 Statistics_100K

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

-- figure 02_Create Table Statistics_100K




03. 使用 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 03_dm_db_stats_properties




04. 使用 SSMS 檢視 Index 與 Statistics。

-- figure 04_Primary_Key_Index_Statisitics





05. 自動產生 Statistics。

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

AUTO_CREATE_STATISTICS

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

-- figure 05_Auto Create Statistics_ Cdata column





06. 檢查 資料表上的 Statistics。

自動建立 Statistics: _WA_Sys_00000003_55DFB4D9

• Column: Cdata
• rows: 100000
• rows_sampled: 89782
• steps： 154
• unfiltered_rows: 100000
• modification_counter: 0
• auto_created: 1

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

-- figure 06_dm_db_stats_properties




07. 使用 SSMS 檢視 Index 與 Statistics。

• 已經新增第 2 個 Statistics: _WA_Sys_00000003_55DFB4D9

-- figure 07_SSMS_Statistics





08. 檢視 Statistics 的 Details 頁籤。

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

-- figure 08_Statistics_Detail_NULL





09. 檢視 Statistics 的 Details 頁籤。

• Statistics: _WA_Sys_00000003_55DFB4D9

-- figure 09_Auto_Create_Statistics_Detail_Data





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_Index_Statistics




14. 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

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

-- figure 14_DBCC SHOW_STATISTICS_WITH STAT_HEADER

---

Dynamic auto update statistics threshold 動態 自動更新統計資料閾值




認識 AUTO_UPDATE_STATISTICS 自動更新統計資料

• 從 SQL Server 2016 (13.x) 開始，並 在資料庫相容性層級 130， 
• 採用 Dynamic auto update statistics threshold(動態 自動更新統計資料閾值)
• SQL Server 會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。 
• 這是以 1000 乘以目前資料表基數的平方根來計算。 

• 例如，如果您的資料表包含 2 百萬個資料列，則計算結果是 SQRT(1000 * 2000000) = 44721.359。 

透過這項變更，大型資料表上的統計資料會經常更新。 

不過，如果資料庫的相容性層級低於 130，便會套用 SQL Server 2014 (12.x) 臨界值。

追蹤旗標 2371

• 在資料庫相容性層級 低於 130 之下，從 SQL Server 2008 R2 開始至 SQL Server 2014 (12.x)。
• 或是，從 SQL Server 2016 (13.x) 至 SQL Server 2017，使用追蹤旗標 2371 時， 
• SQL Server 就會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。

對於較小的表格，閾值仍將是大約 20% 的百分比。

當每個表超過 25,000 行時，動態規則才會啟動，並且隨著行數的增加，更改的記錄百分比變得越來越低。

例如

• 在具有 100,000 行的表中，觸發更新統計信息的閾值已降至 10 %。
• 在一個包含 1,000,000 行的表中，它只需要大約 3.2 % 的異動，即可觸發自動更新統計信息。

以本案例來看：

• 資料表有 10 萬筆資料列，若要達到自動更新統計資訊的 threshold
• 需要有 10, 000 筆以上被異動。
• 計算： SELECT SQRT (1000 * 100000) = 10000

-- figure 52_Threshold

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

• 資料表有 10 萬筆，回傳 10,310 筆。
• Clustered Index Scan, Return 10,310 rows
• TransactionID < 110311
• Execution Plan: Missing Index

-- figure 21_常用的 Query 與 條件式




02. 有 Missing Index

-- figure 22_Missing_Index





檢視 Execution Plan

• Cluster Index Scan
• Reads: 1,289
• Est Rows: 10,310
• Actual Rows: 10,310

-- figure 23_Plan_Explorer




03. 刪除資料

• 刪除 10,300 筆。
• 已達到 更新 Statistics 的 threshold(臨界值)

SQL Server 2016(13.x) 版本，改為以 1000 乘以目前資料表基數的平方根來計算

• This is calculated as the square root of the product of 1000 and the current table cardinality. 
• SELECT SQRT (1000 * 100000) = 10000
• TransactionID < 110301
• Return 10,300 rows

-- figure 24_Modify Data to over threshold_SQRT





04. 檢視 Statistics：

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

• Statistics: IX_Statistics_100K_TransactionID
• Column: TransactionID
• rows: 100000
• rows_sampled:100000
• steps：2
• unfiltered_rows: 100000
• modification_counter: 10300
• auto_created: 0
• Total: 89,700 rows: SELECT 100000 - 10300 -- 89700

-- figure 25_dm_db_stats_properties_超過 臨界值，但 Statistics 仍未改變





使用 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

-- figure 26_DBCC SHOW_STATISTICS_WITH STAT_HEADER




05. 再度執行 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 31_何時更新 statistics_下次執行 Query(SIUD)




改為使用 Index Seek

-- figure 32_何時更新 statistics_下次執行 Query(SIUD)




檢視 Execution Plan

• Index Seek
• Reads: 33
• Est Rows: 10
• Actual Rows: 10

-- figure 33_Plan_Explorer




06. 檢視 Statistics

Statistics 已經更新

modification_counter: 0

(1) Statistics: IX_Statistics_100K_TransactionID

• Column: TransactionID
• rows: 89700
• rows_sampled:88296
• steps：80
• unfiltered_rows: 89700
• modification_counter: 0
• auto_created: 0

(2) Statistics: PK_Statistics_100K_rid 尚未產生 Statistics

(3) _WA_Sys_00000003_55DFB4D9 仍是未更新

-- figure 34_dm_db_stats_properties




07. 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

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

-- figure 35_DBCC SHOW_STATISTICS_WITH STAT_HEADER

---

Statistics 更新前後的比較

	Before	After
Index	Clustered Index Scan	Index Seek
Read	1,289	33
Est Rows	10,310	10

更新過的 Statistics，能夠反映實際的資料特性，幫助 Query Optimizer 選擇合適的 Index。

• 若以 Read IO 來看，更新 Statistics 後，幫助 Query Optimizer 選擇合適的 Index
• 效能提升：97.44 %。

SELECT ABS(33-1289)/1289.0 * 100 -- 97.439800

---

Sample Code

20181011_Use Autostat (AUTO_UPDATE_STATISTICS)
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

Reference

Use Autostat (AUTO_UPDATE_STATISTICS), 認識 自動更新統計資料 (1)
http://sharedderrick.blogspot.com/2018/10/statistics-use-autostat.html