Blocking and Blocked Processes - SQL Server Query Performance Tuning - page 420

In a blocking scenario, you need the following information to have a clear understanding of the cause of the
blocking:
• The connection information of the blocking and blocked sessions: You can obtain this
information from the sys.dm_os_waiting_tasks dynamic management view or the sp_who2
system stored procedure.
• The lock information of the blocking and blocked sessions: You can obtain this information
from the sys.dm_tran_locks DMO.
• The SQL statements last executed by the blocking and blocked sessions: You can use the
sys.dm_exec_requests DMV combined with sys.dm_exec_sql_text and sys.dm_exec_queryplan
or Extended Events to obtain this information.
You can also obtain the following information from SQL Server Management Studio by running the Activity
Monitor. The Processes page provides connection information of all SPIDs. This shows blocked SPIDS, the process
blocking them, and the head of any blocking chain with details on how long the process has been running, its SPID,
and other information. It is possible to put Extended Events to work using the blocking report to gather a lot
of the same information. For immediate checks on locking, use the DMOs; for extended monitoring and historical
tracking, you'll want to use Extended Events. You can find more on this in the “Extended Events and the blocked_
process_report Event” section.
To provide more power and flexibility to the process of collecting blocking information, a SQL Server
administrator can use SQL scripts to provide the relevant information listed here.
Capturing Blocking Information with SQL
To arrive at enough information about blocked and blocking processes, you can bring several dynamic management
views into play. This query will show information necessary to identify blocked processes based on those that are
waiting. You can easily add filtering to access only those processes blocked for a certain period of time or only within
certain databases, among other options.
SELECT dtl.request_session_id AS WaitingSessionID,
der.blocking_session_id AS BlockingSessionID,
dowt.resource_description,
der.wait_type,
dowt.wait_duration_ms,
DB_NAME(dtl.resource_database_id) AS DatabaseName,
dtl.resource_associated_entity_id AS WaitingAssociatedEntity,
dtl.resource_type AS WaitingResourceType,
dtl.request_type AS WaitingRequestType,
dest.[text] AS WaitingTSql,
dtlbl.request_type BlockingRequestType,
destbl.[text] AS BlockingTsql
FROM sys.dm_tran_locks AS dtl
JOIN sys.dm_os_waiting_tasks AS dowt
ON dtl.lock_owner_address = dowt.resource_address
JOIN sys.dm_exec_requests AS der
ON der.session_id = dtl.request_session_id
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
LEFT JOIN sys.dm_exec_requests derbl
ON derbl.session_id = dowt.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(derbl.sql_handle) AS destbl
LEFT JOIN sys.dm_tran_locks AS dtlbl
ON derbl.session_id = dtlbl.request_session_id;