41、SQL Server 扩展事件与查询性能管理

职场萌新987

于 2025-08-16 10:01:20 发布

阅读量94

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server 2012管理精华文章标签： SQL Server 扩展事件查询性能管理

本文链接：https://blog.youkuaiyun.com/go5gopher/article/details/150568938

SQL Server 2012管理精华专栏收录该内容

46 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

SQL Server 扩展事件与查询性能管理

1. 扩展事件接口

扩展事件（Extended Events）是 SQL Server 中用于监控和诊断的强大工具。在创建扩展事件会话时，可设置内存分区模式，按 NUMA 节点或 CPU 对缓冲区进行分区，以提升大型 SQL Server 的性能。

创建新会话时，可选择直接创建或生成脚本稍后创建。以下是创建事件会话的脚本示例：

CREATE EVENT SESSION [Long Running Procedures] ON SERVER  
ADD EVENT sqlserver.module_end(SET collect_statement=(1) 
    WHERE ([duration]>(30000000)))  
ADD TARGET package0.event_file 
(SET filename=N'C:\Long Running Queries.xel') 
GO

若要启动会话，可在 SQL Server Management Studio (SSMS) 中右键单击会话，从上下文菜单中选择“启动会话”，对应的 T - SQL 语句如下：

ALTER EVENT SESSION [Long Running Procedures] 
ON SERVER 
STATE = start

1.1 查看事件

在 SSMS 中，有两种方式以可读网格格式查看事件数据：
- 实时数据监控：类似于 SQL Server Profiler，可监控实时事件流。操作步骤如下：
1. 右键单击之前创建的“Long Running Procedures”事件会话，选择“查看实时数据”。若会话未启动，需先启动。
2. 创建用于生成事件的存储过程：

USE tempdb; 
GO 
CREATE PROCEDURE TestXEvent 
(@WaitFor CHAR(8)) 
AS 
WAITFOR DELAY @WaitFor 
GO

3. 执行以下代码生成事件：

USE tempdb; 
GO 
EXEC TestXEvent '00:00:10' 
GO 3 
EXEC TestXEvent '00:00:31' 
GO 5 
EXEC TestXEvent '00:00:32' 
GO 2

4. 稍等片刻，即可看到符合会话条件的事件数据。若要停止数据馈送，可点击工具栏上的红色停止按钮或关闭 SSMS 窗口。

XEL 文件查看：若按上述步骤创建了事件会话并监控实时数据，会生成 XEL 文件。打开 XEL 文件的方式有：
- 打开单个 XEL 文件：在 SSMS 的“文件”菜单中选择“打开” -> “文件…”，浏览到文件所在目录；或直接将文件拖入 SSMS 窗口。
- 合并多个 XEL 文件：在 SSMS 的“文件”菜单中选择“打开” -> “合并扩展事件文件…”，在弹出窗口中点击“添加”按钮选择一个或多个文件，然后点击“确定”，所有文件的数据将显示在一个网格中。

1.2 列配置

查看扩展事件时，可根据需求添加或删除列。添加列的方法有：
- 右键单击列名，从上下文菜单中选择“选择列”，在弹出窗口中添加或删除列，还可创建新的合并列。例如，添加“statement”和“duration”列到所选列列表，然后点击“确定”。
- 右键单击详细信息窗格中的列，从上下文菜单中选择“在表中显示列”。

1.3 高级操作

SSMS 中的扩展事件工具栏提供了多种操作选项，帮助进行故障排除和数据分析：
- 书签功能：在网格中高亮显示一行，选择蓝色矩形可对所选事件添加书签，方便在书签间切换比较数据。
- 过滤功能：点击“过滤器”图标，在弹出窗口中设置过滤条件，仅显示指定时间范围内感兴趣的数据。
- 分组和聚合功能：若监控实时数据，需先停止数据流。操作步骤如下：
1. 点击扩展事件工具栏上的“分组”图标，在弹出窗口中选择“statement”列添加到分组列列表，点击“确定”，可看到每个组的语句总数。
2. 点击“聚合”图标，在弹出窗口中为“duration”列选择聚合类型（如 SUM），可选择按聚合结果排序，最后点击“确定”返回网格。

2. 查询性能管理

管理查询性能是数据库管理员 (DBA) 的重要技能。以下介绍几种查找和处理问题查询的方法。

2.1 关联 Profiler 和性能监视器

