本文介紹如何處理數據庫應用程序在使用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 性能問題