Querying Blocking Chains in Azure SQL with T-SQL
DRAG DROP
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.
Community Insight
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.
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.
Community Discussion (1 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
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 exposesblocking_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 likesys.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 referencessys.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
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?
sys.dm_exec_requests provides direct access to session IDs and blocking relationships, making it easier to construct a self-referencing join for chain analysis.
What is the purpose of OUTER APPLY in this query?
OUTER APPLY allows you to execute a function (like getting SQL text) for each row in the left table, ensuring blocked sessions are included even if they lack immediate metadata.