針對 SQL Server 中執行緩慢的查詢進行疑難解答

原始產品版本： SQL S
原始 KB 編號： 243589

簡介

本文說明如何處理資料庫應用程式在使用 SQL Server 時可能會遇到的效能問題：特定查詢或查詢群組的效能變慢。 下列方法可協助您縮小查詢問題緩慢的原因，並引導您進行解決。

尋找慢速查詢

若要確定您在 SQL Server 實例上有查詢效能問題，請先檢查查詢的運行時間 (經過的時間) 。 根據已建立的效能基準，檢查時間是否超過 (以毫秒為單位) 閾值。 例如，在壓力測試環境中，您可能已建立工作負載不超過 300 毫秒的閾值，而且您可以使用此閾值。 然後，您可以識別超過該閾值的所有查詢，將焦點放在每個個別查詢及其預先建立的效能基準持續時間。 最後，商務用戶會關心資料庫查詢的整體持續時間;因此，主要焦點在於執行持續時間。 收集其他計量，例如CPU時間和邏輯讀取，以協助縮小調查範圍。

• 如需目前正在執行的語句，請檢查 total_elapsed_time 和 cpu_timesys.dm_exec_requests中的數據行。 執行下列查詢以取得資料：

  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;
  

• 如需查詢的過去執行，請 檢查last_elapsed_time 和 last_worker_timesys.dm_exec_query_stats中的數據行。 執行下列查詢以取得資料：

  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 IO 來執行ON查詢。

  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.
  

• 如果您可以收集查詢計劃，請檢查 執行計劃屬性中的數據。

  1. 使用 [ 包含實際執行計劃 ] 執行查詢。

  2. 從 執行計劃中選取最左邊的運算元。

  3. 從 [屬性] 中，展開 [QueryTimeStats ] 屬性。

  4. 檢查 ElapsedTime 和 CpuTime。

     

執行與等候：為什麼查詢變慢？

如果您發現查詢超過預先定義的臨界值，請檢查其速度可能變慢的原因。 效能問題的原因可以分成兩個類別，執行中或等候中：

• 正在等候：查詢可能會因為長時間等待瓶頸而變慢。 請參閱 等候類型的瓶頸詳細清單。

• 執行中：查詢可能會很慢，因為它們執行 (執行) 很長一段時間。 換句話說，這些查詢會主動使用CPU資源。

查詢可以執行一段時間，並在其存留期 (持續時間) 等候一段時間。 不過，您的重點是判斷哪些是造成其長時間耗用時間的主要類別。 因此，第一個工作是建立查詢落在哪一個類別。 很簡單：如果查詢未執行，則會在等候中。 在理想情況下，查詢會將大部分經過的時間花在執行中狀態，而且很少等待資源。 此外，在最佳案例中，查詢會在預先決定的基準內或以下執行。 比較查詢的耗用時間和CPU時間，以判斷問題類型。

類型 1：CPU 系結 (執行器)

如果 CPU 時間接近、等於或高於經過的時間，您可以將它視為 CPU 系結查詢。 例如，如果經過的時間是 3000 毫秒， (毫秒) ，而 CPU 時間是 2900 毫秒，這表示大部分的耗用時間都花在 CPU 上。 然後我們可以說它是 CPU 系結的查詢。

執行 (CPU 系結) 查詢的範例：

經過的時間 (ms)	CPU 時間 (毫秒)	讀 (邏輯)
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 時間 (毫秒)	讀 (邏輯)
2000	300	28000
10080	700	80000

平行查詢 - 執行器或等候程式

平行查詢可能會使用比整體持續時間更多的CPU時間。 平行處理原則的目標是允許多個線程同時執行部分查詢。 在時鐘時間的一秒內，查詢可能會執行八個平行線程，以使用八秒的CPU時間。 因此，根據經過的時間和CPU時間差異來判斷CPU系結或等候查詢會變得很困難。 不過，一般規則是遵循上述兩節中所列的原則。 摘要為：

• 如果經過的時間遠大於 CPU 時間，請將它視為等候者。
• 如果 CPU 時間遠大於經過的時間，請將它視為執行器。

平行查詢的範例：

經過的時間 (ms)	CPU 時間 (毫秒)	讀 (邏輯)
1200	8100	850000
3080	12300	1500000

方法的高階視覺表示法

診斷和解決等候中的查詢

如果您確定感興趣的查詢是等候者，則下一個步驟是專注於解決瓶頸問題。 否則，請移至步驟 4： 診斷和解析執行中的查詢。

若要將等候瓶頸的查詢優化，請識別等候時間長度，以及瓶頸 (等候類型) 。 確認 等候類型 之後，請減少等候時間或完全消除等候。

若要計算大約的等候時間，請從查詢的經過時間減去 CPU 時間 (背景工作時間) 。 一般而言，CPU 時間是實際的運行時間，而查詢存留期的剩餘部分正在等候中。

如何計算大約等候持續時間的範例：

經過的時間 (ms)	CPU 時間 (毫秒)	等候時間 (毫秒)
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 資料庫封鎖問題
• 針對 (PAGEIOLATCH_*、WRITELOG、IO_COMPLETION、BACKUPIO) I/O 問題所造成的 SQL Server 效能緩慢問題進行疑難解答
• 解決在 SQL Server 中的最後一頁插入PAGELATCH_EX爭用
• 記憶體會授與說明和解決方案 (RESOURCE_SEMAPHORE)
• 針對ASYNC_NETWORK_IO等候類型所產生的慢速查詢進行疑難解答
• 針對具有 Always On 可用性群組的高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) 造成長時間執行巢狀循環的數據列 目標 問題。 如需詳細資訊，請參閱數據列 Goals 消失的 Rogue 和 Showplan 增強功能 - 數據列目標估計值RowsWithoutRowGoal
• 評估並解決 基數估計 問題。 如需詳細資訊，請參閱從 SQL Server 2012 或更早版本升級至 2014 或更新版本之後降低查詢效能
• 找出並解析看似永遠不會完成的查詢，請參閱疑難解答似乎永遠不會結束的查詢 SQL Server
• 識別並解決 受優化工具逾時影響的緩慢查詢
• 識別高 CPU 效能問題。 如需詳細資訊，請參閱針對 SQL Server 中的高 CPU 使用量問題進行疑難解答
• 針對顯示兩部伺服器之間顯著效能差異的查詢進行疑難解答
• 增加系統上的運算資源 (CPU)
• 針對縮小和寬方案的 UPDATE 效能問題進行疑難解答

建議的資源

• SQL Server 和 Azure SQL 受控執行個體 中可偵測的查詢效能瓶頸類型
• 效能監視及微調工具
• SQL Server 中的自動調整選項
• 索引架構和設計指導方針
• 針對查詢逾時錯誤進行疑難排解
• 疑難排解 SQL Server 中的高 CPU 使用率問題
• 從 SQL Server 2012 或更早版本升級至 2014 或更新版本之後，查詢效能降低