快捷導(dǎo)航

一文詳解SQL Server如何跟蹤自動(dòng)統(tǒng)計(jì)信息更新

更新時(shí)間：2025年03月20日 14:36:33 作者：瀟湘隱者

SQL Server數(shù)據(jù)庫(kù)中,我們都清楚統(tǒng)計(jì)信息對(duì)于優(yōu)化器來說非常重要,所以本文就來和大家簡(jiǎn)單聊一聊SQL Server如何跟蹤自動(dòng)統(tǒng)計(jì)信息更新吧

SQL Server數(shù)據(jù)庫(kù)中，我們都清楚統(tǒng)計(jì)信息對(duì)于優(yōu)化器來說非常重要。一般情況下，我們會(huì)開啟"自動(dòng)更新統(tǒng)計(jì)信息"(Auto Update Statistics)這個(gè)選項(xiàng)，以便數(shù)據(jù)庫(kù)能自動(dòng)更新過期/過時(shí)的統(tǒng)計(jì)信息，因?yàn)檫^期/過時(shí)的統(tǒng)計(jì)信息可能會(huì)導(dǎo)致數(shù)據(jù)庫(kù)生成一個(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ì)信息"是否開啟也是有一些爭(zhēng)論的。如果你監(jiān)控發(fā)現(xiàn)一個(gè)SQL的執(zhí)行計(jì)劃經(jīng)常出現(xiàn)變化，除了參數(shù)嗅探外等因素外，那么你要考慮一下可能是因?yàn)镾QL語(yǔ)句中所涉及的表的統(tǒng)計(jì)信息自動(dòng)更新導(dǎo)致。個(gè)人曾遇到一個(gè)案例，SQL語(yǔ)句的執(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)更新?？梢栽谧鲆恍┥钊敕治鰰r(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ù)庫(kù)中"自動(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ù)庫(kù)實(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語(yǔ)句進(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];