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

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

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

SQL Server數(shù)據(jù)庫中，我們都清楚統(tǒng)計信息對于優(yōu)化器來說非常重要。一般情況下，我們會開啟"自動更新統(tǒng)計信息"(Auto Update Statistics)這個選項，以便數(shù)據(jù)庫能自動更新過期/過時的統(tǒng)計信息，因為過期/過時的統(tǒng)計信息可能會導致數(shù)據(jù)庫生成一個糟糕的執(zhí)行計劃，SQL性能將會大打折扣，舉一個例子，我們大腦做一些決策的時候，嚴重依賴所獲取做決策信息的真實性與準確性，如果你所獲得的信息是錯誤的，那么十有八九你會做出一個嚴重錯誤的決定。例如，如果當下環(huán)境中，你獲取的信息：”買房穩(wěn)賺不賠;買房會抗通脹......“是過時/錯誤的信息，那么你就會為當下的決策付出慘痛代價。

"自動更新統(tǒng)計信息"固然是不錯的一個功能，但是很多人對它內部的原理知之甚少。對于"自動更新統(tǒng)計信息"是否開啟也是有一些爭論的。如果你監(jiān)控發(fā)現(xiàn)一個SQL的執(zhí)行計劃經常出現(xiàn)變化，除了參數(shù)嗅探外等因素外，那么你要考慮一下可能是因為SQL語句中所涉及的表的統(tǒng)計信息自動更新導致。個人曾遇到一個案例，SQL語句的執(zhí)行計劃在凌晨2點變了，而且是性能變差，具體原因是在這個時間段，有一個作業(yè)會歸檔清理數(shù)據(jù)，導致觸發(fā)自動統(tǒng)計信息更新，而它使用的是自動采樣比例，而由于采樣比例過低，導致優(yōu)化器生成了一個較差的執(zhí)行計劃。如果你不用擴展事件去跟蹤、分析的話，那么真的很難搞清楚為什么出現(xiàn)這種玄幻的現(xiàn)象。

下面是一個SQL執(zhí)行計劃經常出現(xiàn)變化的例子的截圖，來自SolarWinds的DPA。

下面介紹一下，如何使用擴展事件跟蹤統(tǒng)計信息自動更新。可以在做一些深入分析時用到。

創(chuàng)建擴展事件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

啟動會話,擴展事件就能捕獲數(shù)據(jù)庫中"自動更新統(tǒng)計信息"的一些事件了。

ALTER EVENT SESSION [stat_auto_update_event] ON SERVER
STATE = START;

此時，你就可以用下面SQL查看/分析"自動更新統(tǒng)計信息"的一些詳細信息了。

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];

上面擴展事件是跟蹤整個數(shù)據(jù)庫實例下的所有"自動更新統(tǒng)計信息"事件，會存在一定的開銷，如果我只想跟蹤某個對象，那么可以在創(chuàng)建擴展事件時進行過濾處理，如下所示，我只跟蹤表test的"自動更新統(tǒng)計信息"，那么就可以通過下面腳本添加擴展事件

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ù)實際情況調整相關值，例如[database_id]、[object_id]的值。

手動構造一些條件，觸發(fā)表test自動更新統(tǒng)計信息，此時，你可以使用ssms工具查看擴展事件捕獲的一些數(shù)據(jù)了，如下截圖所示：

當然，你也可以使用下面SQL語句進行查詢

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];