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.

Rate Your Experience

: 89 : 0