Identifying Blocking Sessions in SQL Server

Introduction
Identifying locking sessions with T-SQL
T-SQL description
Blocking Sessions Example
Conclusion

Introduction

If you've ever experienced a situation where your SQL Server database seemed slow or unresponsive, it might be due to locking and blocking sessions. These sessions occur when one query locks a resource while another query is waiting for it, causing delays and potentially affecting your database performance. In this blog, we'll explore a SQL query that helps identify locking and blocking sessions in your SQL Server database.

Identifying locking sessions with T-SQL

This SQL query retrieves information from the dynamic management views, providing the locking and blocking sessions for a given database. Will list the query doing the locking and the query being blocked.

select
    dtl.resource_type,
    db_name(dtl.resource_database_id) as database_name,
    case
    when dtl.resource_type in ('DATABASE', 'FILE', 'METADATA') then
        dtl.resource_type
    when dtl.resource_type = 'OBJECT' then
        object_name(dtl.resource_associated_entity_id)
    when dtl.resource_type in ('KEY', 'PAGE', 'RID') then
        (select object_name(object_id) from sys.partitions where sys.partitions.hobt_id = dtl.resource_associated_entity_id)
    else
        'Unidentified'
    end as requested_object_name,
    dtl.resource_associated_entity_id,
    dtl.request_mode,
    dtl.request_status,
    dowt.wait_duration_ms,
    dowt.wait_type,
    dowt.session_id as blocked_session_id,
    des_blocked.login_name as blocked_user,
    dest_blocked.text as blocked_command,
    dowt.blocking_session_id,
    des_blocking.login_name as blocking_user,
    dest_blocking.text as blocking_command,
    dowt.resource_description
from
    sys.dm_tran_locks dtl
    inner join
    sys.dm_os_waiting_tasks dowt
        on dtl.request_session_id = dowt.blocking_session_id
    inner join
    sys.dm_exec_requests der
        on dowt.session_id = der.session_id
    inner join
    sys.dm_exec_sessions des_blocked
        on dowt.session_id = des_blocked.session_id
    inner join
    sys.dm_exec_sessions des_blocking
        on dowt.blocking_session_id = des_blocking.session_id
    inner join
    sys.dm_exec_connections dec
        on dtl.request_session_id = dec.most_recent_session_id
    cross apply
    sys.dm_exec_sql_text(dec.most_recent_sql_handle) as dest_blocking
    cross apply
    sys.dm_exec_sql_text(der.sql_handle) as dest_blocked
where
    db_name(dtl.resource_database_id) = 'dbdocs' --Replace this with your DBNAME
and
    dtl.resource_associated_entity_id > 0
and
    dtl.resource_type <> 'DATABASE';

T-SQL description

Now, let's break down each component and understand its significance

Column                | Description
--------------------- | -------------------------------------------------------
resource_type         | The resource type being locked, File, Object, Page, Key, RID.
database_name         | The database name where the lock is being held.
requested_object_name | The object name of the resource being locked.
request_mode          | Request mode for the granted or requested lock.
request_status        | Status of the request. Granted, Convert, or Wait.
wait_duration_ms      | Total wait time for thsi wait type.
wait_type             | Name of the wait type.
blocked_session_id    | ID of the session that is being blocked.
blocked_user          | Login of user being blocked.
blocked_command       | Query of the user being blocked.
blocking_session_id   | ID of the session that is blocking the request. If NULL, not blocked.
blocking_user         | Login of the user blocking the request.
blocking_command      | Query of the user blocking the request.
resource_description  | Description of the resource being consumed.

This SQL query retrieves information about locking and blocking sessions in your SQL Server database. Here's what each part of the query does:

• It identifies the type of resource being locked.
• It specifies the database name where the lock is occurring.
• It determines the object (table, index, etc.) involved in the locking process.
• It provides details about the lock mode, status, and wait duration.
• It lists the session ID of the blocked session and the corresponding user.
• It displays the command being executed by the blocked session.
• It identifies the session ID of the blocking session and the corresponding user.
• It shows the command being executed by the blocking session.
• It describes the resource being locked.

Blocking Sessions Example

--Create a table
CREATE TABLE dbdocs_Users (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50)
);

--Insert few rows into the table
INSERT INTO dbdocs_Users (ID, Name) VALUES (1, 'John');
INSERT INTO dbdocs_Users (ID, Name) VALUES (2, 'Smith');
INSERT INTO dbdocs_Users (ID, Name) VALUES (3, 'David');
INSERT INTO dbdocs_Users (ID, Name) VALUES (4, 'Thomas');
INSERT INTO dbdocs_Users (ID, Name) VALUES (5, 'Joe');
INSERT INTO dbdocs_Users (ID, Name) VALUES (6, 'Mark');
INSERT INTO dbdocs_Users (ID, Name) VALUES (7, 'Brad');
INSERT INTO dbdocs_Users (ID, Name) VALUES (8, 'Trump');
INSERT INTO dbdocs_Users (ID, Name) VALUES (9, 'Claire');
INSERT INTO dbdocs_Users (ID, Name) VALUES (10, 'Nick');

--In frist session 
BEGIN TRANSACTION;
UPDATE dbdocs_Users SET Name = 'Ken' WHERE ID = 2;

--In second session
BEGIN TRANSACTION;
UPDATE dbdocs_Users SET Name = 'Hudson' WHERE ID = 2;

Now run the SQL T-SQL to identify locking and blocking sessions

Conclusion

By running this SQL query, you can gain valuable insights into locking and blocking sessions in your SQL Server database. This information can help you diagnose performance issues, optimize queries, and ensure smooth operation of your database. Don't let locking and blocking sessions slow down your database – take control with this simple SQL query!

Rate Your Experience

: 89 : 0