排查SQL Server中运行缓慢的查询问题
原始产品版本：SQL Server
原始 KB 编号： 243589
简介
本文介绍如何处理数据库应用程序在使用SQL Server时可能会遇到的性能问题：特定查询或查询组的性能缓慢。 以下方法将帮助你缩小查询速度缓慢问题的原因范围，并引导你解决问题。
查找慢速查询
若要确定SQL Server实例存在查询性能问题，请首先按查询的执行时间 (运行时间) 检查查询。 根据建立的性能基线，检查时间是否超过 () 设置的阈值（以毫秒为单位）。 例如，在压力测试环境中，你可能已为工作负荷设置了不超过 300 毫秒的阈值，并且可以使用此阈值。 然后，可以识别超过该阈值的所有查询，重点关注每个查询及其预先建立的性能基线持续时间。 最终，业务用户关心数据库查询的总体持续时间：因此，main重点在于执行持续时间。 收集 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
ON和 SET STATISTICS IOON运行它。SET 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”。
正在运行与等待：为什么查询速度较慢？
如果发现超过预定义阈值的查询，请检查它们速度缓慢的原因。 性能问题的原因可分为两类：正在运行或正在等待：
正在等待：查询可能会很慢，因为它们等待瓶颈的时间很长。 请参阅 等待类型中瓶颈的详细列表。
正在运行：查询可能很慢，因为它们长时间运行 (执行) 。 换句话说，这些查询主动使用 CPU 资源。
查询可以运行一段时间，并在其生存期内等待一段时间， (持续时间) 。 但是，你的重点是确定哪个类别是导致其长时间运行的主要类别。 因此，第一个任务是确定查询属于哪个类别。 很简单：如果查询未运行，则它正在等待。 理想情况下，查询的大部分运行时间都处于运行状态，而等待资源的时间很少。 此外，在最佳情况下，查询在预先确定的基线内或以下运行。 比较查询的运行时间和 CPU 时间，以确定问题类型。
类型 1：CPU 绑定 (运行器)
如果 CPU 时间接近、等于或高于已用时间，则可以将其视为 CPU 绑定查询。 例如，如果运行时间为 3000 毫秒 (毫秒) 且 CPU 时间为 2900 毫秒，则表示大部分运行时间都花在 CPU 上。 然后，我们可以说这是一个受 CPU 限制的查询。
运行 (CPU 绑定) 查询的示例：
| 运行时间 (ms) | cpu time (ms) | 读取 (逻辑) |
|---|---|---|
| 3200 | 3000 | 300000 |
| 1080 | 1000 | 20 |
逻辑读取（读取缓存中的数据/索引页）是SQL Server中 CPU 使用率的驱动因素。 在某些情况下，CPU 使用可能来自其他源：T-SQL 中的 while 循环 (或其他代码（如 XProcs 或 SQL CRL 对象) ）。 表中的第二个示例演示了这样的方案，其中大部分 CPU 不是来自读取。
注意
如果 CPU 时间大于持续时间，则表示执行并行查询;多个线程同时使用 CPU。 有关详细信息，请参阅 并行查询 - 运行器或等待者。
类型 2：等待瓶颈 (服务员)
如果已用时间明显大于 CPU 时间，则查询正在等待瓶颈。 运行时间包括在 CPU 上执行查询的时间 (CPU 时间) ，以及等待释放资源的时间 (等待时间) 。 例如，如果运行时间为 2000 毫秒，CPU 时间为 300 毫秒，则等待时间为 1700 毫秒 (2000 - 300 = 1700) 。 有关详细信息，请参阅 等待类型。
等待查询的示例：
| 运行时间 (ms) | cpu time (ms) | 读取 (逻辑) |
|---|---|---|
| 2000 | 300 | 28000 |
| 10080 | 700 | 80000 |
并行查询 - 运行器或服务员
并行查询使用的 CPU 时间可能比总持续时间多。 并行度的目标是允许多个线程同时运行查询的一部分。 在时钟时间的一秒内，查询可以通过执行 8 个并行线程来使用 8 秒的 CPU 时间。 因此，根据已用时间和 CPU 时间差确定受 CPU 限制或等待的查询变得具有挑战性。 但是，作为一般规则，请遵循上述两节中列出的原则。 摘要如下：
- 如果已用时间远远大于 CPU 时间，则将其视为服务员。
- 如果 CPU 时间远远大于已用时间，则将其视为运行器。
并行查询的示例：
| 运行时间 (ms) | cpu time (ms) | 读取 (逻辑) |
|---|---|---|
| 1200 | 8100 | 850000 |
| 3080 | 12300 | 1500000 |
方法的高级视觉表示形式
诊断并解决等待的查询
如果确定感兴趣的查询是服务员，则下一步是专注于解决瓶颈问题。 否则，请转到步骤 4： 诊断和解决正在运行的查询。
若要优化等待瓶颈的查询，请确定等待时间以及瓶颈 (等待类型) 的位置。 确认 等待类型 后，请减少等待时间或完全消除等待。
若要计算近似等待时间，请从查询的运行时间中减去 CPU 时间 (辅助角色时间) 。 通常，CPU 时间是实际执行时间，查询生存期的剩余部分正在等待。
有关如何计算近似等待持续时间的示例：
| 运行时间 (ms) | cpu time (ms) | 等待时间 (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等待类型导致查询速度缓慢的问题
- 排查Always On可用性组的高HADR_SYNC_COMMIT等待类型问题
- 工作原理：CMEMTHREAD 和调试
- 使并行度 (CXPACKET 和 CXCONSUMER)
- THREADPOOL 等待
有关许多 Wait 类型的说明及其指示的内容，请参阅 “等待类型”中的表。
诊断并解决正在运行的查询
如果 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) 会导致长时间运行的嵌套循环。 有关详细信息，请参阅行Goals消失的流氓和显示计划增强功能 - 行目标估计RowsWithoutRowGoal
- 评估和解决 基数估计 问题。 有关详细信息，请参阅从 SQL Server 2012 或更早版本升级到 2014 或更高版本后降低的查询性能
- 识别并解决似乎从未完成的查询，请参阅排查似乎永远不会结束的查询SQL Server
- 识别并解决 受优化器超时影响的慢查询
- 确定 CPU 性能过高的问题。 有关详细信息，请参阅排查SQL Server中的 CPU 使用率过高问题
- 排查显示两个服务器之间显著性能差异的查询
- 增加系统 (CPU) 上的计算资源
- 排查狭窄和较宽计划的 UPDATE 性能问题
建议的资源
反馈
即将发布：在整个 2024 年，我们将逐步淘汰作为内容反馈机制的“GitHub 问题”，并将其取代为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。
提交和查看相关反馈