SQL Server 允许将性能日志导入 SQL Server Profiler，关联系统活动与特定查询，有助于定位性能问题。操作步骤如下：
1. 打开 SQL Server Profiler 和性能监视器（PerfMon）。
2. 在 PerfMon 中创建新的计数器日志：
- 展开“数据收集器集”，右键单击“用户定义”，选择“新建数据收集器集”。
- 为日志命名，如“Perfmon_Profiler_Demo”，选择“手动创建（高级）”选项，点击“下一步”。
- 勾选“性能计数器”复选框，点击“下一步”。
- 点击“添加计数器”按钮，确保“% Processor Time”在计数器列表中高亮显示，点击“添加”，然后点击“确定”返回向导。
- 记录日志文件的位置，点击“完成”创建新的数据收集器集。右键单击新的数据收集器集，选择“启动”开始收集数据。
3. 在 SQL Server Profiler 中启动新跟踪：
- 从“文件”菜单中选择“新建跟踪”，连接到 SQL 实例，选择“标准（默认）”模板，点击“运行”开始跟踪。
4. 生成服务器负载：执行以下脚本模拟一个糟糕的查询：

SELECT * FROM sys.messages  
CROSS JOIN sys.messages B  
WHERE B.language_id = 1033 AND 
      B.text like '%Warning%' AND 
      B.text like '%instance%'

关联两个跟踪：
- 停止、保存并重新打开 Profiler 跟踪，停止 PerfMon 计数器日志。
- 在 SQL Server Profiler 的“文件”菜单中选择“导入性能数据”，浏览到 PerfMon 日志文件的位置进行导入。
- 在弹出的“性能计数器限制”对话框中，确保选择“% Processor Time”，点击“确定”。此时，在 PerfMon 跟踪中选择一个点，Profiler 将定位到该时刻正在执行的查询。

2.2 使用动态管理视图 (DMVs) 查找问题查询

DMVs 是查找可能导致性能问题的查询的宝贵资源，常用的 DMVs 有 sys.dm_exec_query_stats 和 sys.dm_exec_requests 。

查找当前正在运行的查询：结合 sys.dm_exec_sql_text 动态管理函数和 sys.dm_exec_sessions DMV，可获取当前正在执行的查询信息，示例查询如下：

SELECT  blocking_session_id, 
        SUBSTRING(st.text, (r.statement_start_offset/2)+1,  
        ((CASE r.statement_end_offset 
          WHEN -1 THEN DATALENGTH(st.text) 
         ELSE r.statement_end_offset 
         END - r.statement_start_offset)/2) + 1) AS statement_text, 
        r.cpu_time, 
        r.total_elapsed_time, 
        r.reads, 
        r.writes, 
        r.logical_reads, 
        * 
FROM    sys.dm_exec_requests r JOIN 
        sys.dm_exec_sessions s 
          ON r.session_id = s.session_id CROSS APPLY  
        sys.dm_exec_sql_text(sql_handle) AS st 
ORDER BY R.cpu_time desc

查找缓存查询信息：使用 sys.dm_exec_query_stats DMV 可获取缓存查询计划的聚合性能统计信息。以下是两个示例查询：

--Top 10 queries by CPU 
SELECT TOP 10 
       qs.total_worker_time, 
       qs.execution_count, 
       SUBSTRING(st.text,(qs.statement_start_offset / 2) + 1, 
        ((CASE qs.statement_end_offset 
         WHEN -1 THEN DATALENGTH(st.text) 
         ELSE qs.statement_end_offset 
         END - qs.statement_start_offset) / 2 ) + 1) AS statement_text 
FROM    sys.dm_exec_query_stats qs CROSS APPLY  
        sys.dm_exec_sql_text(qs.sql_handle) AS st 
ORDER BY total_worker_time DESC  

--Top 10 queries by IO 
SELECT TOP 10 
        qs.total_logical_reads, 
        qs.total_logical_writes, 
       (qs.total_logical_reads + qs.total_logical_writes) AS TotalIO, 
        qs.execution_count, 
        SUBSTRING(st.text,(qs.statement_start_offset / 2) + 1, 
          ((CASE qs.statement_end_offset 
          WHEN -1 THEN DATALENGTH(st.text) 
          ELSE qs.statement_end_offset 
          END - qs.statement_start_offset) / 2) + 1) AS statement_text 
FROM    sys.dm_exec_query_stats qs CROSS APPLY  
        sys.dm_exec_sql_text(qs.sql_handle) AS st 
ORDER BY TotalIO DESC

此外，Microsoft 提供了一些基于 DMVs 的现成报告，可快速查找性能较差的查询。在 SSMS 中，右键单击 SQL Server 实例或数据库，选择“报告” -> “标准报告”可查看相关报告列表。

2.3 使用 DMVs 查找相似查询

在 SQL Server 2008 之前，查找和调优相似查询是一项艰巨任务。现在， sys.dm_exec_query_stats 和 sys.dm_exec_requests DMVs 中的 query_hash 和 query_plan_hash 列提供了更准确的方法。
- query_hash ：基于查询文本在发送到优化器之前计算的二进制哈希值。
- query_plan_hash ：基于编译后的查询计划计算的二进制哈希值。

