How to Detect SQL Server Blocking

Why blocking can suddenly slow an application

SQL Server blocking occurs when a lock held by one session conflicts with a resource another session needs. Brief blocking is a normal part of transaction processing. It becomes an incident when the blocking transaction stays open or a queue of requests forms behind it. The application may time out while server CPU remains low, so infrastructure charts alone can miss the problem.

Common causes include an uncommitted transaction, a large batch update, a data-changing statement that cannot use an appropriate index, or a transaction kept open during user interaction. The first objective is not to terminate a session. It is to identify the head blocker and understand the transaction's business impact safely.

Find the blocking session through DMVs

The read-only query below lists active requests, their wait type, and the blocking session ID. It is for SQL Server. Verify the required DMV permissions and test it outside production first. Rows with a blocking_session_id greater than zero are waiting for another session.

A single snapshot is not enough. Track how long the same relationship persists and whether the number of waiting sessions grows. Duration and queue depth separate an ordinary short lock from a blocking chain that is affecting users.

• Evaluate wait_type together with wait_time.
• Check the open transaction age on the head blocker.
• Estimate rollback impact before terminating a session.

SELECT
    session_id,
    blocking_session_id,
    wait_type,
    wait_time,
    status,
    total_elapsed_time
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
ORDER BY wait_time DESC;

KILL should not be the first reaction

Terminating the blocking session may release waiting requests, but rolling back an open transaction can create additional load and take a long time. If the session belongs to a financial operation or deployment, data consistency and application behavior also need review. Confirm the query owner, transaction start time, affected rows, and recovery plan first.

The durable fix is often to shorten transaction scope, reduce batch size, make resource access order consistent, or help the statement touch fewer rows with an appropriate index. Do not change the isolation level until its consistency and concurrency effects are understood.

Design a blocking alert that avoids noise

Alerting on every lock creates noise. A useful alert combines blocking duration, waiting-session count, and user impact. A blocker that exceeds a meaningful duration for your workload and develops a growing queue deserves attention. The threshold should come from your production baseline, not a universal number copied from the internet.

Include the server and database, blocker session, wait type, duration, waiting-session count, and dashboard link in the notification. The on-call DBA can then start with validation instead of rediscovering the event from scratch.

Moon makes persistent blocking visible

moon is a low-overhead monitoring agent built by DBAs for SQL Server, PostgreSQL, and MongoDB. Continuous observation of important SQL Server signals helps expose persistent blocking behavior that a one-time DMV query can miss. Notifications can be routed through Slack, PagerDuty, or a webhook.

Monitoring does not fix blocking automatically; it provides the right context at the right time. When the problem repeats, comparing duration, queue depth, and workload makes root-cause analysis faster.