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

2025-03-21 02:50
文章标签 sql server 详解 一文 跟踪 更新 自动 统计 信息 database

本文主要是介绍一文详解SQL Server如何跟踪自动统计信息更新，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《一文详解SQLServer如何跟踪自动统计信息更新》SQLServer数据库中,我们都清楚统计信息对于优化器来说非常重要,所以本文就来和大家简单聊一聊SQLServer如何跟踪自动统计信息更新吧...

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

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

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

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

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

创建扩展事件stat_auto_update_eveXThwbdkIznt

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(MjsAX)') 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
        http://www.chinasem.cnAND [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 Server如何跟踪自动统计信息更新

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

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

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

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

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

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

以上就是一文详解SQL Server如何跟踪自动统计信息更新的详细内容，更多关于SQL Server信息更新的资料请关注China编程(www.chinasem.cn)其它相关文章！

这篇关于一文详解SQL Server如何跟踪自动统计信息更新的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

原文地址:http://www.cppcns.com/shujuku/mssql/704937.html
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.chinasem.cn/article/1153861。

相关文章

Linux中的缓冲区和文件系统详解

Linux中的缓冲区和文件系统详解

《Linux中的缓冲区和文件系统详解》：本文主要介绍Linux中的缓冲区和文件系统方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、FILE结构1、fd2、缓冲区二、文件系统1、固态硬盘2、逻辑地址LBA（一）数据块 Data blocks（二）inode表
阅读更多...
基于Spring实现自定义错误信息返回详解

基于Spring实现自定义错误信息返回详解

《基于Spring实现自定义错误信息返回详解》这篇文章主要为大家详细介绍了如何基于Spring实现自定义错误信息返回效果,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录背景目标实现产出背景Spring 提供了 @RestConChina编程trollerAdvice 用来实现 HTT
阅读更多...
kotlin中的数据转换方法(示例详解)

kotlin中的数据转换方法(示例详解)

《kotlin中的数据转换方法(示例详解)》这篇文章介绍了Kotlin中将数字转换为字符串和字符串转换为数字的多种方法,包括使用`toString()`、字符串模板、格式化字符串、处理可空类型等,同时... 目录1. 直接使用 toString() 方法2. 字符串模板（自动转换）3. 格式化字符串（控制输
阅读更多...
Linux进程终止的N种方式详解

Linux进程终止的N种方式详解

《Linux进程终止的N种方式详解》进程终止是操作系统中,进程的一个重要阶段,他标志着进程生命周期的结束,下面小编为大家整理了一些常见的Linux进程终止方式,大家可以根据需求选择... 目录前言一、进程终止的概念二、进程终止的场景三、进程终止的实现3.1 程序退出码3.2 运行完毕结果正常3.3 运行完毕
阅读更多...
Java实现XML与JSON的互相转换详解

Java实现XML与JSON的互相转换详解

《Java实现XML与JSON的互相转换详解》这篇文章主要为大家详细介绍了如何使用Java实现XML与JSON的互相转换,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1. XML转jsON1.1 代码目的1.2 代码实现2. JSON转XML3. JSON转XML并输出成指定的
阅读更多...
python中time模块的常用方法及应用详解

python中time模块的常用方法及应用详解

《python中time模块的常用方法及应用详解》在Python开发中,时间处理是绕不开的刚需场景,从性能计时到定时任务,从日志记录到数据同步,时间模块始终是开发者最得力的工具之一,本文将通过真实案例... 目录一、时间基石：time.time()典型场景：程序性能分析进阶技巧：结合上下文管理器实现自动计时
阅读更多...
SQL中的CASE WHEN用法小结

SQL中的CASE WHEN用法小结

《SQL中的CASEWHEN用法小结》文章详细介绍了SQL中的CASEWHEN函数及其用法,包括简单CASEWHEN和CASEWHEN条件表达式两种形式,并通过多个实际场景展示了如何使用CASEWH... 目录一、简单CASE WHEN函数：二、CASE WHEN条件表达式函数三、常用场景场景1：不同状态展
阅读更多...
利用Python和C++解析gltf文件的示例详解

利用Python和C++解析gltf文件的示例详解

《利用Python和C++解析gltf文件的示例详解》gltf,全称是GLTransmissionFormat,是一种开放的3D文件格式,Python和C++是两个非常强大的工具,下面我们就来看看如何... 目录什么是gltf文件选择语言的原因安装必要的库解析gltf文件的步骤1. 读取gltf文件2. 提
阅读更多...
CSS @media print 使用详解

CSS @media print 使用详解

《CSS@mediaprint使用详解》：本文主要介绍了CSS中的打印媒体查询@mediaprint包括基本语法、常见使用场景和代码示例，如隐藏非必要元素、调整字体和颜色、处理链接的URL显示、分页控制、调整边距和背景等，还提供了测试方法和关键注意事项，并分享了进阶技巧，详细内容请阅读本文，希望能对你有所帮助...
阅读更多...
python之流程控制语句match-case详解

python之流程控制语句match-case详解

《python之流程控制语句match-case详解》：本文主要介绍python之流程控制语句match-case使用,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐... 目录match-case 语法详解与实战一、基础值匹配（类似 switch-case）二、数据结构解构匹
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2