在 SQL Server 中排查运行缓慢的查询
原始产品版本：SQL Server
原始 KB 数： 243589
简介
本文介绍如何处理数据库应用程序在使用 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 '<Your Query>%' -- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped. ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC注意
如果
avg_wait_time显示负值，则它是并行 查询。如果可以在 SQL Server Management Studio（SSMS）或 Azure Data Studio 中按需执行查询，请使用 SET STATISTICS TIME 和 SET STATISTICS IO
ON运行它。ONSET STATISTICS TIME ON SET STATISTICS IO ON <YourQuery> 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 属性。
检查 运行时间 和 CpuTime。
正在运行与等待：为什么查询速度较慢？
如果发现超出预定义阈值的查询，请检查它们可能很慢的原因。 性能问题的原因可以分为两个类别，即运行或等待：
等待：查询可能很慢，因为它们在等待瓶颈很长一段时间。 请参阅等待类型中瓶颈的详细列表。
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 CRL 对象）中）。 表中的第二个示例演示了这种情况，其中大多数 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 |
方法的高级视觉表示形式
诊断并解决正在等待的查询
如果确定感兴趣的查询是服务员，下一步是专注于解决瓶颈问题。 否则，请转到步骤 4： 诊断并解决正在运行的查询。
若要优化正在等待瓶颈的查询，请确定等待的时间以及瓶颈的位置（等待类型）。 确认等待类型后，请减少等待时间或完全消除等待时间。
若要计算近似等待时间，请从查询运行时间中减去 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 (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time > 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 等待
有关许多等待类型及其指示的说明，请参阅“等待类型”中的表。
诊断并解决正在运行的查询的问题
如果 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 性能问题