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!
Related content