Archive for the ‘Uncategorized’ Category
Get blocking processes chain using CTE
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 |