对 SQL Server 中似乎永不结束的查询进行故障排除

本文介绍查询似乎从未完成的问题的故障排除步骤，或者完成查询可能需要几个小时或几天时间。

什么是永不结束的查询？

本文档重点介绍继续执行或编译的查询，即 CPU 继续增加。 它不适用于被阻止或等待某些从未释放的资源的查询（CPU 保持不变或更改很少）。

重要

如果查询保留以完成其执行，最终将完成。 可能需要几秒钟，或者可能需要几天时间。

术语永不结束用于描述实际上查询最终完成时未完成的查询的感知。

标识永不结束的查询

若要确定查询是持续执行还是停滞在瓶颈上，请执行以下步骤：

1. 运行以下查询：

   DECLARE @cntr int = 0
   
   WHILE (@cntr < 3)
   BEGIN
       SELECT TOP 10 s.session_id,
                       r.status,
                       r.wait_time,
                       r.wait_type,
                       r.wait_resource,
                       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,
                       atrn.name as transaction_name,
                       atrn.transaction_id,
                       atrn.transaction_state
           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
           LEFT JOIN (sys.dm_tran_session_transactions AS stran 
                JOIN sys.dm_tran_active_transactions AS atrn
                   ON stran.transaction_id = atrn.transaction_id)
           ON stran.session_id =s.session_id
           WHERE r.session_id != @@SPID
           ORDER BY r.cpu_time DESC
   
       SET @cntr = @cntr + 1
   WAITFOR DELAY '00:00:05'
   END
   

2. 检查示例输出。

   • 当你注意到与以下输出类似的输出时，本文中的故障排除步骤特别适用，其中 CPU 在经过的时间中按比例增加，且没有明显的等待时间。 请务必注意，在这种情况下，更改 logical_reads 并不相关，因为某些 CPU 绑定的 T-SQL 请求可能根本不执行任何逻辑读取（例如执行计算或 WHILE 循环）。

     session_id	status	cpu_time	logical_reads	wait_time	wait_type
     56	“正在运行”	7038	101000	0	Null
     56	可运行	12040	301000	0	Null
     56	“正在运行”	17020	523000	0	Null

   • 如果观察到的等待方案与以下情况类似，则本文不适用，其中 CPU 不会更改或更改非常轻微，并且会话正在等待资源。

     session_id	status	cpu_time	logical_reads	wait_time	wait_type
     56	suspended	0	3	8312	LCK_M_U
     56	suspended	0	3	13318	LCK_M_U
     56	suspended	0	5	18331	LCK_M_U

   有关详细信息，请参阅 诊断等待或瓶颈。

编译时间长

在极少数情况下，你可能会发现 CPU 在一段时间内持续增加，但这不是由查询执行驱动的。 相反，它可能由过多的编译（查询分析和编译）驱动。 在这些情况下，请检查 transaction_name 输出列并查找值 sqlsource_transform。 此事务名称指示编译。

收集诊断数据

SQL Server 2008 - SQL Server 2014（SP2 之前）

若要使用 SQL Server Management Studio （SSMS）收集诊断数据，请执行以下步骤：

1. 捕获估计的 查询执行计划 XML。

2. 查看查询计划，查看是否有明显的迹象表明速度缓慢。 典型的示例包括：

   • 表或索引扫描（查看估计行）。
   • 由大型外部表数据集驱动的嵌套循环。
   • 嵌套循环，该循环的内侧有一个大分支。
   • 表后台处理程序。
   • SELECT列表中需要很长时间来处理每一行的函数。

3. 如果查询随时快速运行，则可以捕获要比较的实际 XML 执行计划“快速”执行。

SQL Server 2014（SP2 之后）和 SQL Server 2016（SP1 之前）

这些版本的 SQL Server 中引入了轻型查询分析基础结构 。 它允许你在执行慢速查询期间捕获实际统计信息。 通过此故障排除功能，可以在运行时检查查询计划中的查询运算符，并了解查询中花费的大部分时间。

若要使用 轻型查询执行统计信息分析基础结构 v1 识别查询中的慢速步骤，请执行以下步骤：

1. 运行以下命令以启用 query_thread_profile XEvent：

   CREATE EVENT SESSION [NodePerfStats] ON SERVER
   ADD EVENT sqlserver.query_thread_profile(
     ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
       sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
       sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
       sqlserver.sql_text))
   ADD TARGET package0.ring_buffer(SET max_memory=(25600))
   WITH (MAX_MEMORY=4096 KB,
     EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
     MAX_DISPATCH_LATENCY=30 SECONDS,
     MAX_EVENT_SIZE=0 KB,
     MEMORY_PARTITION_MODE=NONE,
     TRACK_CAUSALITY=OFF,
     STARTUP_STATE=OFF);
   
   ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = START
   

