1

I am trying to troubleshoot a long running query in a stored procedure that around once every month randomly runs for ever and never finishes. Probably some plan caching issue. But the problem is that I am unable to retrieve the query plan because of blocking in tempdb while the query is running. I am using the below dmv's to try to retrieve the query plan

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

The above query to retrieve the plan gets blocked by the long running problem query because the above query needs a schema S lock on an object on tempdb. They long running query queries and updates a temp table and has option recompile on it.

Has anyone come across this issue?

  • Could you add a reproducable example? When I create a procdure that inserts, updates and selects from a temporary table, all with OPTION(RECOMPILE) I can get the query plan using above query. – Randi Vertongen Aug 11 at 11:07
  • I can try, but its a reporting query that joins a couple of big tables and runs for 10 mins normally. Did you try to retrieve the query plan while the query is running? – jesijesi Aug 12 at 5:33
  • If possible, I wouldn't rely on plan from the cache to troubleshoot performances, since they're cached pre-execution. If not possible, I guess you could try and setup an extended events session to capture the compiled plan but be aware this is an expensive option.(either sqlserver.query_post_execution_showplan for actual query plans, or sqlserver.query_post_compilation_showplan for cached ones) – GabSql Aug 12 at 13:19
0

Try flipping this predicate from

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

to something like

where object_id = object_id('sp_name')

You might get a simpler plan that aviods whatever resource is blocked in TempDb.

-2

could you try this query:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.