原始产品版本：SQL Server
原始 KB 数： 243589
总结
本文介绍如何排查SQL Server中运行缓慢的查询，这是数据库应用程序遇到的最常见性能问题之一。 它提供了一种方法，用于确定查询是缓慢的，因为它 正在等待 瓶颈，还是因为它长时间在 CPU 上运行（正在 执行 ）。 确定查询属于哪个类别后，可以应用匹配的解析，例如减少等待、优化索引、更新统计信息、检查查询计划或解析参数敏感计划。
此方法适用于SQL Server。 在排查Azure SQL 数据库和Azure SQL 托管实例查询性能问题时，将等待查询与正在运行的查询分离的高级方法也有助于，尽管这些工具、权限和资源选项在这些产品中有所不同。
如何在SQL Server中识别运行缓慢的查询
若要确定 SQL Server 实例上存在查询性能问题，请首先检查查询的执行时间（已用时间）。 检查时间是否超过根据已建立的性能基线设置的阈值（以毫秒为单位）。 例如，在压力测试环境中，可能会为工作负荷建立不超过 300 毫秒的阈值，然后使用该阈值。
接下来，确定超出该阈值的所有查询，重点关注每个单个查询及其预先建立的基线持续时间。 最终，业务用户关注数据库查询的总体持续时间，因此主要关注执行持续时间。 收集其他指标（如 CPU 时间和逻辑读取）以帮助缩小调查范围。
如果对数据库启用了查询存储，还可以使用内置报表查找最长持续时间的查询并比较其随时间推移的性能。
对于当前正在执行的语句，请检查sys.dm_exec_requests中的total_elapsed_time和cpu_time列。 运行以下查询以获取数据：
SELECT
req.session_id
, req.total_elapsed_time AS duration_ms
, req.cpu_time AS cpu_time_ms
, req.total_elapsed_time - req.cpu_time AS wait_time
, req.logical_reads
, SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(ST.text)
ELSE req.statement_end_offset
END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '),
1, 512) AS statement_text
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;
要检查查询的历史执行情况，请查看sys.dm_exec_query_stats中的last_elapsed_time和last_worker_time列。 运行以下查询以获取数据：
SELECT t.text,
(qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
(qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.total_logical_writes / qs.execution_count AS avg_writes,
(qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '
-- Replace
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC
注意
如果 avg_wait_time 显示负值，则它是并行 查询。
如果在 SQL Server Management Studio（SSMS）、 sqlcmd 或 Visual Studio Code 的 MSSQL 扩展中按需执行查询，请使用 SET STATISTICS TIMEON 和 SET STATISTICS IOON 运行它。
SET STATISTICS TIME ON
SET STATISTICS IO ON
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
然后，从 消息中，你将看到 CPU 时间、已用时间和逻辑读取，如下所示：
Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 460 ms, elapsed time = 470 ms.
如果可以收集查询计划，请检查执行计划属性中的数据。
运行包含 实际执行计划的 查询。
从 执行计划中选择最左侧的运算符。
从 “属性”中找到并展开 “QueryTimeStats” 属性。
检查 已用时间 和 CPU时间。
运行与等待：为什么查询SQL Server速度缓慢？
如果发现超出预定义阈值的查询，请检查它们可能很慢的原因。 SQL Server查询性能问题的原因分为两类：正在运行或等待。
等待：查询可能很慢，因为它们长时间在瓶颈处等待。 请参阅在等待类型中瓶颈的详细列表。
RUNNING：查询可能很慢，因为它们长时间运行。 换句话说，这些查询会主动使用 CPU 资源。
查询在其整个生命周期（持续时间）内，可能会运行一段时间，并等待一段时间。 但是，你的重点是确定哪个是导致其持续时间过长的主要类别。 因此，第一个任务是确定查询所属的类别。 很简单：如果查询未运行，则它正在等待。 理想情况下，查询在运行状态下花费了大部分时间，并且等待资源的时间很少。 此外，在最佳情况下，查询在预先确定的基线内或以下运行。 比较查询的已用时间和 CPU 时间，以确定问题类型。
类型 1：CPU 绑定（运行程序）
如果 CPU 时间接近、等于或高于已用时间，则可以将其视为 CPU 绑定查询。 例如，如果已用时间为 3000 毫秒（ms），并且 CPU 时间为 2900 毫秒，则表示大部分已用时间都花在 CPU 上。 然后，我们可以说这是一个 CPU 绑定的查询。
运行（CPU 绑定）查询的示例：
已用时间（ms）
CPU 时间(毫秒)
读取次数（逻辑）
3200
3000
300000
1080
1000
20
逻辑读取 - 读取缓存中的数据/索引页 - 通常是 SQL Server 中 CPU 使用率的驱动因素。 在某些情况下，CPU 使用可能来自其他来源，例如 while 循环（在 T-SQL 或其他代码中，如 XProcs 或 SQL CLR 对象）。 表中的第二个示例演示了这种情况，其中大多数 CPU 不是来自读取。
注意
如果 CPU 时间大于持续时间，则表示执行并行查询;多个线程同时使用 CPU。 有关详细信息，请参阅 并行查询 - 运行程序或等待器。
类型 2：等待瓶颈（服务员）
如果经过的时间明显大于 CPU 时间，则查询受制于瓶颈。 已用时间包括在 CPU 上执行查询的时间（CPU 时间）以及等待资源释放的时间（等待时间）。 例如，如果经过的时间为 2000 毫秒，并且 CPU 时间为 300 毫秒，则等待时间为 1700 毫秒（2000 - 300 = 1700）。 有关详细信息，请参阅 “等待类型”。
等待查询的示例：
已用时间（ms）
CPU 时间(毫秒)
读取次数（逻辑）
2000
300
28000
10080
700
80000
并行查询 - 执行器或等待者
并行查询可能会使用比总持续时间更多的 CPU 时间。 并行度的目标是允许多个线程同时运行查询的各个部分。 在时钟时间的 1 秒内，查询可以通过执行 8 个并行线程来使用 8 秒的 CPU 时间。 因此，根据流逝时间和 CPU 时间的差异来判断查询是 CPU 密集型还是处于等待状态变得困难。 但是，作为一般规则，请遵循上述两节中列出的原则。 摘要为：
如果已用时间远远大于 CPU 时间，请考虑它为等待进程。
如果 CPU 时间大于运行时间，请视之为一个运行实例。
并行查询的示例：
已用时间（ms）
CPU 时间(毫秒)
读取次数（逻辑）
1200
8100
850000
3080
12300
1500000
故障排除方法的高级视觉表示形式
诊断并解决SQL Server中等待的查询
如果确定感兴趣的查询是服务员，请专注于解决瓶颈问题。 否则，请转到 “诊断”并解决正在运行的查询。
若要优化正在等待瓶颈的查询，请确定等待的时间以及瓶颈的位置（等待类型）。
确认等待类型后，请减少等待时间或完全消除等待时间。
若要计算近似等待时间，请从查询运行时间中减去 CPU 时间（工作时间）。 通常，CPU 时间是实际执行时间，查询生命周期的剩余部分用于等待。
如何计算近似等待持续时间的示例：
已用时间（ms）
CPU 时间(毫秒)
等待时间（ms）
3200
3000
200
7080
1000
6080
确定瓶颈或等待
若要标识历史上等待时间较长的查询（例如，>20% 的总运行时间为等待时间），请运行以下查询。 自 SQL Server 启动以来，此查询使用缓存查询计划的性能统计信息。
SELECT t.text,
qs.total_elapsed_time / qs.execution_count
AS avg_elapsed_time,
qs.total_worker_time / qs.execution_count
AS avg_cpu_time,
(qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count
AS avg_wait_time,
qs.total_logical_reads / qs.execution_count
AS avg_logical_reads,
qs.total_logical_writes / qs.execution_count
AS avg_writes,
qs.total_elapsed_time
AS cumulative_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE 1.0 * (qs.total_elapsed_time - qs.total_worker_time) / NULLIF(qs.total_elapsed_time, 0)
> 0.2
ORDER BY qs.total_elapsed_time / qs.execution_count DESC
若要识别当前执行时间超过 500 毫秒的查询，请运行以下查询：
SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE wait_time > 500
AND is_user_process = 1
如果可以收集查询计划，请从 SSMS 中的执行计划属性检查 WaitStats：
请在运行查询时启用 包含实际执行 计划的选项。
在“执行计划”选项卡中右键单击最左侧的运算符
选择 “属性”，然后选择 “WaitStats” 属性。
检查 WaitTimeMs 和 WaitType。
如果熟悉 PSSDiag/SQLdiag 或 SQL LogScout LightPerf/GeneralPerf 方案，请考虑使用其中任一方案收集性能统计信息并识别 SQL Server 实例上的等待查询。 可以使用 SQL Nexus 导入收集的数据并分析性能数据。
帮助消除或减少等待的参考
每种等待类型的原因和解决方法各不相同。 没有一种常规方法来解析所有等待类型。 下面是排查和解决常见等待类型问题的文章：
了解和解决阻止问题（LCK_M_*）
了解并解决 Azure SQL 数据库阻塞问题
排查 I/O 问题导致的 SQL Server 性能缓慢问题（PAGEIOLATCH_*、WRITELOG、IO_COMPLETION、BACKUPIO）
解决 SQL Server 中的最后一页插入 PAGELATCH_EX 争用
内存授予解释和解决方案（RESOURCE_SEMAPHORE）
排查ASYNC_NETWORK_IO等待类型导致的慢查询问题
使用 AlwaysOn 可用性组排查高HADR_SYNC_COMMIT等待类型问题
工作原理：CMEMTHREAD 和调试它们
使并行度等待可操作（CXPACKET 和 CXCONSUMER）
THREADPOOL 等待
有关许多等待类型及其所指示内容的说明，请参阅《等待类型》中的表。
在 SQL Server 中诊断和解决正在运行的查询
如果 CPU（工作线程）时间非常接近整体运行时间，则查询将在大部分时间里执行。 通常，当SQL Server引擎驱动高 CPU 使用率时，高 CPU 使用率来自驱动大量逻辑读取（最常见的原因）的查询。
为确定当前导致 CPU 使用率高的查询，请运行以下语句：
SELECT TOP 10 s.session_id,
r.status,
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
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,
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))
+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
如果查询当前未占用 CPU，可运行以下语句查找历史 CPU 限制型查询：
SELECT TOP 10 qs.last_execution_time, st.text AS batch_text,
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,
(qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
(qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
(qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
(qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC
解决长时间运行的、受制于 CPU 的查询的问题的常用方法
检查查询的查询计划
更新统计信息
查明并应用缺少的索引。 有关如何识别缺失索引的更多步骤，请参阅 使用缺少索引建议优化非聚集索引
重新设计或重写查询
查明并解决参数敏感计划问题
确定并解决 SARG 能力问题
确定并解决与 行目标 有关的问题，这些问题可能因为 TOP、EXISTS、IN、FAST、SET ROWCOUNT、OPTION（FAST N）导致长时间运行的嵌套循环。 有关详细信息，请参阅 “行目标消失” 和 “显示计划”增强功能 - 行目标 EstimateRowsWithoutRowGoal
评估和解决 基数估计 问题。 有关详细信息，请参阅 从 SQL Server 2012 或更低版本升级到 2014 或更高版本后降低的查询性能
确定并解决似乎从未完成的查询。 有关详细信息，请参阅排查似乎从未以SQL Server结尾的查询。
识别并解决 受优化器超时影响的慢查询
确定高 CPU 性能问题。 有关详细信息，请参阅 SQL Server 中的高 CPU 使用率问题疑难解答
对在两台服务器上具有显著性能差异的查询进行故障排除
增加系统上的计算资源 (CPU)
使用窄和宽执行计划排查 UPDATE 性能问题
相关内容
SQL Server 和 Azure SQL 托管实例中可检测的查询性能瓶颈类型
性能监视和优化工具
SQL Server 自动优化
SQL Server 索引体系结构和设计指南
排查SQL Server中的查询超时错误
排查 SQL Server 中的高 CPU 使用率问题
从 SQL Server 2012 或更低版本升级到 2014 或更高版本后查询性能下降