摘要
本文件包含一系列用于 SQL Server 性能排查的 SQL 脚本，主要用于分析查询执行统计信息，识别高 CPU 消耗、频繁执行、高 I/O 等性能瓶颈。
1. 最消耗 CPU 资源的查询计划
SELECT TOP 10
total_worker_time, -- 总CPU工作时间（微秒）
last_worker_time, -- 上次执行的CPU时间
max_worker_time, -- 最大单次CPU时间
min_worker_time, -- 最小单次CPU时间
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text -- 提取的SQL语句文本
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY max_worker_time DESC
用途说明： 识别单次执行CPU消耗最高的查询。
注意事项： max_worker_time反映的是单次执行的峰值CPU消耗。
2. 最频繁使用的查询计划
SELECT TOP 10
creation_time, -- 查询计划编译时间
last_execution_time, -- 最后执行时间
execution_count, -- 总执行次数
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text -- 提取的SQL语句文本
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY execution_count DESC
用途说明： 找出执行最频繁的查询，可能需优化缓存或索引。
注意事项： 高频率查询即使单次消耗低，累计影响也可能很大。
3. 单次执行平均资源消耗最高的查询
SELECT Top 10
plan_generation_num, -- 计划生成编号（重新编译次数）
creation_time, -- 计划创建时间
total_elapsed_time/execution_count as avg, -- 平均执行时间（微秒）
last_execution_time, -- 最后执行时间
execution_count, -- 执行次数
total_worker_time, -- 总CPU时间
total_physical_reads, -- 物理读取次数
total_logical_reads, -- 逻辑读取次数
total_logical_writes, -- 逻辑写入次数
total_elapsed_time, -- 总执行时间
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text -- SQL语句文本
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY avg desc
用途说明： 识别单次执行平均资源消耗最高的查询。
注意事项： avg字段综合反映了查询的单次性能表现。
4. 累计资源消耗最高的执行计划
SELECT Top 10
plan_generation_num, -- 计划生成编号
creation_time, -- 创建时间
last_execution_time, -- 最后执行时间
execution_count, -- 执行次数
total_worker_time, -- 总CPU时间
total_physical_reads, -- 物理读取总次数
total_logical_reads, -- 逻辑读取总次数
total_logical_writes, -- 逻辑写入总次数
total_elapsed_time, -- 总执行时间
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text -- SQL语句文本
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY total_elapsed_time desc
用途说明： 找出累计资源消耗最大的查询。
注意事项： 关注高累计消耗的查询对系统整体性能的影响。
5. 执行时间最长的10条SQL（详细版）
SELECT top 10
(total_elapsed_time / execution_count)/1000 N'平均时间ms', -- 平均执行时间（毫秒）
total_elapsed_time/1000 N'总花费时间ms', -- 总执行时间（毫秒）
total_worker_time/1000 N'所用的CPU总时间ms', -- 总CPU时间（毫秒）
total_physical_reads N'物理读取总次数', -- 物理读取次数
total_logical_reads/execution_count N'每次逻辑读次数', -- 平均逻辑读取次数
total_logical_reads N'逻辑读取总次数', -- 逻辑读取总次数
total_logical_writes N'逻辑写入总次数', -- 逻辑写入总次数
execution_count N'执行次数', -- 执行次数
creation_time N'语句编译时间', -- 编译时间
last_execution_time N'上次执行时间', -- 最后执行时间
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句', -- SQL语句文本
qp.query_plan -- 查询执行计划
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%' -- 过滤条件
ORDER BY total_elapsed_time / execution_count DESC;
用途说明： 全面分析执行时间最长的查询，包含执行计划。
注意事项：
- 包含
query_plan字段，可查看具体执行计划 - WHERE条件可自定义筛选（如：
like '%user%'查找包含user的SQL）
6. 执行最慢的SQL语句
SELECT TOP 10
(total_elapsed_time / execution_count)/1000 N'平均时间ms', -- 平均执行时间
total_elapsed_time/1000 N'总花费时间ms', -- 总执行时间
total_worker_time/1000 N'所用的CPU总时间ms', -- 总CPU时间
total_physical_reads N'物理读取总次数', -- 物理读取次数
total_logical_reads/execution_count N'每次逻辑读次数', -- 平均逻辑读
total_logical_reads N'逻辑读取总次数', -- 逻辑读总次数
total_logical_writes N'逻辑写入总次数', -- 逻辑写总次数
execution_count N'执行次数', -- 执行次数
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句', -- SQL语句
creation_time N'语句编译时间', -- 编译时间
last_execution_time N'上次执行时间' -- 最后执行时间
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like 'fetch%' -- 过滤条件
ORDER BY total_elapsed_time / execution_count DESC;
用途说明： 专门针对执行速度慢的查询进行分析。
注意事项： 过滤条件可根据需要调整。
7. 最耗时的前N条T-SQL语句（按计划分组）
-- 给N赋初值为30
declare @n int set @n=30
;with maco as
(
select top (@n)
plan_handle, -- 执行计划句柄
sum(total_worker_time) as total_worker_time, -- 总CPU时间
sum(execution_count) as execution_count, -- 总执行次数
count(1) as sql_count -- 包含的SQL语句数量
from sys.dm_exec_query_stats
group by plan_handle
order by sum(total_worker_time) desc
)
select t.text, -- 完整SQL文本
a.total_worker_time, -- 总CPU时间
a.execution_count, -- 执行次数
a.sql_count -- SQL语句数量
from maco a
cross apply sys.dm_exec_sql_text(plan_handle) t
用途说明： 按执行计划分组分析，识别相关SQL的整体性能影响。
注意事项： 通过修改@n值控制返回结果数量。
8. 平均耗CPU最多的SQL（SQL Server 2005+）
SELECT TOP 5
total_worker_time / execution_count AS [Avg CPU Time], -- 平均CPU时间
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 -- SQL语句
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC
用途说明： 简单快速的CPU消耗分析。
注意事项： 适用于SQL Server 2005及以上版本。
9. 平均耗CPU最多的SQL（SQL Server 2008+增强版）
SELECT TOP 20
total_worker_time/1000 AS [总消耗CPU 时间(ms)], -- 总CPU时间（毫秒）
execution_count [运行次数], -- 执行次数
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)], -- 平均CPU时间
last_execution_time AS [最后一次执行时间], -- 最后执行时间
min_worker_time /1000 AS [最小执行时间(ms)], -- 最小CPU时间
max_worker_time /1000 AS [最大执行时间(ms)], -- 最大CPU时间
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END
-qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], -- SQL语句片段
qt.text [完整语法], -- 完整SQL文本
qt.dbid, dbname=db_name(qt.dbid), -- 数据库ID和名称
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName -- 对象ID和名称
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1 -- 只分析执行超过1次的查询
ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC
用途说明： 详细的CPU消耗分析，包含数据库和对象信息。
注意事项： 需要SQL Server 2008及以上版本。
10. 总耗CPU最多的SQL
SELECT TOP 20
total_worker_time/1000 AS [总消耗CPU 时间(ms)], -- 总CPU时间
execution_count [运行次数], -- 执行次数
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)], -- 平均CPU时间
last_execution_time AS [最后一次执行时间], -- 最后执行时间
max_worker_time /1000 AS [最大执行时间(ms)], -- 最大CPU时间
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END
-qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], -- SQL语句片段
qt.text [完整语法], -- 完整SQL文本
qt.dbid, dbname=db_name(qt.dbid), -- 数据库信息
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName -- 对象信息
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1 -- 过滤单次执行的查询
ORDER BY total_worker_time DESC -- 按总CPU时间排序
用途说明： 识别累计CPU消耗最大的查询。
注意事项： 关注高累计消耗对系统资源的长期影响。
11. 内存利用率查询
SELECT
sqlserver_start_time, -- SQL Server启动时间
(committed_kb/1024) AS Total_Server_Memory_MB, -- 已提交内存（MB）
(committed_target_kb/1024) AS Target_Server_Memory_MB -- 目标内存（MB）
FROM sys.dm_os_sys_info;
用途说明： 查看SQL Server内存使用情况。
注意事项： 帮助判断内存压力情况。
通用注意事项
- 权限要求： 需要
VIEW SERVER STATE权限才能访问DMV - 数据时效性： 统计信息在SQL Server重启后会重置
- 性能影响： 查询DMV对性能影响较小，但应避免在生产高峰频繁执行
- 版本兼容性： 各脚本标注了适用的SQL Server版本
- 结果解读： 高资源消耗不一定代表有问题，需结合业务场景分析
- 过滤条件： 可根据需要修改WHERE条件进行针对性分析