Data Collector being blocked by user query

• Leo.Miller

  SSChampion

  Points: 13051

  More actions

  February 13, 2018 at 12:09 pm

  #333590

  I've a strange situation, where the Data Collector is being blocked by a long running user query. The blocked query is:
  SELECT 
   @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
  FROM 
   [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 MERGE
  

  Besides 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.

  Leo
  Nothing in life is ever so complex that with a little work it can't be made more complex

  Leo
  Nothing in life is ever so complicated that with a little work it can't be made more complicated.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  February 23, 2018 at 6:43 am

  #1980913

  Leo.Miller - Tuesday, February 13, 2018 12:09 PM

  I've a strange situation, where the Data Collector is being blocked by a long running user query. The blocked query is:
  SELECT 
   @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
  FROM 
   [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 MERGE
  

  Besides 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.

  Leo
  Nothing in life is ever so complex that with a little work it can't be made more complex

  How would you expect Data Collector to gather its information without occasionally running into a long-running query that has a lock.   About the only way you could solve the problem is to either re-code that ugly MERGE to run a lot faster, or to configure Data Collector to not look at certain data.   As I'm not familiar with Data Collector, I don't even know if that's possible, so maybe someone with more knowledge on it can respond.    This is one of those times when reading your own signature is of value...

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)