N2

Meh

Archive for the ‘Uncategorized’ Category

Get blocking processes chain using CTE

without comments

SQL Server 2005 introduced common type expressions. I find it really useful in writing recursive queries. Getting a blocking chain of spids is one way I use CTEs to check for the lead blocker.

 
WITH Blocking_chain( row, depth, dbname, spid, hostname, program, 
		waittime, blocked, sql_handle, stmt_start, stmt_end ) as 
	( 
		Select 
			row_number() over(order by a.spid) [row],
			0 [depth] ,
			db_name(a.dbid) [dbname], 
			a.spid, 
			a.hostname, 
			a.program_name, 
			a.waittime,
			a.blocked,
			a.sql_handle,
			a.stmt_start,
			a.stmt_end
		from 
			master.dbo.sysprocesses a 
			inner join master.dbo.sysprocesses b on b.blocked = a.spid
		where 
			a.blocked = 0 and a.ecid = 0
		union all
		Select 
			b.row, 
			b.depth + 1 , 
			db_name(a.dbid), 
			a.spid, 
			a.hostname, 
			a.program_name, 
			a.waittime,
			a.blocked,
			a.sql_handle,
			a.stmt_start,
			a.stmt_end
		from 
			master.dbo.sysProcesses a 
			inner join Blocking_chain b on a.blocked = b.spid
		where 
			a.blocked > 0 and a.ecid = 0 
	)
 
Select p.*, 
case 
	when p.stmt_start=0 then t.text 
	when p.stmt_end < 0 then substring(t.text, p.stmt_start/2, len(t.text)/2 ) 
	else substring(t.text, p.stmt_start/2, (p.stmt_end-p.stmt_start)/2 ) 
end [statement] 
from 
	Blocking_chain p 
	cross apply sys.dm_exec_sql_text(p.sql_handle) t

Written by Singh

April 10th, 2009 at 9:00 am

Posted in Uncategorized