一文详解SQL Server如何跟踪自动统计信息更新_MsSqlserver

sql server数据库中，我们都清楚统计信息对于优化器来说非常重要。一般情况下，我们会开启"自动更新统计信息"(auto update statistics)这个选项，以便数据库能自动更新过期/过时的统计信息，因为过期/过时的统计信息可能会导致数据库生成一个糟糕的执行计划，sql性能将会大打折扣，举一个例子，我们大脑做一些决策的时候，严重依赖所获取做决策信息的真实性与准确性，如果你所获得的信息是错误的，那么十有八九你会做出一个严重错误的决定。例如，如果当下环境中，你获取的信息：”买房稳赚不赔;买房会抗通胀......“是过时/错误的信息，那么你就会为当下的决策付出惨痛代价。

"自动更新统计信息"固然是不错的一个功能，但是很多人对它内部的原理知之甚少。对于"自动更新统计信息"是否开启也是有一些争论的。如果你监控发现一个sql的执行计划经常出现变化，除了参数嗅探外等因素外，那么你要考虑一下可能是因为sql语句中所涉及的表的统计信息自动更新导致。个人曾遇到一个案例，sql语句的执行计划在凌晨2点变了，而且是性能变差，具体原因是在这个时间段，有一个作业会归档清理数据，导致触发自动统计信息更新，而它使用的是自动采样比例，而由于采样比例过低，导致优化器生成了一个较差的执行计划。如果你不用扩展事件去跟踪、分析的话，那么真的很难搞清楚为什么出现这种玄幻的现象。

下面是一个sql执行计划经常出现变化的例子的截图，来自solarwinds的dpa。

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

创建扩展事件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

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

alter event session [stat_auto_update_event] on server
state = start;

此时，你就可以用下面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\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];

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

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

注意：要根据实际情况调整相关值，例如[database_id]、[object_id]的值。

手动构造一些条件，触发表test自动更新统计信息，此时，你可以使用ssms工具查看扩展事件捕获的一些数据了，如下截图所示：

当然，你也可以使用下面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];