針對在 SQL Server 中似乎無休止地執行的查詢進行疑難排解

本文提供針對 Microsoft SQL Server 查詢花費過多時間才能結束（小時或數天）的問題進行疑難排解指引。

癥狀

本文著重於似乎無休止地運行或編譯的查詢。也就是說，它們的 CPU 使用率不斷增加。本文不適用於遭到封鎖或等候從未發行之資源的查詢。在這些情況下，CPU 使用率會保持不變或僅略有變化。

重要

如果查詢繼續執行，它最終可能會完成。此過程可能只需要幾秒鐘或幾天的時間。在某些情況下，查詢可能確實是無窮無盡的，例如當 WHILE 迴圈未結束時。這裡使用「永無止境」一詞來描述未完成的查詢的感知。

原因

長時間執行（永無止境）查詢的常見原因包括：

巢狀迴圈（NL）聯結在非常大的表格上： 由於 NL 聯結的本質，聯結具有許多資料列的資料表的查詢可能會執行很長時間。如需詳細資訊，請參閱聯結。
- NL 聯結的一個範例是使用 TOP、 FAST或 EXISTS。即使雜湊或合併聯結可能更快，最佳化工具也無法使用任一運算子，因為資料列目標。
- NL 聯結的另一個範例是在查詢中使用不等式聯結述詞。例如： SELECT .. FROM tab1 AS a JOIN tab 2 AS b ON a.id > b.id 。優化器也無法在此處使用合併或哈希聯結。
過時的統計資料： 根據過時統計資料挑選計劃的查詢可能不是最佳狀態，而且需要很長時間才能執行。
無限循環： 使用 WHILE 迴圈的 T-SQL 查詢可能會寫入不正確。生成的代碼永遠不會離開循環並無休止地運行。這些查詢確實是永無止境的。他們一直奔跑，直到被手動殺死。
具有許多聯結和大型資料表的複雜查詢： 涉及許多聯結資料表的查詢通常會有複雜的查詢計劃，可能需要很長時間才能執行。此案例在分析查詢中很常見，這些查詢不會篩選出資料列，且涉及大量資料表。
缺少索引： 如果在資料表上使用適當的索引，查詢的執行速度會大幅加快。索引可讓您選取資料的子集，以提供更快的存取。

Solution

步驟 1：發現永無止境的查詢

尋找系統上執行的永無止境的查詢。您必須判斷查詢的執行時間是否長、等待時間長（停在瓶頸上）或編譯時間長。

1.1 執行診斷

在永不結束查詢作用中的 SQL Server 執行個體上執行下列診斷查詢：

DECLARE @cntr INT = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    CAST(r.cpu_time / (1000 * 60.0) AS DECIMAL(10,2)) AS cpu_time_minutes,
                    CAST(r.total_elapsed_time / (1000 * 60.0) AS DECIMAL(10,2)) AS elapsed_minutes,
                    r.logical_reads,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    r.reads,
                    r.writes,
                    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

1.2 檢查輸出

有數種情況可能會導致查詢長時間執行：長時間執行、長時間等待和長時間編譯。如需查詢執行緩慢原因的詳細資訊，請參閱執行中與等待：為什麼查詢速度緩慢？

執行時間長

當您收到類似下列的輸出時，本文中的疑難排解步驟適用，其中 CPU 時間與經歷的時間成比例增加，而沒有顯著的等待時間。

session_id	狀態	cpu_time_minutes	elapsed_time_minutes	logical_reads	wait_time_minutes	wait_type
56	執行中	64.40	23.50	0	0.00	NULL

如果查詢具有下列條件，則查詢會持續執行：

CPU 時間增加
狀態為 running 或 runnable
等待時間最短或為零
沒有wait_type

在此情況下，查詢正在讀取列、聯結、處理結果、計算或格式化。這些活動都是 CPU 繫結動作。

注意

在此情況下，中的 logical_reads 變更並不相關，因為某些 CPU 系結的 T-SQL 要求，例如執行計算或 WHILE 迴圈，可能根本不執行任何邏輯讀取。

如果慢速查詢符合這些條件，請專注於減少其運行時間。一般而言，減少執行時間牽涉到透過套用索引、重寫查詢或更新統計資料，減少查詢在其整個生命週期中必須處理的資料列數。如需詳細資訊，請參閱「解決方法」一節。

