針對似乎從未在 SQL Server 中結束的查詢進行疑難解答

發行項
07/30/2024

本文說明您有查詢似乎永遠不會完成的問題疑難解答步驟，或讓查詢完成可能需要數小時或數天的時間。

什麼是永不結束的查詢？

本檔著重於繼續執行或編譯的查詢，也就是其 CPU 繼續增加。它不適用於封鎖或等候某些從未釋放的資源的查詢（CPU 會維持不變或變更很少）。

重要

如果查詢保留以完成其執行，它最終就會完成。可能需要幾秒鐘的時間，或者可能需要幾天的時間。

「永不結束」一詞是用來描述當事實上查詢最終完成時未完成查詢的感知。

識別永不結束的查詢

若要識別查詢是否持續執行或停滯在瓶頸上，請遵循下列步驟：

執行下列查詢：

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

檢查範例輸出。
- 當您注意到類似下列輸出的輸出時，本文中的疑難解答步驟特別適用，其中 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 暫止 0 3 8312 LCK_M_U
  
  56 暫止 0 3 13318 LCK_M_U
  
  56 暫止 0 5 18331 LCK_M_U
如需詳細資訊，請參閱診斷等候或瓶頸。

session_id	status	cpu_time	logical_reads	wait_type
56	執行中	7038	101000	NULL
56	可執行的	12040	301000	NULL
56	執行中	17020	523000	NULL

session_id	status	logical_reads	wait_time	wait_type
56	暫止	3	8312	LCK_M_U
56	暫止	3	13318	LCK_M_U
56	暫止	5	18331	LCK_M_U

編譯時間長

在極少數情況下，您可能會發現 CPU 會隨著時間持續增加，但不是由查詢執行所驅動。相反地，它可能是由過多的編譯所驅動（查詢的剖析和編譯）。在這些情況下，請檢查 transaction_name 輸出數據行，並尋找的值 sqlsource_transform。此交易名稱表示編譯。

收集診斷資料

若要使用 SQL Server Management Studio （SSMS）收集診斷數據，請遵循下列步驟：

擷取預估的查詢執行計劃 XML。
檢閱查詢計劃，以查看是否有任何明顯的指示，指出速度緩慢是否來自何處。一般範例包括：
- 數據表或索引掃描（查看估計的數據列）。
- 由大型外部數據表數據集驅動的巢狀迴圈。
- 巢狀迴圈與循環內部有大型分支的巢狀迴圈。
- 數據表多任務緩衝處理。
- 清單中需要很長的時間來處理每個數據列的 SELECT 函式。
如果查詢隨時快速執行，您可以擷取要比較的實際 XML 執行計劃「快速」執行。

這些版本的 SQL Server 引進了輕量型查詢分析基礎結構。它可讓您在執行緩慢查詢期間擷取實際統計數據。這項疑難解答功能可讓您在運行時間檢查查詢計劃中的查詢運算符，並了解查詢中大部分時間都花在何處。

若要使用輕量型查詢執行統計數據分析基礎結構 v1 來識別查詢中的緩慢步驟，請遵循下列步驟：

執行下列命令以啟用 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

從應用程式啟動受影響的永不結束查詢。

每分鐘左右執行下列命令多次，以檢查查詢計劃運算符的運行時間執行統計數據：

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)

擷取三到四個快照集相隔一分鐘，讓您有足夠的數據進行分析。具體來說，您可以比較 row_count 一段時間后每個運算子的數位，並查看其顯示數據列計數顯著增加（百萬或更多）。
在 SSMS 的新查詢視窗中，執行下列命令來擷取問題查詢的估計查詢計劃：
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
使用節點標識元搭配步驟 3 中查詢所識別的最高數據列計數，在估計查詢計劃中尋找相同的節點。此步驟將協助了解計劃中哪一個運算子是長時間運行時間的主要原因。

執行下列命令以停止 XEvent：

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP

您可以使用輕量型查詢執行統計數據分析基礎結構 v2 ，擷取具有數據列計數實際值的實時查詢計劃。此分析基礎結構可讓您在運行時間檢查查詢計劃中查詢運算符，並了解查詢中大部分時間都花在何處。

