Guardian
1 blocking chain active — SPID 84 blocking 3 sessions for 47 seconds · 2 deadlocks in last hour
Active: 38
Sleeping: 64
Blocked: 3
Total Connections
102
of 150 max
Active Sessions
38
executing queries
Blocked Sessions
3
waiting on locks
Deadlocks/hr
2
last 60 minutes
Max Block Time
47s
SPID 84 blocking chain
⚠ Active Blocking Chain
Blocking Chain — 47 seconds · 3 sessions waiting
SPID 84
· AppUser · UPDATE Claims SET StatusCode='PROC' WHERE BatchId=4821 · Open txn 47s · SPID holds IX lock on Claims
↳ blocking:
SPID 91
· AppUser · SELECT * FROM Claims WHERE StatusCode='PEND' · waiting 47s for SH lock
SPID 98
· AppUser · usp_EligibilityCheck · waiting 44s for SH lock
SPID 103
· ReportUser · SELECT COUNT(*) FROM Claims · waiting 39s for SH lock
⚡ Fix: SPID 84 has an open transaction not yet committed. Consider RCSI (Read Committed Snapshot Isolation) to eliminate reader-writer blocking. Also review application connection pooling — transactions should be kept as short as possible.
Deadlock History — Last Hour
Deadlock #1 — 11:24 AM · Victim: SPID 77
SPID 77 (VICTIM)
UPDATE Claims
IX on Claims row A
⇄
SPID 82 (SURVIVOR)
UPDATE ERA
IX on ERA row B
Root cause: Claims↔ERA cross-update without consistent lock order. Fix: always update Claims before ERA in transaction.
Deadlock #2 — 10:52 AM · Victim: SPID 61
SPID 61 (VICTIM)
usp_ProcessClaims
IX on Patients row
⇄
SPID 74 (SURVIVOR)
usp_UpdatePatient
IX on Claims row
Root cause: Inconsistent access order between usp_ProcessClaims and usp_UpdatePatient. Enforce lock order or use UPDLOCK hint.
Active Sessions
| SPID | Status | Login | Database | Host | CPU (ms) | Reads | Duration | Wait Type |
|---|