SQL Server如何跟蹤自動(dòng)統(tǒng)計(jì)信息更新?
2025-03-20 10:07 瀟湘隱者 閱讀(1138) 評(píng)論(2) 收藏 舉報(bào)SQL Server數(shù)據(jù)庫中，我們都清楚統(tǒng)計(jì)信息對(duì)于優(yōu)化器來說非常重要。一般情況下，我們會(huì)開啟"自動(dòng)更新統(tǒng)計(jì)信息"(Auto Update Statistics)這個(gè)選項(xiàng)，以便數(shù)據(jù)庫能自動(dòng)更新過期/過時(shí)的統(tǒng)計(jì)信息，因?yàn)檫^期/過時(shí)的統(tǒng)計(jì)信息可能會(huì)導(dǎo)致數(shù)據(jù)庫生成一個(gè)糟糕的執(zhí)行計(jì)劃，SQL性能將會(huì)大打折扣，舉一個(gè)例子，我們大腦做一些決策的時(shí)候，嚴(yán)重依賴所獲取做決策信息的真實(shí)性與準(zhǔn)確性，如果你所獲得的信息是錯(cuò)誤的，那么十有八九你會(huì)做出一個(gè)嚴(yán)重錯(cuò)誤的決定。例如，如果當(dāng)下環(huán)境中，你獲取的信息：”買房穩(wěn)賺不賠;買房會(huì)抗通脹......“是過時(shí)/錯(cuò)誤的信息，那么你就會(huì)為當(dāng)下的決策付出慘痛代價(jià)。
"自動(dòng)更新統(tǒng)計(jì)信息"固然是不錯(cuò)的一個(gè)功能，但是很多人對(duì)它內(nèi)部的原理知之甚少。對(duì)于"自動(dòng)更新統(tǒng)計(jì)信息"是否開啟也是有一些爭論的。如果你監(jiān)控發(fā)現(xiàn)一個(gè)SQL的執(zhí)行計(jì)劃經(jīng)常出現(xiàn)變化，除了參數(shù)嗅探外等因素外，那么你要考慮一下可能是因?yàn)镾QL語句中所涉及的表的統(tǒng)計(jì)信息自動(dòng)更新導(dǎo)致。個(gè)人曾遇到一個(gè)案例，SQL語句的執(zhí)行計(jì)劃在凌晨2點(diǎn)變了，而且是性能變差，具體原因是在這個(gè)時(shí)間段，有一個(gè)作業(yè)會(huì)歸檔清理數(shù)據(jù)，導(dǎo)致觸發(fā)自動(dòng)統(tǒng)計(jì)信息更新，而它使用的是自動(dòng)采樣比例，而由于采樣比例過低，導(dǎo)致優(yōu)化器生成了一個(gè)較差的執(zhí)行計(jì)劃。如果你不用擴(kuò)展事件去跟蹤、分析的話，那么真的很難搞清楚為什么出現(xiàn)這種玄幻的現(xiàn)象。
下面是一個(gè)SQL執(zhí)行計(jì)劃經(jīng)常出現(xiàn)變化的例子的截圖，來自SolarWinds的DPA。
下面介紹一下，如何使用擴(kuò)展事件跟蹤統(tǒng)計(jì)信息自動(dòng)更新。可以在做一些深入分析時(shí)用到。
創(chuàng)建擴(kuò)展事件stat_auto_update_event
CREATE EVENT SESSION [stat_auto_update_event] ON SERVER
ADD EVENT sqlserver.auto_stats(
ACTION(sqlserver.sql_text,sqlserver.username,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'E:\extevntlog\stat_auto_update_event',max_rollover_files=(60)),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
啟動(dòng)會(huì)話,擴(kuò)展事件就能捕獲數(shù)據(jù)庫中"自動(dòng)更新統(tǒng)計(jì)信息"的一些事件了。
ALTER EVENT SESSION [stat_auto_update_event] ON SERVER
STATE = START;
此時(shí)，你就可以用下面SQL查看/分析"自動(dòng)更新統(tǒng)計(jì)信息"的一些詳細(xì)信息了。
IF OBJECT_ID('tempdb..#stat_auto_update_event') IS NOT NULL
DROP TABLE #stat_auto_update_event;
CREATE TABLE #stat_auto_update_event
(
[ID] INT IDENTITY(1, 1)
NOT NULL ,
[stat_update_dtl] XML ,
CONSTRAINT [pk_stat_auto_update_event] PRIMARY KEY CLUSTERED ( [ID] )
);
INSERT #stat_auto_update_event
( [stat_update_dtl] )
SELECT CONVERT(XML, [event_data]) AS [stat_update_dtl]
FROM [sys].[fn_xe_file_target_read_file]('E:\extevntlog\stat_update_event*.xel', NULL, NULL, NULL)
CREATE PRIMARY XML INDEX [xml_idx_stat_dtl] ON #stat_auto_update_event([stat_update_dtl]);
CREATE XML INDEX [xml_idx_stat_dtl_path] ON [#stat_auto_update_event]([stat_update_dtl])
USING XML INDEX [xml_idx_stat_dtl] FOR VALUE;
WITH cte_stat AS (
SELECT
[sw].[stat_update_dtl].[value]('(/event/data[@name="database_id"]/value)[1]', 'INT') AS [database_id],
[sw].[stat_update_dtl].[value]('(/event/@timestamp)[1]', 'DATETIME2(7)') AS [event_time],
[sw].[stat_update_dtl].[value]('(/event/@name)[1]', 'VARCHAR(MAX)') AS [event_name],
[sw].[stat_update_dtl].[value]('(/event/data[@name="index_id"]/value)[1]', 'BIGINT') AS [index_id],
[sw].[stat_update_dtl].[value]('(/event/data[@name="object_id"]/value)[1]', 'BIGINT') AS [object_id],
[sw].[stat_update_dtl].[value]('(/event/data[@name="job_type"]/text)[1]', 'VARCHAR(MAX)') AS [job_type],
[sw].[stat_update_dtl].[value]('(/event/data[@name="sample_percentage"]/value)[1]','INT') AS [sample_pct],
[sw].[stat_update_dtl].[value]('(/event/data[@name="status"]/text)[1]', 'VARCHAR(MAX)') AS [status],
[sw].[stat_update_dtl].[value]('(/event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000000. AS [duration],
[sw].[stat_update_dtl].[value]('(/event/data[@name="statistics_list"]/value)[1]', 'VARCHAR(MAX)') AS [statistics_list]
FROM [#stat_auto_update_event] AS [sw]
)
SELECT
DB_NAME([cte_stat].[database_id]) AS [database_name] ,
DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), [cte_stat].[event_time]) AS [event_time] ,
[cte_stat].[event_name] ,
OBJECT_NAME([cte_stat].[object_id],[cte_stat].[database_id]) AS object_name,
[cte_stat].[index_id] ,
[cte_stat].[job_type] ,
[cte_stat].[status] ,
[cte_stat].[sample_pct],
[cte_stat].[duration] ,
[cte_stat].[statistics_list]
FROM cte_stat
ORDER BY [cte_stat].[event_time];
上面擴(kuò)展事件是跟蹤整個(gè)數(shù)據(jù)庫實(shí)例下的所有"自動(dòng)更新統(tǒng)計(jì)信息"事件，會(huì)存在一定的開銷，如果我只想跟蹤某個(gè)對(duì)象，那么可以在創(chuàng)建擴(kuò)展事件時(shí)進(jìn)行過濾處理，如下所示，我只跟蹤表test的"自動(dòng)更新統(tǒng)計(jì)信息"，那么就可以通過下面腳本添加擴(kuò)展事件
CREATE EVENT SESSION [test_auto_update_event] ON SERVER
ADD EVENT sqlserver.auto_stats(
SET collect_database_name=(0)
ACTION
(
sqlserver.client_app_name
,sqlserver.sql_text
,sqlserver.tsql_stack
,sqlserver.username
,sqlserver.database_name
)
WHERE
[object_id] =45243216/* order of conditions matters - pick the most selective first */
AND [database_id] =5
AND [package0].[not_equal_uint64]([status], 'Loading stats without updating')
)
ADD TARGET package0.event_file(SET filename=N'E:\extevntlog\test_auto_update_event',max_rollover_files=(60)),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
注意：要根據(jù)實(shí)際情況調(diào)整相關(guān)值，例如[database_id]、[object_id]的值。
手動(dòng)構(gòu)造一些條件，觸發(fā)表test自動(dòng)更新統(tǒng)計(jì)信息，此時(shí)，你可以使用ssms工具查看擴(kuò)展事件捕獲的一些數(shù)據(jù)了，如下截圖所示：
當(dāng)然，你也可以使用下面SQL語句進(jìn)行查詢
IF OBJECT_ID('tempdb..#stat_auto_update_event') IS NOT NULL
DROP TABLE #stat_auto_update_event;
CREATE TABLE #stat_auto_update_event
(
[ID] INT IDENTITY(1, 1)
NOT NULL ,
[stat_update_dtl] XML ,
CONSTRAINT [pk_stat_auto_update_event] PRIMARY KEY CLUSTERED ( [ID] )
);
INSERT #stat_auto_update_event
( [stat_update_dtl] )
SELECT CONVERT(XML, [event_data]) AS [stat_update_dtl]
FROM [sys].[fn_xe_file_target_read_file]('E:\extevntlog\test_auto_update_event*.xel', NULL, NULL, NULL)
CREATE PRIMARY XML INDEX [xml_idx_stat_dtl] ON #stat_auto_update_event([stat_update_dtl]);
CREATE XML INDEX [xml_idx_stat_dtl_path] ON [#stat_auto_update_event]([stat_update_dtl])
USING XML INDEX [xml_idx_stat_dtl] FOR VALUE;
WITH cte_stat AS (
SELECT
[sw].[stat_update_dtl].[value]('(/event/data[@name="database_id"]/value)[1]', 'INT') AS [database_id],
[sw].[stat_update_dtl].[value]('(/event/@timestamp)[1]', 'DATETIME2(7)') AS [event_time],
[sw].[stat_update_dtl].[value]('(/event/@name)[1]', 'VARCHAR(MAX)') AS [event_name],
[sw].[stat_update_dtl].[value]('(/event/data[@name="index_id"]/value)[1]', 'BIGINT') AS [index_id],
[sw].[stat_update_dtl].[value]('(/event/data[@name="object_id"]/value)[1]', 'BIGINT') AS [object_id],
[sw].[stat_update_dtl].[value]('(/event/data[@name="job_type"]/text)[1]', 'VARCHAR(MAX)') AS [job_type],
[sw].[stat_update_dtl].[value]('(/event/data[@name="sample_percentage"]/value)[1]','INT') AS [sample_pct],
[sw].[stat_update_dtl].[value]('(/event/data[@name="status"]/text)[1]', 'VARCHAR(MAX)') AS [status],
[sw].[stat_update_dtl].[value]('(/event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000000. AS [duration],
[sw].[stat_update_dtl].[value]('(/event/data[@name="statistics_list"]/value)[1]', 'VARCHAR(MAX)') AS [statistics_list],
[sw].[stat_update_dtl].[value]('(/event/action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') AS [sql_text],
[sw].[stat_update_dtl].[value]('(/event/action[@name="client_app_name"]/value)[1]','VARCHAR(MAX)') AS [client_app_name]
FROM [#stat_auto_update_event] AS [sw]
)
SELECT
DB_NAME([cte_stat].[database_id]) AS [database_name] ,
DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), [cte_stat].[event_time]) AS [event_time] ,
[cte_stat].[event_name] ,
OBJECT_NAME([cte_stat].[object_id],[cte_stat].[database_id]) AS object_name,
[cte_stat].[index_id] ,
[cte_stat].[job_type] ,
[cte_stat].[status] ,
[cte_stat].[sample_pct],
[cte_stat].[duration] ,
[cte_stat].[statistics_list],
[cte_stat].[sql_text],
[cte_stat].[client_app_name]
FROM cte_stat
ORDER BY [cte_stat].[event_time];
關(guān)于擴(kuò)展信息捕獲的aut_stat數(shù)據(jù)，status狀態(tài)一般有下面一些值(狀態(tài))，其中Loading stats without updating通常指的是加載統(tǒng)計(jì)信息而不進(jìn)行更新操作
Loading stats without updating Other Loading and updating stats
那么使用擴(kuò)展事件追蹤統(tǒng)計(jì)自動(dòng)統(tǒng)計(jì)信息更新，有哪一些用途呢？ 下面是我簡單的一些總結(jié)，不僅僅局限于此，你也可以擴(kuò)展其用途。
追蹤分析自動(dòng)統(tǒng)計(jì)信息的采樣比例 分析SQL語句執(zhí)行計(jì)劃變化的原因。 為手工更新統(tǒng)計(jì)信息的頻率與表對(duì)象提供數(shù)據(jù)支撐 研究自動(dòng)統(tǒng)計(jì)信息更新觸發(fā)的一些機(jī)制。
參考資料
https://dba.stackexchange.com/questions/331860/use-extended-events-to-track-autoupdate-statistics-on-a-specific-table
浙公網(wǎng)安備 33010602011771號(hào)