德瑞克：SQL Server 學習筆記: 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

德瑞克：SQL Server 學習筆記

搜尋本站文章

2018-10-16

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

沒有留言:

張貼留言

搜尋本站文章

2018-10-16

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

沒有留言:

張貼留言

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