How can I retrieve the SQL query that causes one SPID to block another?

Data and Databases
1

Using SQL Server 2019, I need to pinpoint the SQL command that results in one SPID blocking another. How can I capture the active statement?

SELECT session_id, sql_statement
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;
2

ya, try cross apply dm_exec_sql_text with the sql_handle from your dm_exec_requests. this tends to return full query text. works better than classic joins sometimes, esp in complex blocking scenrios.

3

In my experience, when pinpointing the blocking SQL statement I tend to combine session details from multiple DMVs. One effective method is to join sys.dm_exec_requests with sys.dm_os_waiting_tasks to find the blocking session, then use CROSS APPLY on sys.dm_exec_sql_text on the waiting session’s sql_handle. This approach not only retrieves the blocked query for analysis but also helps in understanding the context of the blocking session. It has proven especially helpful when managing and diagnosing performance issues in a production environment.

4

hey forum, i fnd that combining dm_exec_procedure_stats with tracing blocked sql might expose odd triggers. have u ever seen sporadic stored procs spark blockins? lov to hear more abot checks u perform in such cases!

5

hey, you can try extended events. it tracks blocking events in near realtime. i used one capturing spid and statement details which made it easier to spot offending sql in live sessions. could be neat for diagnosing our production issues.