Identifying the Complete Session Blocking Chain in SQL Server
Introduction
T-SQL Overview
SQL Query
T-SQL description
Conclusion
Introduction
In the world of SQL Server, dealing with blocking sessions can be a common challenge that affects database performance.
To gain insights into this issue, let's explore a SQL query that displays a tree-like structure of the blocking chain, helping to identify
the root cause of blocking and its impact on database operations.
T-SQL Overview
This SQL query consists of multiple parts:
- Temporary Table Creation: The script creates a temporary table
#blocks to store information about
blocking sessions, including the session ID (spid), the session ID that
is being blocked (blocked), and the SQL batch being executed (batch).
- Recursive Common Table Expression (CTE): The recursive CTE blocking_tree constructs a tree-like structure of the blocking chain by
recursively joining rows from the
#blocks table. This CTE identifies the blocking sessions and their relationships,
organizing them into a hierarchical structure.
- Final Query: The final query retrieves information about blocking sessions and associated details such as
session type, login name,
database, SQL text, wait resource, and more. It also formats the output to display the blocking sessions in a tree-like structure, making
it easier to visualize the blocking chain.
T-SQL Query
You can use different conditions in the ORDER BY clause to achieve the desired result.
if object_id('tempdb..#blocks') is not null
drop table #blocks
select
spid,
blocked,
replace (replace (st.text, char(10), ' '), char (13), ' ' ) as batch
into
#blocks
from
sys.sysprocesses spr
cross apply
sys.dm_exec_sql_text(spr.sql_handle) st
go
with blocking_tree (spid, blocking_spid, [level], batch)
as
(
select
blc.spid,
blc.blocked,
cast (replicate ('0', 4-len (cast (blc.spid as varchar))) + cast (blc.spid as varchar) as varchar (1000)) as [level],
blc.batch
from
#blocks blc
where
(blc.blocked = 0 or blc.blocked = spid)
and
exists (select * from #blocks blc2 where blc2.blocked = blc.spid and blc2.blocked <> blc2.spid)
union all
select
blc.spid,
blc.blocked,
cast(bt.[level] + right (cast ((1000 + blc.spid) as varchar (100)), 4) as varchar (1000)) as [level],
blc.batch
from
#blocks as blc
inner join
blocking_tree bt
on blc.blocked = bt.spid
where
blc.blocked > 0
and
blc.blocked <> blc.spid
)
select
N'' + isnull(replicate (N'| ', len (level)/4 - 2),'') + case when (len(level)/4 - 1) = 0 then '' else '|------ ' end + cast (bt.spid as nvarchar (10)) as blocking_tree,
spr.lastwaittype AS [type],
spr.loginame AS [login_name],
db_name(spr.dbid) as [source_database],
st.text AS [sql_text],
case when cur.sql_handle is null then '' else (select [text] from sys.dm_exec_sql_text (cur.sql_handle)) end as [cursor_sql_text],
db_name(sli.rsc_dbid) as [database],
object_schema_name(sli.rsc_objid,sli.rsc_dbid) as [schema],
object_name(sli.rsc_objid, sli.rsc_dbid) as [table],
spr.waitresource as [wait_resource],
spr.cmd as [command],
spr.program_name as [application],
spr.hostname as [hostname],
spr.last_batch as [last_batch_time]
from
blocking_tree bt
left outer join
sys.sysprocesses spr
on spr.spid = bt.spid
cross apply
sys.dm_exec_sql_text(spr.sql_handle) st
left join
sys.dm_exec_cursors(0) cur
on cur.session_id = spr.spid
and cur.fetch_status != 0
join
sys.syslockinfo sli
on sli.req_spid = spr.spid
and sli.rsc_type = 5
and object_name(sli.rsc_objid, sli.rsc_dbid) is not null
order by
level asc;
T-SQL description
Now, let's break down each component and understand its significance
Column | Description
------------------- | ---------------------------------------------------------
blocking_tree | Blocking tree structure.
type | A string indicating the name of the last or current wait type.
login_name | Login name.
source_database | Source database.
sql_text | Last SQL statement.
cursor_sql_text | SQL text of the batch that declared the cursor.
database | Database associated to the locked resource.
schema | Schema associated to the locked resource.
table | Table associated to the locked resource.
wait_resource | Textual representation of a lock resource.
command | Command currently being executed.
application | Name of the application program.
hostname | Hostname of the client.
last_batch_time | Last time a client process executed a statement.
Conclusion
In conclusion, by understanding and analyzing the output of this SQL query, database administrators can gain valuable insights into the blocking
sessions occurring in their SQL Server environment. This information is crucial for diagnosing performance issues, identifying bottlenecks,
and optimizing database operations for improved efficiency and reliability.
Related content