I've a strange situation, where the Data Collector is being blocked by a long running user query. The blocked query is:
@plan_handle AS plan_handle,
@statement_start_offset AS statement_start_offset,
@statement_end_offset AS statement_end_offset,
[dbid] AS database_id,
[objectid] AS object_id,
OBJECT_NAME(objectid, dbid) AS object_name,
[query_plan] AS query_plan
[sys].[dm_exec_text_query_plan](@plan_handle, @statement_start_offset, @statement_end_offset) dm
And the blocking query is a pretty ugly MERGE statement with 288 lines of conditions, tests and actions.
When I look at the blocked object I find the Data Collector is trying to get a Sch-S on a Temporary table (#TableName) in TempDB, that the MERGE command has a Sch-M lock - GRANT lock on. SPID 73 is the data Collector, SPID 120 is the user MERGEBesides the obvious, which is to improve the code or the way things are done, is there any way to stop the Data Collecting being blocked? I would have expected the design to be such that this can't happen.
Nothing in life is ever so complex that with a little work it can't be made more complex