Question

Users report that an Azure SQL Database is intermittently unresponsive during business hours. You suspect blocking. You need to identify the root blocking session (the head of the blocking chain) and the query it is running, using the least effort. Which approach should you use?

Accepted Answer

Query sys.dm_exec_requests joined to sys.dm_exec_sessions, filtering where blocking_session_id is not 0, and trace back to the session whose own blocking_session_id is 0.

Answer

Enable Query Store in READ_WRITE mode and review the Top Resource Consuming Queries report.

Answer

Run DBCC CHECKDB with the PHYSICAL_ONLY option during the blocking window.

Answer

Configure Resource Governor to place blocked sessions into a lower-priority workload group.