等待時間長

本文不適用於長時間等待的案例。在等待案例中，您可能會收到類似下列範例的輸出，其中 CPU 使用量不會變更或略有變更，因為工作階段正在等待資源：

session_id	狀態	cpu_time_minutes	elapsed_time_minutes	logical_reads	wait_time_minutes	wait_type
56	暫止	0.03	4.20	50	4.10	LCK_M_U

等待類型指出階段作業正在等待資源。經過時間長及等待時間長表示階段作業正在等待此資源的大部分生命週期。 CPU 時間短表示實際處理查詢所花費的時間很少。

若要針對因等候而長時間的查詢進行疑難排解，請參閱針對 SQL Server 中執行緩慢的查詢進行疑難排解。

編譯時間長

在極少數情況下，您可能會觀察到 CPU 使用量會隨著時間持續增加，但並非由查詢執行驅動。相反地，過長的編譯（查詢的剖解析和編譯）可能是原因。在這些情況下，請檢查輸出直欄是否有 transaction_name 的值 sqlsource_transform。此交易名稱表示編譯。

步驟 2：手動收集診斷記錄

判斷系統上存在永無止境的查詢之後，您可以收集查詢的計劃資料以進一步進行疑難排解。若要收集資料，請根據您的 SQL Server 版本，使用下列其中一種方法。

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

擷取預估的查詢執行計劃 XML。
檢閱查詢計劃，以瞭解資料是否顯示造成速度緩慢的明顯指示。典型適應症的例子包括：
- 表格或索引掃描（查看估計資料列）
- 由龐大的外表資料集驅動的巢狀迴圈
- 在迴圈內側有一個大分支的巢狀迴圈
- 桌軸
- 清單中 SELECT 處理每一列需要很長時間的函數
如果查詢在任何時候執行得更快，您可以擷取「快速」執行（實際的 XML 執行計劃）以比較結果。

輕量型查詢分析基礎結構是在 Microsoft SQL Server 2014 中引進的。它可讓您在執行緩慢查詢時擷取實際統計資料。此疑難排解功能可讓您在執行階段檢查查詢計劃中的查詢運算子，並瞭解查詢中大部分時間所花費的位置。

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

執行下列命令以啟用 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 來擷取包含實際資料列計數的即時查詢計劃。此分析基礎結構可讓您在執行階段檢查查詢計劃中的查詢運算子，並瞭解查詢中大部分時間所花費的位置。

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

若要啟用輕量型基礎結構，請使用下列其中一種方法：

執行下列命令來啟用追蹤旗標 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> 為您在上一個步驟中找到的整數值。
```
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 來擷取包含實際資料列計數的即時查詢計劃。此分析基礎結構可讓您在執行階段檢查查詢計劃中的查詢運算子，並瞭解查詢中大部分時間所花費的位置。 Microsoft 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> 為您在上一個步驟中找到的整數值。
```
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 LogScout 擷取永無止境的查詢

您可以使用 SQL LogScout 在執行永無止境的查詢時擷取記錄。搭配下列命令使用永不結束的查詢案例：

.\SQL_LogScout.ps1 -Scenario "NeverEndingQuery" -ServerName "SQLInstance"

注意

此日誌擷取程序需要長查詢耗用至少 60 秒的 CPU 時間。

SQL LogScout 會針對每個高 CPU 耗用量查詢擷取至少三個查詢計劃。您可以找到類似 servername_datetime_NeverEnding_statistics_QueryPlansXml_Startup_sessionId_#.sqlplan的檔案名稱。當您檢閱計劃時，您可以在下一個步驟中使用這些檔案，以識別長時間查詢執行的原因。

步驟 3：檢閱已收集的計劃

本節討論如何檢閱收集的資料。它會使用 Microsoft SQL Server 2016 SP1 和更新版本組建和版本中收集的多個 XML 查詢計劃（使用延伸模組 .sqlplan）。

請依照下列步驟比較執行計劃：

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

例如：

第 4 步：解決方案

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

針對顯示兩部伺服器之間顯著效能差異的查詢進行疑難排解

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-08-19