SQL Server如何跟踪自动统计信息更新?

SQL Server数据库中，我们都清楚统计信息对于优化器来说非常重要。一般情况下，我们会开启"自动更新统计信息"(Auto Update Statistics)这个选项，以便数据库能自动更新过期/过时的统计信息，因为过期/过时的统计信息可能会导致数据库生成一个糟糕的实行计划，SQL性能将会大打折扣，举一个例子，我们大脑做一些决定的时候，严重依赖所获取做决定信息的真实性与准确性，如果你所获得的信息是错误的，那么十有八九你会做出一个严重错误的决定。例如，如果当下环境中，你获取的信息：”买房稳赚不赔;买房会抗通胀......“是过时/错误的信息，那么你就会为当下的决定付出凄惨代价。
"自动更新统计信息"固然是不错的一个功能，但是很多人对它内部的原理知之甚少。对于"自动更新统计信息"是否开启也是有一些争论的。如果你监控发现一个SQL的实行计划常常出现变化，除了参数嗅探外等因素外，那么你要考虑一下可能是因为SQL语句中所涉及的表的统计信息自动更新导致。个人曾碰到一个案例，SQL语句的实行计划在破晓2点变了，而且是性能变差，具体原因是在这个时间段，有一个作业会归档清理数据，导致触发自动统计信息更新，而它利用的是自动采样比例，而由于采样比例过低，导致优化器生成了一个较差的实行计划。如果你不用扩展事件去跟踪、分析的话，那么真的很难搞清楚为什么出现这种玄幻的现象。
下面是一个SQL实行计划常常出现变化的例子的截图，来自SolarWinds的DPA。

下面介绍一下，如何利用扩展事件跟踪统计信息自动更新。可以在做一些深入分析时用到。
创建扩展事件stat_auto_update_event

1. CREATE EVENT SESSION [stat_auto_update_event] ON SERVER 
   
2. ADD EVENT sqlserver.auto_stats(
   
3.     ACTION(sqlserver.sql_text,sqlserver.username,sqlserver.database_name))
   
4. ADD TARGET package0.event_file(SET filename=N'E:\extevntlog\stat_auto_update_event',max_rollover_files=(60)),
   
5. ADD TARGET package0.ring_buffer
   
6. 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)
   
7. GO

复制代码

启动会话,扩展事件就能捕获数据库中"自动更新统计信息"的一些事件了。

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

复制代码

此时，你就可以用下面SQL查看/分析"自动更新统计信息"的一些具体信息了。

1. IF OBJECT_ID('tempdb..#stat_auto_update_event') IS NOT NULL
   
2.    DROP TABLE #stat_auto_update_event;
   
3. 
   
4. CREATE TABLE #stat_auto_update_event
   
5. (
   
6.          [ID] INT IDENTITY(1, 1)
   
7.                   NOT NULL ,
   
8.          [stat_update_dtl] XML ,
   
9.          CONSTRAINT [pk_stat_auto_update_event] PRIMARY KEY CLUSTERED ( [ID] )
   
10. );
    
11. 
    
12. INSERT  #stat_auto_update_event
    
13.         ( [stat_update_dtl] )
    
14. SELECT  CONVERT(XML, [event_data]) AS [stat_update_dtl]
    
15. FROM    [sys].[fn_xe_file_target_read_file]('E:\extevntlog\stat_update_event*.xel', NULL, NULL, NULL)
    
16. 
    
17. CREATE PRIMARY XML INDEX [xml_idx_stat_dtl] ON #stat_auto_update_event([stat_update_dtl]);
    
18. 
    
