sql server / case study

Recurring Blocking Review

The blocking was not random. The evidence just had not been collected in the right shape yet.

A SQL Server case study about recurring blocking where the team needed evidence, not another round of hopeful tuning.

Technical evidence checked

Blocking capture

Blocked session snapshots, head blocker identification, wait resource, lock mode, transaction age, open transaction count, and host/application names where available.

Runtime sources

sys.dm_exec_requests, sys.dm_tran_locks, sys.dm_os_waiting_tasks, execution plans, Query Store or plan cache samples, and SQL Agent job timing.

Pattern check

Busy-hour samples compared with quiet-hour samples so the review did not blame a one-off query for a repeat workload pattern.

Likely causes

Long transactions, lookup-heavy access paths, batch jobs colliding with user workload, and inconsistent transaction order.

Fact-check note

The page avoids saying blocking is always an indexing problem. Indexing can reduce lock footprint, but transaction scope and workload timing often matter just as much.

Case snapshot

Users were seeing timeouts and slow periods, but the database did not look permanently overloaded. The pain appeared in waves, which made every quick review feel partly convincing and partly wrong.

The team had already tried a few fixes. Some helped briefly. None explained the recurring pattern well enough to trust.

That is the point where performance work needs to slow down slightly before it speeds up. Without the right evidence, every fix risks changing the symptom instead of removing the cause.

Item	Detail
Environment type	Production SQL Server with recurring user-facing slow periods
Main concern	Blocking appeared in waves and earlier fixes did not explain the pattern
Service fit	SQL Server performance review
Primary risk	The team could keep tuning victim queries while missing the head blocker
Useful output	A triage order across blocking capture, immediate relief, transaction scope, and follow-up tuning

Technical evidence reviewed

The performance review focused on blocking chains, head blockers, transaction duration, job timing, access paths, isolation behavior, and whether the captured evidence matched the user complaints.

It did not start by tuning the noisiest query. It started by proving what was waiting, what was holding locks, and whether the same shape returned across busy periods.

That changed the discussion from general slowness to a more useful production story.

Evidence	What it checked
Blocked session snapshots	Which sessions were victims and which session was actually holding the chain
Transaction age and open transaction count	Whether long transactions were holding locks longer than expected
Wait resource, lock mode, and object context	Which tables, indexes, or access paths were involved
Query plans and Query Store or plan-cache samples	Whether access paths expanded the lock footprint
SQL Agent job timing and workload calendar	Whether scheduled work collided with user activity
Busy-hour and quiet-hour comparisons	Whether the pattern was repeatable or only one noisy capture

Findings

The review produced a blocking story the team could act on. It separated immediate pressure relief from deeper changes around transaction scope, indexing, and workload timing.

That distinction mattered. The team no longer had to pretend that one change would settle everything.

Finding	Evidence	Risk	Practical action
Victim sessions were getting too much attention	Blocked sessions were easier to see than the repeat head blocker	The team could tune the wrong query	Capture and label head blockers during incidents
Transaction scope needed review	Some blocking periods aligned with longer-running transactions	Locks stayed alive longer than the business action required	Review transaction boundaries and commit timing
Scheduled work overlapped with user pressure	Blocking windows lined up with job or batch activity	Useful jobs could make busy periods worse	Move or reshape scheduled work
Indexing was part of the answer, not all of it	Some access paths increased lock footprint	Index changes alone could miss the transaction pattern	Tune access paths after the blocking shape is proven

Fix order

The output started with capture quality. Without that, every later fix would still be half guesswork.

The first actions reduced production pain and made the next capture more useful. Deeper code or index changes came after the repeat pattern was clear.

When	Work	Why first
First incident window	Capture head blocker, victims, wait resource, transaction age, and query text	The next fix needs the right target
First week	Move or adjust obvious colliding jobs where risk is low	This can reduce pressure without code change
First week	Review transaction boundaries for repeat head blockers	Long locks often come from application behavior, not server settings
Next 2 weeks	Tune indexes and access paths that widen lock footprint	Index work is stronger once the blocking shape is proven
Later follow-up	Add a lightweight blocking capture routine for future incidents	The team should not restart diagnosis from zero

Outcome

Recurring blocking is often expensive because teams keep paying for the same diagnosis. The system recovers, everyone gets busy, and the evidence disappears until the next wave.

This case shows why a performance review should leave behind a way to reason about the pattern, not just a short-term fix. The fix matters. The repeatable diagnosis matters more.

When this applies

This case applies when users report timeouts or slow periods, but the SQL Server does not look generally overloaded all day.

It is a performance-review problem when the team needs to prove the blocking shape before deciding whether the real fix is query tuning, indexing, job timing, or application transaction work.

Recurring blocking or timeout waves
Several plausible theories but no stable head-blocker story
Previous tuning helped briefly but did not explain the repeat pattern
Scheduled jobs or batches may overlap with user activity
The team needs production-safe triage before bigger changes

If this looks familiar, send the rough situation.

If this looks close to your situation, send the rough problem and I can tell you where it fits.

SQL Server consulting Request a performance review