Advertisements
Home > Dynamic Management Views/Functions, Performance Tuning > Listing blocking chain by tasks

Listing blocking chain by tasks

WITH TaskChain (waiting_task_address, blocking_task_address, ChainId, LEVEL)
AS
(
   -- Anchor member definition: use self join so that we output
   -- Only tasks that blocking others and remove dupliates

   SELECT   DISTINCT A.waiting_task_address
          , A.blocking_task_address
          , A.waiting_task_address   AS ChainId
          , 0 AS LEVEL
     FROM sys.dm_os_waiting_tasks AS A
     JOIN sys.dm_os_waiting_tasks AS B
       ON A.waiting_task_address = B.blocking_task_address
    WHERE A.blocking_task_address IS NULL

UNION ALL

	-- Recursive member definition: Get to the next level waiting tasks

   SELECT   A.waiting_task_address
	  , A.blocking_task_address
	  , B.ChainId
	  , LEVEL + 1
     FROM sys.dm_os_waiting_tasks AS A
     JOIN TaskChain AS B
       ON B.waiting_task_address = A.blocking_task_address
)
  SELECT   waiting_task_address
         , blocking_task_address
 	 , ChainId
         , LEVEL
    FROM TaskChain
ORDER BY ChainId

References: #20

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: