Getting a blocking chain

It’s been a while, primarily because I’ve been looking into a different blogging platform with actual Markdown support, and on a different host. It’s definitely better looking and undoubtedly easier to use from a publishing perspective. I may still end up self-hosting and just buying a domain name. However, I digress …

We’ve been having some issues with blocking on a pre-prod implementation recently. That’s not really a huge cause for alarm since blocking comes with the territory, but this has been excessive and was having some noticeable performance impact. I wanted to put together a query that would help us find the blocking chain (this was before we put the environment in the list of servers monitored by SQLSentry). Recursive CTEs really came in handy. The first iteration looked something like this:

with chains as (
	select 
		dense_rank() over (order by s.session_id) as block_number,
		0 as block_chain_level,
		s.session_id, 	
		s.status,
		s.host_name,
		r.blocking_session_id,			
		coalesce(r.command, 'AWAITING COMMAND') command,
		qp.query_plan, 
		SUBSTRING(st.text,r.statement_start_offset/2,  
            (case when r.statement_end_offset = -1  
            then len(convert(nvarchar(max), st.text)) * 2  
            else r.statement_end_offset end -r.statement_start_offset)/2) as request_query_text,
		st.text,		
		t.task_state, 
		t.context_switches_count, 
		t.pending_io_count, 
		t.scheduler_id, 
		t.exec_context_id, 
		ow.state worker_state, 
		ow.last_wait_type, 
		ow.is_preemptive, 
		ow.is_sick,
		case ow.return_code
			when 0 then 'SUCCESS'
			when 3 then 'DEADLOCK'
			when 4 then 'PREMATURE_WAKEUP'
			when 258 then 'TIMEOUT'
		end as last_wait_return_code, 
		worker_suspended = 
			CASE COALESCE(ow.wait_started_ms_ticks,0)
				WHEN 0 THEN 0
			ELSE 
				si.ms_ticks - ow.wait_started_ms_ticks
        END,
	    w_runnable = 
			CASE COALESCE(ow.wait_resumed_ms_ticks,0)
				WHEN 0 THEN 0
			ELSE 
				si.ms_ticks - ow.wait_resumed_ms_ticks
      END,
	r.wait_type,
	r.wait_time,
	r.wait_resource
	from sys.dm_exec_sessions s
	left join sys.dm_exec_requests r
		on s.session_id = r.session_id
	left join sys.dm_os_tasks t 
		on s.session_id = t.session_id
		and r.request_id = t.request_id 
	left join sys.dm_os_workers AS ow
		ON ow.task_address = r.task_address
	CROSS JOIN sys.dm_os_sys_info AS si
	outer apply sys.dm_exec_query_plan(r.plan_handle) qp
	outer apply sys.dm_exec_sql_text(r.sql_handle) st
	where s.session_id > 50 and coalesce(blocking_session_id,0) = 0
	union all
	select 
		block_number,
		block_chain_level+1 as block_chain_level,
		s.session_id, 	
		s.status,
		s.host_name,
		r.blocking_session_id,			
		coalesce(r.command, 'AWAITING COMMAND') command,
		qp.query_plan, 
		SUBSTRING(st.text,r.statement_start_offset/2,  
            (case when r.statement_end_offset = -1  
            then len(convert(nvarchar(max), st.text)) * 2  
            else r.statement_end_offset end -r.statement_start_offset)/2) as request_query_text,
		st.text,		
		t.task_state, 
		t.context_switches_count, 
		t.pending_io_count, 
		t.scheduler_id, 
		t.exec_context_id,
		ow.state worker_state, 
		ow.last_wait_type, 
		ow.is_preemptive, 
		ow.is_sick,
		case ow.return_code
			when 0 then 'SUCCESS'
			when 3 then 'DEADLOCK'
			when 4 then 'PREMATURE_WAKEUP'
			when 258 then 'TIMEOUT'
		end as last_wait_return_code,
		worker_suspended = 
			CASE COALESCE(ow.wait_started_ms_ticks,0)
				WHEN 0 THEN 0
			ELSE 
				si.ms_ticks - ow.wait_started_ms_ticks
        END,
	    w_runnable = 
			CASE COALESCE(ow.wait_resumed_ms_ticks,0)
				WHEN 0 THEN 0
			ELSE 
				si.ms_ticks - ow.wait_resumed_ms_ticks
      END, 
	r.wait_type, 
	r.wait_time,
	r.wait_resource
	from sys.dm_exec_sessions s
	join sys.dm_exec_requests r
		on s.session_id = r.session_id
	join sys.dm_os_tasks t 
		on s.session_id = t.session_id
		and r.request_id = t.request_id
	INNER JOIN sys.dm_os_workers AS ow
		ON ow.task_address = r.task_address
	CROSS JOIN sys.dm_os_sys_info AS si
	outer apply sys.dm_exec_query_plan(r.plan_handle) qp
	outer apply sys.dm_exec_sql_text(r.sql_handle) st
	join chains c
		on r.blocking_session_id = c.session_id
	where r.blocking_session_id > 0
)
select * 
from chains 
order by block_number, block_chain_level, session_id
option (recompile, maxdop 1);
GO

That was good but I wondered if it was the best. For the information that I wanted, I decided to refactor the code to this:

with chains as (
	select 
		DENSE_RANK() over (ORDER BY r.session_id) as block_number, 
		0 as block_chain_level,
		s.session_id, 
		r.blocking_session_id, 
		r.wait_type,
		r.wait_time,
		r.wait_resource,
		r.statement_start_offset,
		r.statement_end_offset,
		r.plan_handle, 
		r.sql_handle, 
		r.task_address, 
		r.request_id, 
		r.command,
		s.host_name, 
		s.status
	from sys.dm_exec_sessions s  
		left join sys.dm_exec_requests r
			on s.session_id = r.session_id
	where s.session_id > 50 and coalesce(blocking_session_id,0) = 0
	union all
	select 
		DENSE_RANK() over (ORDER BY r.session_id) as block_number, 
		block_chain_level+1 as block_chain_level,
		r.session_id, 
		r.blocking_session_id, 
		r.wait_type,
		r.wait_time,
		r.wait_resource,
		r.statement_start_offset,
		r.statement_end_offset,
		r.plan_handle, 
		r.sql_handle, 
		r.task_address, 
		r.request_id,
		r.command,
		s.host_name, 
		s.status
	from sys.dm_exec_sessions s 
	join sys.dm_exec_requests r
		on s.session_id = r.session_id
	join chains c 
		on r.blocking_session_id = c.session_id
	where r.blocking_session_id > 0
)
--select * from chains
select 
	--block_number,	
	c.session_id, 	
	block_chain_level+1 as block_chain_level,
	c.status,
	c.host_name,
	c.blocking_session_id,			
	coalesce(c.command, 'AWAITING COMMAND') command,
	qp.query_plan, 
	SUBSTRING(st.text,c.statement_start_offset/2,  
           (case when c.statement_end_offset = -1  
           then len(convert(nvarchar(max), st.text)) * 2  
           else c.statement_end_offset end -c.statement_start_offset)/2) as request_query_text,
	st.text,		
	t.task_state, 
	t.context_switches_count, 
	t.pending_io_count, 
	t.scheduler_id, 
	t.exec_context_id,
	ow.state worker_state, 
	ow.last_wait_type, 
	ow.is_preemptive, 
	ow.is_sick,
	case ow.return_code
		when 0 then 'SUCCESS'
		when 3 then 'DEADLOCK'
		when 4 then 'PREMATURE_WAKEUP'
		when 258 then 'TIMEOUT'
	end as last_wait_return_code,
	worker_suspended = 
		CASE COALESCE(ow.wait_started_ms_ticks,0)
			WHEN 0 THEN 0
		ELSE 
			si.ms_ticks - ow.wait_started_ms_ticks
       END,
	   w_runnable = 
		CASE COALESCE(ow.wait_resumed_ms_ticks,0)
			WHEN 0 THEN 0
		ELSE 
			si.ms_ticks - ow.wait_resumed_ms_ticks
     END, 
	c.wait_type, 
	c.wait_time,
	c.wait_resource
from chains c	
	left join sys.dm_os_tasks t 
		on c.session_id = t.session_id
		and c.request_id = t.request_id 
	left join sys.dm_os_workers AS ow
		ON ow.task_address = c.task_address
	CROSS JOIN sys.dm_os_sys_info AS si
	outer apply sys.dm_exec_query_plan(c.plan_handle) qp
	outer apply sys.dm_exec_sql_text(c.sql_handle) st
where c.session_id > 50
order by block_number, block_chain_level, c.session_id
option (recompile, maxdop 1);
GO

I pushed the task, workers, sysinfo, query plan, and sql text collection outside of the recursive CTE. I ran this and pulled the plans in SQL Sentry Plan Explorer. The second query was marginally slower, but was less expensive.

ss.blocking.overview

I’m more comfortable with the way the second query performs so that’s the one that I think I’ll use but hopefully either of these will be useful. Please note that if a query is parallelized, you will have multiple rows for that query. A further level of development for this could be to include “self-blocking” queries (where on `execution_context_id` is waiting for another to complete).

Advertisements

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