How to identify and resolve blocking queries in SQL Server?

During peak load, our database server experiences severe performance degradation due to query blocking. Transactions stall, causing API timeouts across our microservices. What scripts can I run to identify the lead blocker and resolve lock contention quickly?

Finding Active Blockers

You can query dynamic management views (DMVs) to isolate blocking sessions immediately.

-- Select blocked and blocking session details
SELECT 
    r.session_id AS blocked_session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    t.text AS sql_text
FROM sys.dm_exec_requests r
-- Cross apply to retrieve the exact T-SQL query text
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

Resolution Strategies

• Add missing indexes to shorten transaction lock duration
• Use read committed snapshot isolation (RCSI) to eliminate reader-writer blocking
• Keep transactions short and avoid user interaction within explicit transactions