本文介绍如何处理数据库应用程序在使用SQL Server时可能会遇到的性能问题：特定查询或查询组的性能缓慢。 以下方法将帮助你缩小查询速度缓慢问题的原因范围，并引导你解决问题。

若要确定SQL Server实例存在查询性能问题，请首先按查询的执行时间 (运行时间) 检查查询。 根据建立的性能基线，检查时间是否超过 () 设置的阈值（以毫秒为单位）。 例如，在压力测试环境中，你可能已为工作负荷设置了不超过 300 毫秒的阈值，并且可以使用此阈值。 然后，可以识别超过该阈值的所有查询，重点关注每个查询及其预先建立的性能基线持续时间。 最终，业务用户关心数据库查询的总体持续时间：因此，main重点在于执行持续时间。 收集 CPU 时间和逻辑读取等其他指标，以帮助缩小调查范围。

• 对于当前正在执行的语句，检查 sys.dm_exec_requests 中的total_elapsed_time列和cpu_time列。 运行以下查询以获取数据：

  SQL<button class="action position-relative display-none-print" type="button" data-bi-name="copy"></button><button class="action position-relative display-none-print" type="button" data-bi-name="copy">复制</button>

   

  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列。 运行以下查询以获取数据：

  SQL<button class="action position-relative display-none-print" type="button" data-bi-name="copy"></button><button class="action position-relative display-none-print" type="button" data-bi-name="copy">复制</button>

   

  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 TIMEON 和 SET STATISTICS IOON 运行它。

  SQL<button class="action position-relative display-none-print" type="button" data-bi-name="copy"></button><button class="action position-relative display-none-print" type="button" data-bi-name="copy">复制</button>

   

  SET STATISTICS TIME ON
  SET STATISTICS IO ON
  <YourQuery>
  SET STATISTICS IO OFF
  SET STATISTICS TIME OFF
  

  然后，从 “消息”中，你将看到 CPU 时间、运行时间和逻辑读取，如下所示：

  输出<button class="action position-relative display-none-print" type="button" data-bi-name="copy"></button><button class="action position-relative display-none-print" type="button" data-bi-name="copy">复制</button>

   

    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.
  

• 如果可以收集查询计划，检查执行计划属性中的数据。

  1. 运行包含 实际执行计划的 查询。

  2. 从 执行计划中选择最左侧的运算符。

  3. 在 “属性”中，展开 “QueryTimeStats 属性”。

  4. 选中 “已用时间” 和 “CpuTime”。

     

如果发现超过预定义阈值的查询，请检查它们速度缓慢的原因。 性能问题的原因可分为两类：正在运行或正在等待：

• 正在等待：查询可能会很慢，因为它们等待瓶颈的时间很长。 请参阅 等待类型中瓶颈的详细列表。

• 正在运行：查询可能很慢，因为它们长时间运行 (执行) 。 换句话说，这些查询主动使用 CPU 资源。

查询可以运行一段时间，并在其生存期内等待一段时间， (持续时间) 。 但是，你的重点是确定哪个类别是导致其长时间运行的主要类别。 因此，第一个任务是确定查询属于哪个类别。 很简单：如果查询未运行，则它正在等待。 理想情况下，查询的大部分运行时间都处于运行状态，而等待资源的时间很少。 此外，在最佳情况下，查询在预先确定的基线内或以下运行。 比较查询的运行时间和 CPU 时间，以确定问题类型。

如果 CPU 时间接近、等于或高于已用时间，则可以将其视为 CPU 绑定查询。 例如，如果运行时间为 3000 毫秒 (毫秒) 且 CPU 时间为 2900 毫秒，则表示大部分运行时间都花在 CPU 上。 然后，我们可以说这是一个受 CPU 限制的查询。

运行 (CPU 绑定) 查询的示例：

逻辑读取（读取缓存中的数据/索引页）是SQL Server中 CPU 使用率的驱动因素。 在某些情况下，CPU 使用可能来自其他源：T-SQL 中的 while 循环 (或其他代码（如 XProcs 或 SQL CRL 对象) ）。 表中的第二个示例演示了这样的方案，其中大部分 CPU 不是来自读取。

如果已用时间明显大于 CPU 时间，则查询正在等待瓶颈。 运行时间包括在 CPU 上执行查询的时间 (CPU 时间) ，以及等待释放资源的时间 (等待时间) 。 例如，如果运行时间为 2000 毫秒，CPU 时间为 300 毫秒，则等待时间为 1700 毫秒 (2000 - 300 = 1700) 。 有关详细信息，请参阅 等待类型。

等待查询的示例：

并行查询使用的 CPU 时间可能比总持续时间多。 并行度的目标是允许多个线程同时运行查询的一部分。 在时钟时间的一秒内，查询可以通过执行 8 个并行线程来使用 8 秒的 CPU 时间。 因此，根据已用时间和 CPU 时间差确定受 CPU 限制或等待的查询变得具有挑战性。 但是，作为一般规则，请遵循上述两节中列出的原则。 摘要如下：

• 如果已用时间远远大于 CPU 时间，则将其视为服务员。
• 如果 CPU 时间远远大于已用时间，则将其视为运行器。

并行查询的示例：

如果确定感兴趣的查询是服务员，则下一步是专注于解决瓶颈问题。 否则，请转到步骤 4： 诊断和解决正在运行的查询。

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

若要计算近似等待时间，请从查询的运行时间中减去 CPU 时间 (辅助角色时间) 。 通常，CPU 时间是实际执行时间，查询生存期的剩余部分正在等待。

有关如何计算近似等待持续时间的示例：

• 若要识别历史长时间等待查询 (例如， >总运行时间的 20% 是等待时间) ，请运行以下查询。 此查询使用自SQL Server开始以来缓存查询计划的性能统计信息。

  SQL<button class="action position-relative display-none-print" type="button" data-bi-name="copy"></button><button class="action position-relative display-none-print" type="button" data-bi-name="copy">复制</button>

   

  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 毫秒的当前正在执行的查询，请运行以下查询：

  SQL<button class="action position-relative display-none-print" type="button" data-bi-name="copy"></button><button class="action position-relative display-none-print" type="button" data-bi-name="copy">复制</button>

   

  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等待类型导致查询速度缓慢的问题
• 排查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 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

• 检查查询的查询计划
• 更新统计信息
• 标识并应用 缺少的索引。 有关如何识别缺失索引的更多步骤，请参阅 优化缺少索引的非聚集索引建议
• 重新设计或重写查询
• 识别和解析 参数敏感型计划
• 识别并解决 SARG 能力问题
• 识别并解决 以下行目标 问题：TOP、EXISTS、IN、FAST、SET ROWCOUNT、OPTION (FAST N) 会导致长时间运行的嵌套循环。 有关详细信息，请参阅 行目标消失流氓 和 显示计划增强功能 - 行目标 EstimateRowsWithoutRowGoal
• 评估和解决 基数估计 问题。 有关详细信息，请参阅从 SQL Server 2012 或更早版本升级到 2014 或更高版本后降低的查询性能
• 识别并解决似乎从未完成的查询，请参阅排查似乎永远不会结束的查询SQL Server
• 识别并解决 受优化器超时影响的慢查询
• 确定 CPU 性能过高的问题。 有关详细信息，请参阅排查SQL Server中的 CPU 使用率过高问题
• 排查显示两个服务器之间显著性能差异的查询
• 增加系统 (CPU) 上的计算资源
• 排查狭窄和较宽计划的 UPDATE 性能问题