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?