示例操作如下（注意：仅在测试系统上运行，避免清除过程缓存影响性能）：
1. 清除计划缓存并执行相似查询：

DBCC FREEPROCCACHE 
GO 
SELECT * FROM sys.messages WHERE severity = 23 
GO 
SELECT * FROM sys.messages WHERE severity = 23 
GO 
SELECT * FROM sys.messages WHERE severity = 23; 
GO 
SELECT * FROM sys.messages WHERE severity=23 
GO 
SELECT * FROM sys.messages WHERE severity = 24 
GO 
SELECT * FROM sys.messages WHERE severity IN (24) 
GO 
SELECT * FROM sys.messages WHERE severity in (23,24) 
GO

查看查询生成的哈希值：

SELECT qs.query_hash, 
       qs.query_plan_hash, 
       st.text, 
       qs.execution_count, 
       qs.total_worker_time  
FROM sys.dm_exec_query_stats qs CROSS APPLY  
     sys.dm_exec_sql_text (plan_handle) st  
WHERE st.text like 'SELECT * FROM sys.messages%'  
ORDER BY qs.total_worker_time DESC

基于哈希值聚合数据：

SELECT qs.query_hash, 
       qs.query_plan_hash, 
       MIN(st.text) QueryTextLike, 
       SUM(qs.execution_count) ExecutionCount, 
       SUM(qs.total_worker_time)TotalWorkerTime  
FROM sys.dm_exec_query_stats qs CROSS APPLY  
     sys.dm_exec_sql_text (plan_handle) st  
WHERE st.text LIKE 'SELECT * FROM sys.messages%'  
GROUP BY qs.query_hash,  
         qs.query_plan_hash  
ORDER BY TotalWorkerTime DESC

通过聚合数据，可更准确地了解查询的执行次数和总工作时间，有助于聚焦实际使用最多资源的查询进行调优。

SQL Server 扩展事件与查询性能管理

3. 操作总结与对比

3.1 扩展事件操作总结

操作类型	操作步骤
创建事件会话	使用 `CREATE EVENT SESSION` 语句，示例代码如下： `sql<br>CREATE EVENT SESSION [Long Running Procedures] ON SERVER <br>ADD EVENT sqlserver.module_end(SET collect_statement=(1) <br> WHERE ([duration]>(30000000))) <br>ADD TARGET package0.event_file <br>(SET filename=N'C:\Long Running Queries.xel') <br>GO<br>`
启动事件会话	使用 `ALTER EVENT SESSION` 语句，示例代码如下： `sql<br>ALTER EVENT SESSION [Long Running Procedures] <br>ON SERVER <br>STATE = start<br>`
查看实时数据	1. 右键单击事件会话，选择“查看实时数据”，若未启动会话需先启动。 2. 创建存储过程 `TestXEvent` 并执行代码生成事件。 3. 稍等片刻查看事件数据，停止数据馈送可点击红色停止按钮或关闭窗口。
查看 XEL 文件	单个文件：在 SSMS“文件”菜单选“打开” -> “文件…”浏览目录或直接拖入文件；多个文件：在“文件”菜单选“打开” -> “合并扩展事件文件…”，添加文件后点击“确定”。
列配置	方法一：右键列名选“选择列”，在窗口中添加或删除列、创建合并列；方法二：右键详细信息窗格列选“在表中显示列”。
高级操作 - 书签	在网格中高亮行，选蓝色矩形添加书签，方便切换比较数据。
高级操作 - 过滤	点击“过滤器”图标，设置过滤条件显示指定时间范围数据。
高级操作 - 分组和聚合	1. 停止实时数据流。 2. 点击“分组”图标，选“statement”列分组。 3. 点击“聚合”图标，为“duration”列选聚合类型（如 SUM），可排序后点击“确定”。

3.2 查询性能管理操作总结