2. 从应用程序启动受影响的永不结束查询。

3. 每隔一分钟左右多次运行以下命令，检查查询计划运算符的运行时执行统计信息：

   SELECT CONVERT (varchar(30), getdate(), 126) as runtime,
               qp.session_id,
               convert(nvarchar(48), qp.physical_operator_name) as physical_operator_name,
               qp.row_count,
               qp.estimate_row_count,
               qp.node_id,
               req.cpu_time,
               req.total_elapsed_time,
               substring
               (REPLACE
               (REPLACE
                   (SUBSTRING
                   (SQLText.text
                   , (req.statement_start_offset/2) + 1
                   , (
                       (CASE statement_END_offset
                           WHEN -1
                           THEN DATALENGTH(SQLText.text)  
                           ELSE req.statement_END_offset
                           END
                           - req.statement_start_offset)/2) + 1)
               , CHAR(10), ' '), CHAR(13), ' '), 1, 512)  AS active_statement_text
   FROM sys.dm_exec_query_profiles qp 
   RIGHT OUTER JOIN sys.dm_exec_requests req
       ON qp.session_id = req.session_id
   LEFT OUTER JOIN sys.dm_exec_sessions sess
       on req.session_id = sess.session_id
   LEFT OUTER JOIN sys.dm_exec_connections conn on conn.session_id = req.session_id
   OUTER APPLY sys.dm_exec_sql_text (ISNULL (req.sql_handle, conn.most_recent_sql_handle)) as SQLText
   WHERE req.session_id <> @@SPID 
       AND sess.is_user_process = 1 
   ORDER BY qp.session_id asc, row_count desc 
   --this is to prevent massive grants
   OPTION (max_grant_percent = 3, MAXDOP 1)
   

4. 捕获三到四个快照，间隔一分钟，以便提供足够的数据进行分析。 具体而言，可以比较每个运算符随时间推移 row_count 的数字，并查看显示行计数显著增加（百万或更多）。

5. 在 SSMS 的新查询窗口中，通过运行以下命令捕获问题查询的估计查询计划：

   SET SHOWPLAN_XML ON
   GO
   <problem query here>
   GO
   SET SHOWPLAN_XML OFF
   

6. 使用具有步骤 3 中查询标识的最高行计数的节点 ID，在估计的查询计划中查找同一个节点。 此步骤将有助于了解计划中哪个运算符是长时间执行的主要原因。

7. 运行以下命令停止 XEvent：

   ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP
   

SQL Server 2016（SP1 之后）和 SQL Server 2017

可以使用 轻型查询执行统计信息分析基础结构 v2 捕获具有行计数实际值的实时查询计划。 此分析基础结构允许在运行时检查查询计划中查询运算符，并了解查询中大部分时间花费在查询中的位置。

若要识别查询中的慢速步骤，请执行以下步骤：

1. 若要在这些版本的 SQL Server 上启用轻型基础结构，请使用以下方法之一：

   • 运行以下命令启用 跟踪标志 7412：

     DBCC TRACEON (7412, -1)
     

   • 或者，通过运行以下命令启用 query_thread_profile XEvent：

     CREATE EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER
     ADD EVENT sqlserver.query_plan_profile(
      ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
        sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
        sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
        sqlserver.sql_text))
     ADD TARGET package0.ring_buffer(SET max_memory=(25600))
     WITH (MAX_MEMORY=4096 KB,
      EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=30 SECONDS,
      MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,
      TRACK_CAUSALITY=OFF,
      STARTUP_STATE=OFF);
     
     ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = START
     

2. 从应用程序启动受影响的永不结束查询。

3. 使用类似于下面的命令来标识 Session_id 正在运行的永无止境查询：

   SELECT t.text, session_id 
   FROM sys.dm_exec_requests req
   CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
   

4. 运行以下命令三到四次间隔一分钟，以检查计划中的查询计划和实际统计信息。 请务必每次保存查询计划，以便比较它们并建立哪个查询运算符消耗大部分 CPU 时间。 具体而言，可以比较一段时间内每个运算符的行计数（实际行数），并查看哪些运算符显示行计数显著增加（百万或更多）。 替换为 <session_id> 在上一步骤 3 中找到的整数值。

   SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
   

