Blocked requests (SQL Server)

SQL ServerRead onlyScripts Gallery

During an incident: the requests waiting on another session, with what they wait for and their query.

Verification status. Written from the BroadSQL documentation and the SQL Server documentation. It has not been run by the BroadSQL team: run it first on a test database, and read its result critically.

When to use it

An application hangs on a lock.
Find the blocking session before anyone ends it.

The Script

Download mssql-blocked-requests.sql

-- @description: Requests blocked by another session, with their wait and query text (SQL Server)
-- @tags: sqlserver, diagnostics, locks, incident
-- @status: draft
-- @access: read
-- @dialect: SQL Server
SELECT r.session_id,
       r.blocking_session_id,
       r.wait_type,
       r.wait_time / 1000 AS wait_seconds,
       s.login_name,
       DB_NAME(r.database_id) AS database_name,
       SUBSTRING(t.text, 1, 200) AS query_text
  FROM sys.dm_exec_requests r
  JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
 WHERE r.blocking_session_id > 0
 ORDER BY r.wait_time DESC;

Parameters

This Script takes no parameter.

Install and run it

Save the file in your Scripts Library folder (scripts in the installation folder by default), for example as gallery/mssql-blocked-requests.sql.
Connect to the SQL Server instance with VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE from SQL Server 2022); without it, you see your own session only.
Check what it declares: LIB SHOW gallery/mssql-blocked-requests.sql;
Run it: @gallery/mssql-blocked-requests.sql

What you get

One row per blocked request: its session, the blocking session, the wait type and duration, the login, the database and the start of its query text.

Limits

It reads; it never ends a session.
Negative blocking_session_id values, which Microsoft documents as special cases, are left out.
Written from the documentation of sys.dm_exec_requests.

Found something wrong or unclear on this page? Tell us, with the page address.