DP-800: Developing AI-Enabled Database Solutions

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 that are blocking other sessions.

Complete the code. Each value may be used once, more than once, or not at all.

Drag & Drop

WITH cteBL (session_id, blocking_these) AS
(
    SELECT
        s.session_id,
        blocking_these = x.blocking_these
    FROM sys.dm_exec_sessions AS s
    CROSS APPLY
    (
        SELECT
            ISNULL(CONVERT(varchar(6), er.session_id), '') + ',' + ' '
        FROM sys.dm_exec_requests AS er
        WHERE er.blocking_session_id = ISNULL(s.session_id, 0)
            AND er.blocking_session_id <> 0
        FOR XML PATH('')
    ) AS x(blocking_these)
)
SELECT
    s.session_id,
    blocked_by = r.blocking_session_id,
    bl.blocking_these,
    batch_text = t.text,
    input_buffer = ib.event_info
FROM sys.dm_exec_sessions AS s
 AS r ON r.session_id = s.session_id
INNER JOIN cteBL AS bl ON s.session_id = bl.session_id
 AS t
 AS ib
WHERE bl.blocking_these IS NOT NULL
    OR r.blocking_session_id > 0
ORDER BY LEN(bl.blocking_these) DESC, r.blocking_session_id DESC, r.session_id;

Explanation

LEFT OUTER JOIN retains sessions that block other sessions even if they have no current request row. OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) retrieves the batch text without removing rows with no SQL handle. sys.dm_exec_input_buffer(s.session_id, NULL) retrieves the input buffer for the session's current request and retains sessions that do not have a request row.

DP-800: Developing AI-Enabled Database Solutions

AstroTutor

Notes

Readiness

QuestionQ9

Community Discussion