針對似乎永遠不會以 SQL Server 結尾的查詢進行疑難解答

發行項
02/07/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 狀態 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 狀態 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	狀態	cpu_time	logical_reads	wait_type
56	運行	7038	101000	NULL
56	可執行	12040	301000	NULL
56	運行	17020	523000	NULL

session_id	狀態	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

執行下列命令，間隔 3 或 4 次，間隔 1 分鐘以檢查查詢計劃和計劃中的實際統計數據。請務必每次儲存查詢計劃，以便進行比較，並建立哪個查詢運算符耗用大部分的 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 來擷取具有數據列計數實際值的即時查詢計劃。此分析基礎結構可讓您在運行時間檢查查詢計劃中的查詢運算符，並了解查詢中大部分的時間花在何處。在 2019 SQL Server 預設會啟用輕量型分析。

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

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

使用類似下列命令來識別 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查詢計劃統計數據。

檢閱收集計劃的方法

本節將說明如何檢閱收集的數據。它會使用在 2016 SP1 和更新版本 SQL Server 中收集的擴充功能 *.sqlplan) ， (使用多個 XML 查詢計劃。

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

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

以下為範例:

解決方案

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

診斷等候或瓶頸

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

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

若要計算大約的等候時間，請從查詢的經過時間減去 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 分析效能數據。

有助於消除或減少等候的參考

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

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