Common Blocking Scenarios in Azure SQL Database: Causes & Resolutions

Blocking happens when one session (SPID) holds a lock on a resource, preventing another session from accessing it. Unlike deadlocks, where two or more processes are stuck indefinitely, blocking can eventually resolve—but it can still lead to performance bottlenecks.
Common Blocking Scenarios & Their Resolutions

Scenario	Wait Type	Open Transactions	Status	Resolves?
1	NOT NULL	≥ 0	Runnable	✅ Yes, when the query finishes.
2	NULL	>0	Sleeping	❌ No, but SPID can be killed.
3	NULL	≥ 0	Runnable	❌ No, won’t resolve until the client fetches all rows or closes the connection.  Killing SPID may take up to 30 seconds.
4	Varies	≥ 0	Runnable	❌ No, won’t resolve until the client cancels queries or closes connections.  Killing SPIDs may take up to 30 seconds.
5	NULL	>0	Rollback	✅ Yes.
6	NULL	>0	Sleeping	⏳ Eventually. When Windows NT detects inactivity, the connection will break.

How to Identify Blocking in Azure SQL Database
1. Identify Blocked and Blocking Sessions
SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource  
FROM sys.dm_exec_requests  
WHERE blocking_session_id <> 0;
2. Check Open Transactions
SELECT session_id, open_transaction_count, status  
FROM sys.dm_exec_sessions  
WHERE open_transaction_count > 0;
3. Analyze Query Execution Details
SELECT r.session_id, s.host_name, s.program_name, r.command, r.wait_type, r.wait_time  
FROM sys.dm_exec_requests r  
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id  
WHERE r.blocking_session_id <> 0;

How to Resolve Blocking in Azure SQL Database
✅ Scenarios that resolve automatically
    Scenario 1: Query completes, releasing locks.
    Scenario 5: Rollback operation finishes.
    Scenario 6: Windows NT eventually disconnects the session.

❌ Scenarios requiring manual intervention
If blocking does not resolve, consider the following approaches:
1. Kill the Blocking SPID
If a transaction is stuck, you can terminate it:
KILL <session_id>;
Use this cautiously, as it may cause rollbacks.
2. Optimize Long-Running Queries
    Index Optimization: Ensure proper indexing to reduce query execution time.
    Query Tuning: Use QUERY_PLAN to optimize slow queries.
    Batch Processing: Process data in smaller batches to prevent long locks.
3. Handle Open Transactions Properly
    Regularly check sys.dm_tran_active_transactions for long-running transactions.
    Ensure all transactions explicitly COMMIT or ROLLBACK when completed.
4. Improve Connection Management
    Ensure clients properly fetch all rows or close connections.
    Avoid unnecessary long-running transactions that hold locks.