Performance tuning: start with wait stats

When a system feels “slow”, teams often jump straight to indexes, more RAM, or rewriting the biggest stored procedure they can find. Sometimes that works. Often it wastes days.

Wait stats tell you what SQL Server is waiting on. That is usually the shortest path from symptom to root cause.

Why wait stats first?

SQL Server is excellent at telling you where time goes:

• CPU pressure vs runnable queues
• Disk latency (PAGEIOLATCH_*)
• Locking and blocking (LCK_*)
• Network / client issues (ASYNC_NETWORK_IO)
• TempDB contention
• Parallelism waits

If you skip this step, you optimise in the dark.

A practical workflow

1. Confirm the problem window

Is slowness:

• All day or only month-end?
• One report or the whole app?
• After a release, index change, or data growth spike?

Baselines matter. A single screenshot of waits without context misleads.

2. Capture waits (instance and, where useful, query-level)

Useful starting points:

• sys.dm_os_wait_stats (reset-aware; compare deltas over a known interval)
• Query Store (when enabled) for regressions
• sys.dm_exec_session_wait_stats / extended events for targeted sessions
• Blocking reports when LCK_* dominates

Clear the noise: ignore benign waits that always top the list on idle systems, and focus on what changed during the pain window.

3. Map waits to the next investigation

Dominant wait theme	Next place to look
PAGEIOLATCH_*	Storage latency, missing indexes, large scans, memory grant / buffer pool pressure
LCK_M_*	Blocking chains, transaction design, isolation level, long transactions
CXPACKET / CXCONSUMER	Parallel plans, skew, cost threshold, MAXDOP
ASYNC_NETWORK_IO	Client fetching rows slowly, chatty apps, huge result sets
WRITELOG	Log file latency, VLF issues, synchronous commit cost
CPU-bound (signal waits high)	Query CPU, parameter sniffing, missing indexes causing scans

4. Prove the fix with before/after metrics

Good tuning is measurable:

• Duration / CPU of the target workload
• Logical reads
• Wait profile during the same business window
• User-visible SLAs (report runtime, API latency)

If you cannot measure it, you cannot defend the change — and you may not notice a regression later.

Example: “Everything is slow after 9am”

A common pattern:

1. Wait stats show rising PAGEIOLATCH_SH and high CPU on a few queries
2. Query Store shows a plan change on a high-frequency procedure
3. Actual plan reveals a scan + key lookup after stats/auto-update
4. Fix may be a targeted index, plan guide / Query Store pin, or stats sampling change — not a server reboot

Reboots clear the symptom and the evidence.

What we do in a tuning engagement

At Allay Data Solutions we typically:

1. Define the business symptom and success criteria
2. Collect waits, Query Store, and top resource consumers
3. Isolate the smallest set of changes with the biggest impact
4. Document what changed and how to monitor it

Get in touch if you want a structured performance review rather than another round of guesswork.