由於阻塞，無法檢索正在執行的查詢的查詢計劃

我正在嘗試對儲存過程中長時間執行的查詢進行故障排除，該儲存過程大約每月一次隨機執行並且永遠不會完成。可能是一些計劃記憶體問題。但問題是我無法檢索查詢計劃，因為查詢執行時 tempdb 阻塞。我正在使用以下 dmv 嘗試檢索查詢計劃

FROM sys.dm_exec_query_stats AS s1   
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2    
cross apply sys.dm_exec_text_query_plan (plan_handle, statement_start_offset, statement_end_offset) s3
where object_name ( s2.objectid, s2.dbid) = 'sp_name'  
ORDER BY statement_start_offset

上述檢索計劃的查詢被長時間執行的問題查詢阻塞，因為上述查詢需要在 tempdb 上的對像上使用模式 S 鎖。他們長時間執行查詢查詢並更新臨時表，並在其上重新編譯選項。

有沒有人遇到過這個問題？

嘗試從

where object_name ( s2.objectid, s2.dbid) = 'sp_name'  

類似於

where object_id = object_id('sp_name')

您可能會得到一個更簡單的計劃來避免 TempDb 中阻止的任何資源。

你可以試試這個查詢：

SELECT 
   blocker.[spid],
   blocker.[Database],
   blocker.[Login],
   blocker.[Hostname],
--  blocker.[HostPID],
--  blocker.[ProgramName],
   blocker.[LastWaitType],
   blocker.[Blocker],
   blocker.[Command] AS [BlockerCommand],
   blocker.[SQL_Text] AS [Blocker_SQL_Text],
   blocker.[QueryPlan] AS [Blocker_Query_Plan],
   spr.[spid],
   RTRIM(spr.[loginame]) AS [Login],
--  DB_NAME(spr.[dbid]) AS [Database],
   RTRIM(spr.[hostname]) AS [Hostname],
--  spr.[hostprocess] AS [HostPID],
--  RTRIM(spr.[program_name]) AS [ProgramName],
   RTRIM(spr.[lastwaittype]) AS [LastWaitType],
   spr.[blocked] AS [Blocker],
   spr.[cmd] AS [BlockedCommand],
   EST.[text] AS [Blocked_SQL_Text],
   blocker.[KillCommand] AS [BlockerKillCommand]
FROM [master].[dbo].[sysprocesses] spr
   JOIN [master].[sys].[dm_exec_connections] dc ON spr.[spid] = dc.[session_id]
   CROSS APPLY sys.dm_exec_sql_text(dc.[most_recent_sql_handle]) AS EST
   RIGHT JOIN (
       SELECT 
           spr.[spid],
           DB_NAME(spr.[dbid]) AS [Database],
           RTRIM(spr.[loginame]) AS [Login],
           RTRIM(spr.[hostname]) AS [Hostname],
--          spr.[hostprocess] AS [HostPID],
--          RTRIM(spr.[program_name]) AS [ProgramName],
           RTRIM(spr.[lastwaittype]) AS [LastWaitType],
           spr.[blocked] AS [Blocker],
           spr.[cmd] AS [Command],
           EST.[text] AS [SQL_Text],
           EQP.[query_plan] AS [QueryPlan],
           'kill ' + CAST(spr.[spid] AS VARCHAR(10)) AS [KillCommand]
       FROM [master].[dbo].[sysprocesses] spr
           JOIN [master].[sys].[dm_exec_connections] dc ON spr.[spid] = dc.[session_id]
           LEFT JOIN [master].[sys].[dm_exec_query_stats] AS EQS ON dc.[most_recent_sql_handle] = EQS.[sql_handle]
           CROSS APPLY sys.dm_exec_sql_text(dc.[most_recent_sql_handle]) AS EST 
           CROSS APPLY sys.dm_exec_query_plan(EQS.[plan_handle]) AS EQP ) AS blocker ON spr.[blocked] = blocker.[spid]
WHERE spr.[blocked] <> 0
--AND blocker.Blocker = 0       -- Only Running Blockers

引用自：https://dba.stackexchange.com/questions/245075