Sql-server – Unable to retrieve query plan of running query because of blocking

performancequery-performancesql serversql server 2014

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?

Best Answer

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.

Related Question