19. CREATE XML INDEX [xml_idx_stat_dtl_path] ON [#stat_auto_update_event]([stat_update_dtl])
    
20. USING XML INDEX [xml_idx_stat_dtl] FOR VALUE;
    
21. 
    
22. 
    
23. 
    
24. WITH cte_stat AS (
    
25. SELECT
    
26. [sw].[stat_update_dtl].[value]('(/event/data[@name="database_id"]/value)[1]', 'INT') AS [database_id],               
    
27. [sw].[stat_update_dtl].[value]('(/event/@timestamp)[1]', 'DATETIME2(7)') AS [event_time],
    
28. [sw].[stat_update_dtl].[value]('(/event/@name)[1]', 'VARCHAR(MAX)') AS [event_name],
    
29. [sw].[stat_update_dtl].[value]('(/event/data[@name="index_id"]/value)[1]', 'BIGINT') AS [index_id],
    
30. [sw].[stat_update_dtl].[value]('(/event/data[@name="object_id"]/value)[1]', 'BIGINT') AS [object_id],
    
31. [sw].[stat_update_dtl].[value]('(/event/data[@name="job_type"]/text)[1]', 'VARCHAR(MAX)') AS [job_type],
    
32. [sw].[stat_update_dtl].[value]('(/event/data[@name="sample_percentage"]/value)[1]','INT') AS [sample_pct],
    
33. [sw].[stat_update_dtl].[value]('(/event/data[@name="status"]/text)[1]', 'VARCHAR(MAX)') AS [status],
    
34. [sw].[stat_update_dtl].[value]('(/event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000000. AS [duration],
    
35. [sw].[stat_update_dtl].[value]('(/event/data[@name="statistics_list"]/value)[1]', 'VARCHAR(MAX)') AS [statistics_list]
    
36. FROM [#stat_auto_update_event] AS [sw]  
    
37. )
    
38. SELECT  
    
39.         DB_NAME([cte_stat].[database_id]) AS [database_name] ,
    
40.         DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), [cte_stat].[event_time]) AS [event_time] ,
    
41.         [cte_stat].[event_name] ,
    
42.         OBJECT_NAME([cte_stat].[object_id],[cte_stat].[database_id]) AS object_name,
    
43.         [cte_stat].[index_id] ,
    
44.         [cte_stat].[job_type] ,
    
45.         [cte_stat].[status] ,
    
46.         [cte_stat].[sample_pct],
    
47.         [cte_stat].[duration] ,
    
48.         [cte_stat].[statistics_list]
    
49. FROM cte_stat
    
50. ORDER BY [cte_stat].[event_time];

复制代码

上面扩展事件是跟踪整个数据库实例下的所有"自动更新统计信息"事件，会存在一定的开销，如果我只想跟踪某个对象，那么可以在创建扩展事件时举行过滤处理，如下所示，我只跟踪表test的"自动更新统计信息"，那么就可以通过下面脚本添加扩展事件

1. CREATE EVENT SESSION [test_auto_update_event] ON SERVER 
   
2. ADD EVENT sqlserver.auto_stats(
   
3.     SET collect_database_name=(0)
   
4.     ACTION
   
5.     (
   
6.          sqlserver.client_app_name      
   
7.         ,sqlserver.sql_text             
   
8.         ,sqlserver.tsql_stack           
   
9.         ,sqlserver.username
   
10.         ,sqlserver.database_name
    
11.     )
    
12.     WHERE 
    
13.         [object_id] =45243216/* order of conditions matters - pick the most selective first */
    
14.         AND [database_id] =5
    
15.         AND [package0].[not_equal_uint64]([status], 'Loading stats without updating')
    
16.     
    
17.     )
    
18. ADD TARGET package0.event_file(SET filename=N'E:\extevntlog\test_auto_update_event',max_rollover_files=(60)),
    
19. ADD TARGET package0.ring_buffer
    
20. 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)
    
21. GO

复制代码

注意：要根据实际情况调整相关值，例如[database_id]、[object_id]的值。
手动构造一些条件，触发表test自动更新统计信息，此时，你可以利用ssms工具查看扩展事件捕获的一些数据了，如下截图所示：

当然，你也可以利用下面SQL语句举行查询

1. IF OBJECT_ID('tempdb..#stat_auto_update_event') IS NOT NULL
   
2.    DROP TABLE #stat_auto_update_event;
   
3. 
   
4. CREATE TABLE #stat_auto_update_event
   
5. (
   
6.          [ID] INT IDENTITY(1, 1)
   
7.                   NOT NULL ,
   
8.          [stat_update_dtl] XML ,
   
9.          CONSTRAINT [pk_stat_auto_update_event] PRIMARY KEY CLUSTERED ( [ID] )
   
10. );
    
11. 
    
12. INSERT  #stat_auto_update_event
    
13.         ( [stat_update_dtl] )
    
