Querying Blocking Chains in Azure SQL with T-SQL
Short answer
Box 1: sys.dm_exec_requests; Box 2: AS r ON r.session_id = s.session_id; Box 3: AS t OUTER APPLY sys.dm_exec_sql_text(r.sql_handle); Box 4: AS ib OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL)
Question
You have an Azure SQL database that supports an OLTP application.
You need to write Transact-SQL code that returns blocking chain details. The output must return only sessions that are blocked or are blocking other sessions.
How should you complete the code? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Key Takeaways
The key insight is recognizing that sys.dm_exec_requests contains both the blocker (blocking_session_id) and the blocked (session_id) information, requiring a self-referencing join or specific DMV usage to map these relationships accurately.
This scenario tests the ability to construct advanced T-SQL queries using dynamic management views (DMVs) to diagnose blocking issues in an OLTP environment. The solution requires correctly joining sys.dm_exec_requests and applying appropriate outer applies for execution context.
Common mistake: Candidates often incorrectly use sys.dm_os_waiting_tasks instead of sys.dm_exec_requests or fail to use OUTER APPLY for sys.dm_exec_sql_text, which prevents retrieving the actual SQL text causing the block.
Expert Analysis
Why the Answer Is Correct
The correct configuration utilizes sys.dm_exec_requests as the primary source for session activity because it directly exposes blocking_session_id and session_id. By joining the requests table to itself (r ON r.session_id = s.session_id), you can link blocked sessions to their blockers. Using OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) retrieves the T-SQL code associated with the request, while OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) captures the input buffer. This combination provides a comprehensive view of blocking chains without excluding sessions that have no active SQL text yet.
Why Other Candidate Answers Are Wrong
Using sys.dm_os_waiting_tasks might provide wait details but lacks the direct session-to-session mapping clarity needed for chain analysis in some contexts compared to requests. Incorrectly using INNER JOINs instead of LEFT/OUTER joins would exclude sessions that are blocked but haven't started executing SQL yet. Failing to use OUTER APPLY for DMFs like sys.dm_exec_sql_text results in null values or errors if the handle is not immediately available, breaking the result set.
Community Comment Notes
As noted by user gangreja, the specific drag-and-drop targets align with standard diagnostic patterns: Box 1 references sys.dm_exec_requests, Box 2 establishes the self-join condition, Box 3 retrieves SQL text via sql_handle, and Box 4 retrieves the input buffer. This confirms the necessity of linking session IDs across the requests CTE and applying metadata functions correctly.
Official Reference
This link opens the official vendor documentation in a new tab.
Exam Strategy
When diagnosing blocking, always start with sys.dm_exec_requests for active sessions and use OUTER APPLY for DMFs to ensure you don't lose rows where metadata might be temporarily unavailable. Familiarize yourself with the columns blocking_session_id and session_id to understand how to construct the self-join logic.
Frequently Asked Questions
Why use sys.dm_exec_requests over sys.dm_os_waiting_tasks?
What is the purpose of OUTER APPLY in this query?
Discussion 1 contributions
Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.
From public community threads 1
-
gangreja 👍 1Box 1 (inside the CTE, AS er) FROM sys.dm_exec_requests Box 2 (AS r ON r.session_id = s.session_id) LEFT OUTER JOIN sys.dm_exec_requests Box 3 (AS t) OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) Box 4 (AS ib) OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL)
Reader comments & corrections
No reader corrections yet — spotted an error or have a note? Share it below.