若要識別查詢中的緩慢步驟，請遵循下列步驟：

若要在這些版本的 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

從應用程式啟動受影響的永不結束查詢。

使用類似下列命令來識別 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

執行下列命令三到四次，將一分鐘隔開一分鐘，以檢查計劃的查詢計劃和實際統計數據。請務必每次儲存查詢計劃，以便比較查詢計劃，並建立哪一個查詢運算元耗用大部分的CPU時間。具體來說，您可以比較一段時間內每個運算子的數據列計數（實際數據列數目），並查看哪些運算符顯示數據列計數顯著增加（百萬或更多）。將取代 <session_id> 為您在上一個步驟 3 中找到的整數值。
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```

如果您啟動 XEvent，請停止 XEvent，或停用追蹤旗標：

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

您可以使用輕量型查詢執行統計數據分析基礎結構 v3 ，擷取具有數據列計數實際值的實時查詢計劃。此分析基礎結構可讓您在運行時間檢查查詢計劃中查詢運算符，並了解查詢中大部分時間都花在何處。 SQL Server 2019 預設會啟用輕量型分析。

若要識別查詢中的緩慢步驟，請遵循下列步驟：

從您的應用程式啟動受影響的永不結束查詢。

使用類似下列命令來識別 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

執行下列命令三或四次，以檢查計劃的查詢計劃和實際統計數據。請務必每次儲存查詢計劃，以便比較查詢計劃，並建立哪一個查詢運算元耗用大部分的CPU時間。將取代 <session_id> 為您在上一個步驟 3 中找到的整數值。
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
具體而言，選取 [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）。

請遵循下列步驟來比較執行計劃：

開啟先前儲存的查詢執行計劃檔案（.sqlplan）。
以滑鼠右鍵按兩下執行計畫的空白區域，然後選取 [ 比較執行程式表]。
選擇您想要比較的第二個查詢計劃檔案。
尋找粗箭號，指出在運算符之間流動大量的數據列。然後選取箭號前後的運算符，並比較兩個計劃的實際數據列數目。
比較第二個和第三個計劃，以查看數據列的最大流程是否發生在相同的運算符中。

以下是範例：

解決方法

請確定已更新查詢中使用的數據表統計數據。
在查詢計劃中尋找遺漏的索引建議，並套用任何索引。
以簡化查詢的目標重寫查詢：
- 使用更選擇性 WHERE 的述詞來減少預先處理的數據。
- 將其分開。
- 在臨時表中選取一些元件，稍後再聯結這些元件。
- 由於優化器數據列目標，在TOP長時間執行的查詢中移除、 EXISTS和 FAST （T-SQL）。或者，您可以使用DISABLE_OPTIMIZER_ROWGOAL提示。如需詳細資訊，請參閱數據列目標消失的流氓。
- 避免在將語句結合成單一大型查詢時，在這類情況下使用通用數據表運算式（CTE）。
請嘗試使用查詢提示來產生更好的計劃：
- HASH JOIN 或 MERGE JOIN 提示
- FORCE ORDER 提示
- FORCESEEK 提示
- RECOMPILE
- 如果您有可以強制的快速查詢計劃，請使用PLAN N'<xml_plan>'
如果這類計劃存在，且您的 SQL Server 版本支援查詢存放區，請使用查詢存放區（QDS）強制良好的已知計劃。

診斷等候或瓶頸

如果問題不是長時間執行的CPU驅動查詢，本節會在此包含為參考。您可以使用它來針對因等候而長時間的查詢進行疑難解答。

若要優化等候瓶頸的查詢，請找出等候的時間長度，以及瓶頸所在的位置（等候類型）。確認等候類型之後，請減少等候時間，或完全排除等候。

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

如何計算近似等候持續時間的範例：

經過的時間（毫秒）	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 分析效能數據。

協助消除或減少等候的參考

每個等候類型的原因和解決方式會有所不同。沒有一個一般方法可以解析所有等候類型。以下是疑難解答和解決常見等候類型問題的文章：

如需許多 Wait 類型及其指示的描述，請參閱 Waits 類型中的表格。

共用方式為