14. SELECT  CONVERT(XML, [event_data]) AS [stat_update_dtl]
    
15. FROM    [sys].[fn_xe_file_target_read_file]('E:\extevntlog\test_auto_update_event*.xel', NULL, NULL, NULL)
    
16. 
    
17. CREATE PRIMARY XML INDEX [xml_idx_stat_dtl] ON #stat_auto_update_event([stat_update_dtl]);
    
18. 
    
19. CREATE XML INDEX [xml_idx_stat_dtl_path] ON [#stat_auto_update_event]([stat_update_dtl])
    
20. USING XML INDEX [xml_idx_stat_dtl] FOR VALUE;
    
21. 
    
22. WITH cte_stat AS (
    
23. SELECT
    
24. [sw].[stat_update_dtl].[value]('(/event/data[@name="database_id"]/value)[1]', 'INT') AS [database_id],               
    
25. [sw].[stat_update_dtl].[value]('(/event/@timestamp)[1]', 'DATETIME2(7)') AS [event_time],
    
26. [sw].[stat_update_dtl].[value]('(/event/@name)[1]', 'VARCHAR(MAX)') AS [event_name],
    
27. [sw].[stat_update_dtl].[value]('(/event/data[@name="index_id"]/value)[1]', 'BIGINT') AS [index_id],
    
28. [sw].[stat_update_dtl].[value]('(/event/data[@name="object_id"]/value)[1]', 'BIGINT') AS [object_id],
    
29. [sw].[stat_update_dtl].[value]('(/event/data[@name="job_type"]/text)[1]', 'VARCHAR(MAX)') AS [job_type],
    
30. [sw].[stat_update_dtl].[value]('(/event/data[@name="sample_percentage"]/value)[1]','INT') AS [sample_pct],
    
31. [sw].[stat_update_dtl].[value]('(/event/data[@name="status"]/text)[1]', 'VARCHAR(MAX)') AS [status],
    
32. [sw].[stat_update_dtl].[value]('(/event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000000. AS [duration],
    
33. [sw].[stat_update_dtl].[value]('(/event/data[@name="statistics_list"]/value)[1]', 'VARCHAR(MAX)') AS [statistics_list],
    
34. [sw].[stat_update_dtl].[value]('(/event/action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') AS [sql_text],
    
35. [sw].[stat_update_dtl].[value]('(/event/action[@name="client_app_name"]/value)[1]','VARCHAR(MAX)') AS [client_app_name]
    
36. FROM [#stat_auto_update_event] AS [sw]  
    
37. )
    
38. SELECT  
    
39.         DB_NAME([cte_stat].[database_id]) AS [database_name] ,
    
40.         DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), [cte_stat].[event_time]) AS [event_time] ,
    
41.         [cte_stat].[event_name] ,
    
42.         OBJECT_NAME([cte_stat].[object_id],[cte_stat].[database_id]) AS object_name,
    
43.         [cte_stat].[index_id] ,
    
44.         [cte_stat].[job_type] ,
    
45.         [cte_stat].[status] ,
    
46.         [cte_stat].[sample_pct],
    
47.         [cte_stat].[duration] ,
    
48.         [cte_stat].[statistics_list],
    
49.         [cte_stat].[sql_text],
    
50.   [cte_stat].[client_app_name]
    
51. FROM cte_stat
    
52. ORDER BY [cte_stat].[event_time];

复制代码

关于扩展信息捕获的aut_stat数据，status状态一般有下面一些值(状态)，此中Loading stats without updating通常指的是加载统计信息而不举行更新操作

• Loading stats without updating
  
• Other
  
• Loading and updating stats
  

那么利用扩展事件追踪统计自动统计信息更新，有哪一些用途呢？ 下面是我简单的一些总结，不光仅局限于此，你也可以扩展其用途。

• 追踪分析自动统计信息的采样比例
  
• 分析SQL语句实行计划变化的原因。
  
• 为手工更新统计信息的频率与表对象提供数据支撑
  
• 研究自动统计信息更新触发的一些机制。
  

参考资料

• https://dba.stackexchange.com/questions/331860/use-extended-events-to-track-autoupdate-statistics-on-a-specific-table
  

扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮助“保举"一下，您的“保举”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面显着位置给出原文连接.
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。