5. 如果启动 XEvent，或禁用跟踪标志，请停止：

   ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = STOP
   -- or
   DBCC TRACEOFF (7412, -1)
   

SQL Server 2019 及更高版本

可以使用 轻型查询执行统计信息分析基础结构 v3 捕获具有行计数实际值的实时查询计划。 此分析基础结构允许在运行时检查查询计划中查询运算符，并了解查询中大部分时间花费在查询中的位置。 默认情况下，在 SQL Server 2019 上启用轻型分析。

若要识别查询中的慢速步骤，请执行以下步骤：

1. 从应用程序启动受影响的永不结束查询。

2. 使用类似于下面的命令来标识 Session_id 正在运行的永无止境查询：

   SELECT t.text, session_id 
   FROM sys.dm_exec_requests req
   CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
   

3. 运行以下命令三次或四次，检查计划中的查询计划和实际统计信息。 请务必每次保存查询计划，以便比较它们并建立哪个查询运算符消耗大部分 CPU 时间。 替换为 <session_id> 在上一步骤 3 中找到的整数值。

   SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
   

4. 具体而言，请选择query_plan列下的 XML 链接。 图形查询计划在新窗口中打开后，右键单击它并选择“ 另存为执行计划...”。重复这些步骤，捕获三到四个间隔一分钟的快照，以便提供足够的数据进行分析。 具体而言，可以比较一段时间内每个运算符的行计数（实际行数），并查看哪些运算符显示行计数显著增加（百万或更多）。

   注意

   如果未从 sys.dm_exec_query_statistics_xml中获取任何输出，可以通过运行以下命令来检查数据库选项 LAST_QUERY_PLAN_STATS 是否已禁用：

   SELECT name, value, value_for_secondary, is_value_default 
   FROM sys.database_scoped_configurations
   WHERE name = 'LAST_QUERY_PLAN_STATS'
   

   可以通过运行 ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON数据库级别启用最后一个查询计划统计信息。

查看收集的计划的方法

本部分将演示如何查看收集的数据。 它将使用 SQL Server 2016 SP1 和更高版本中收集的多个 XML 查询计划（使用扩展 *.sqlplan）。

按照以下步骤比较执行计划：

1. 打开以前保存的查询执行计划文件（.sqlplan）。

2. 右键单击执行计划的空白区域，然后选择“ 比较显示计划”。

3. 选择要比较的第二个查询计划文件。

4. 查找指示大量行在运算符之间流动的粗箭头。 然后选择箭头前后的运算符，并比较两个计划中的实际行数。

5. 比较第二和第三个计划，以查看行的最大流是否在同一运算符中发生。

   下面是一个示例：

   

解决方法

1. 确保为查询中使用的表更新统计信息。

2. 在查询计划中查找缺少的索引建议并应用任何索引。

3. 使用简化查询的目标重写查询：

   • 使用更具选择性 WHERE 的谓词来减少预先处理的数据。
   • 将其分开。
   • 在临时表中选择一些部分，并在以后联接它们。
   • 由于优化器行目标，在TOP长时间运行的查询中删除和 EXISTSFAST （T-SQL）。 或者，可以使用提示DISABLE_OPTIMIZER_ROWGOAL。 有关详细信息，请参阅 行目标消失流氓。
   • 避免在将语句合并为单个大型查询时使用通用表表达式（CTE）。

4. 尝试使用 查询提示 生成更好的计划：

   • HASH JOIN 或 MERGE JOIN 提示
   • FORCE ORDER 提示
   • FORCESEEK 提示
   • RECOMPILE
   • PLAN N'<xml_plan>'如果快速查询计划可以强制使用，请使用

5. 使用 查询存储 （QDS） 强制建立良好的已知计划（如果存在此类计划），如果 SQL Server 版本支持查询存储。

诊断等待或瓶颈

如果问题不是长时间运行的 CPU 驱动查询，请在此处提供此部分作为参考。 可以使用它对由于等待时间较长的查询进行故障排除。

若要优化正在等待瓶颈的查询，请确定等待的时间以及瓶颈的位置（等待类型）。 确认等待类型后，请减少等待时间或完全消除等待时间。

若要计算近似等待时间，请从查询运行时间中减去 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：

  1. 运行包含 实际执行 计划的查询。
  2. 在“执行计划”选项卡中右键单击最左侧的运算符
  3. 选择“属性”，然后选择 WaitStats 属性。
  4. 检查 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 等待

有关许多等待类型及其指示的说明，请参阅“等待类型”中的表。