VA Blocking Chain Auditor
HEALTHY (< 5s): Locks are resolving quickly. Standard transactional concurrency is maintaining throughput.
WARNING (6-29s): Contention detected. Multiple sessions are queuing for the same data resources.
CRITICAL (30s+): High crash risk. A Lead Blocker is preventing server-wide data modifications.
Surgical Action Plan: Restore Concurrency
- The Lead Blocker: Identify the red-highlighted row. This SPID is the "Head of the Snake" holding everyone else up.
- Fix: Contact the user to
COMMIT;orROLLBACK;their transaction. In emergencies, useKILL [Spid];. - Preventative: Check Query Performance for queries with high reads that may be holding locks longer than necessary.
Blocked Sessions
1
Lead Blocker
SPID 56
Audit Status
Live
| Spid | Blocked By | Who to Call | Database / Object | Duration | Sql Text |
|---|---|---|---|---|---|
| 53 | 56 | DESKTOP-LQEABPI\penny DESKTOP-LQEABPI |
AWL Ad-Hoc / Idle |
16723s | -- 1. Ensure a database has Read Committed Snapshot enabled (Safe for local TEST)
ALTER DATABASE AWL SET READ_COMMITTED_SNAPSHOT ON;
|
| 56 LEAD | -- | DESKTOP-LQEABPI\penny DESKTOP-LQEABPI |
AWL Ad-Hoc / Idle |
0s | SELECT session_id, user_objects_alloc_page_count
FROM tempdb.sys.dm_db_session_space_usage
WHERE session_id > 50; -- Filters for your user sessions |
Reference: Raw Truth Verification Query
SELECT session_id, blocking_session_id, wait_time, wait_type, last_wait_type FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 OR session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0);