操作类型	操作步骤
关联 Profiler 和性能监视器	1. 打开 SQL Server Profiler 和 PerfMon。 2. 在 PerfMon 创建新计数器日志，记录日志位置并启动。 3. 在 Profiler 启动新跟踪。 4. 执行脚本模拟糟糕查询。 5. 停止、保存并重新打开 Profiler 跟踪，停止 PerfMon 日志，导入 PerfMon 日志到 Profiler，选择“% Processor Time”关联查询。
使用 DMVs 查找当前运行查询	使用以下查询： sql<br>SELECT blocking_session_id, <br> SUBSTRING(st.text, (r.statement_start_offset/2)+1, <br> ((CASE r.statement_end_offset <br> WHEN -1 THEN DATALENGTH(st.text) <br> ELSE r.statement_end_offset <br> END - r.statement_start_offset)/2) + 1) AS statement_text, <br> r.cpu_time, <br> r.total_elapsed_time, <br> r.reads, <br> r.writes, <br> r.logical_reads, <br> * <br>FROM sys.dm_exec_requests r JOIN <br> sys.dm_exec_sessions s <br> ON r.session_id = s.session_id CROSS APPLY <br> sys.dm_exec_sql_text(sql_handle) AS st <br>ORDER BY R.cpu_time desc<br>
使用 DMVs 查找缓存查询信息	示例查询如下： sql<br>--Top 10 queries by CPU <br>SELECT TOP 10 <br> qs.total_worker_time, <br> qs.execution_count, <br> SUBSTRING(st.text,(qs.statement_start_offset / 2) + 1, <br> ((CASE qs.statement_end_offset <br> WHEN -1 THEN DATALENGTH(st.text) <br> ELSE qs.statement_end_offset <br> END - qs.statement_start_offset) / 2 ) + 1) AS statement_text <br>FROM sys.dm_exec_query_stats qs CROSS APPLY <br> sys.dm_exec_sql_text(qs.sql_handle) AS st <br>ORDER BY total_worker_time DESC <br><br>--Top 10 queries by IO <br>SELECT TOP 10 <br> qs.total_logical_reads, <br> qs.total_logical_writes, <br> (qs.total_logical_reads + qs.total_logical_writes) AS TotalIO, <br> qs.execution_count, <br> SUBSTRING(st.text,(qs.statement_start_offset / 2) + 1, <br> ((CASE qs.statement_end_offset <br> WHEN -1 THEN DATALENGTH(st.text) <br> ELSE qs.statement_end_offset <br> END - qs.statement_start_offset) / 2) + 1) AS statement_text <br>FROM sys.dm_exec_query_stats qs CROSS APPLY <br> sys.dm_exec_sql_text(qs.sql_handle) AS st <br>ORDER BY TotalIO DESC<br>
使用 DMVs 查找相似查询	1. 清除计划缓存并执行相似查询。 `sql<br>DBCC FREEPROCCACHE <br>GO <br>SELECT * FROM sys.messages WHERE severity = 23 <br>GO <br>SELECT * FROM sys.messages WHERE severity = 23 <br>GO <br>SELECT * FROM sys.messages WHERE severity = 23; <br>GO <br>SELECT * FROM sys.messages WHERE severity=23 <br>GO <br>SELECT * FROM sys.messages WHERE severity = 24 <br>GO <br>SELECT * FROM sys.messages WHERE severity IN (24) <br>GO <br>SELECT * FROM sys.messages WHERE severity in (23,24) <br>GO<br>` 2. 查看查询哈希值。 `sql<br>SELECT qs.query_hash, <br> qs.query_plan_hash, <br> st.text, <br> qs.execution_count, <br> qs.total_worker_time <br>FROM sys.dm_exec_query_stats qs CROSS APPLY <br> sys.dm_exec_sql_text (plan_handle) st <br>WHERE st.text like 'SELECT * FROM sys.messages%' <br>ORDER BY qs.total_worker_time DESC<br>` 3. 基于哈希值聚合数据。 `sql<br>SELECT qs.query_hash, <br> qs.query_plan_hash, <br> MIN(st.text) QueryTextLike, <br> SUM(qs.execution_count) ExecutionCount, <br> SUM(qs.total_worker_time)TotalWorkerTime <br>FROM sys.dm_exec_query_stats qs CROSS APPLY <br> sys.dm_exec_sql_text (plan_handle) st <br>WHERE st.text LIKE 'SELECT * FROM sys.messages%' <br>GROUP BY qs.query_hash, <br> qs.query_plan_hash <br>ORDER BY TotalWorkerTime DESC<br>`

4. 总结与建议

通过上述介绍，我们了解了 SQL Server 中扩展事件接口和查询性能管理的相关知识和操作方法。扩展事件可帮助我们监控和诊断系统，而有效的查询性能管理能让我们快速定位和解决查询性能问题。

在实际应用中，建议按照以下步骤进行操作：
1. 监控系统 ：使用扩展事件创建合适的会话，监控系统的各种事件，如长时间运行的查询等。
2. 定位问题 ：当发现系统性能下降时，利用关联 Profiler 和性能监视器、DMVs 等方法定位问题查询。
3. 分析查询 ：对于定位到的问题查询，使用 DMVs 分析其性能指标，如 CPU 时间、IO 操作等。
4. 优化查询 ：根据分析结果，对查询进行优化，如添加索引、修改查询语句等。
5. 持续监控 ：优化后持续监控系统，确保性能得到提升且问题不再出现。

mermaid 流程图如下：

graph LR
    A[监控系统] --> B[定位问题]
    B --> C[分析查询]
    C --> D[优化查询]
    D --> E[持续监控]
    E -->|有问题| B
    E -->|无问题| A

总之，掌握这些技术和方法，能让我们更好地管理 SQL Server 系统，提高系统的性能和稳定性。