Blocking chains and long-running transactions
How open transactions, chatty apps, and missing indexes combine into blocking storms — and what a health review prioritises first.
Blocking is a symptom. The cause might be a missing index, a report in a write transaction, an ORM chatty pattern, or a human who left a transaction open in a tool.
What we look for
- Frequent head blockers by query fingerprint
- Long-running open transactions
- Escalation patterns
- NOLOCK cargo-cult “fixes” that hide write anomalies
- Overnight batch jobs overlapping online users
Short-term vs structural fixes
Short-term: identify the blocker, reduce impact, unblock the business.
Structural: fix indexing, transaction scope, isolation choices, and schedule collisions.
Killing sessions without a design change guarantees a sequel.
Health check vs incident response
A health check may not catch a rare monthly deadlock. It will catch chronic blocking signatures, missing monitoring, and job schedules that invite contention. That is enough to justify a focused tuning sprint.
Need hands-on SQL Server help?
Allay Data Solutions offers maintenance, tuning, and assessments from Port Elizabeth, South Africa.