Unable to retrieve query plan of running query because of blocking 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?
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.