SQL Server performance tuning
Find out why SQL Server is slow before buying more hardware
Slow screens, reports that time out, overnight jobs running into the working day. We diagnose SQL Server performance problems from evidence and fix the cause, not the symptom.
What we investigate
Performance problems we’re asked about
They often arrive together: blocking makes CPU look busy, a bad plan makes storage look slow.
- Slow applicationsScreens and transactions that used to be quick and now aren’t, or are slow only at certain times.
- High CPUSustained CPU from parallel plans, implicit conversions, scans or excessive compilation.
- BlockingLong-running transactions holding locks, and the queue of sessions waiting behind them.
- DeadlocksProcesses that acquire locks in different orders, and the error 1205 your users see as a failed save.
- Inefficient queriesNon-SARGable predicates, key lookups, row-by-row processing and estimates that are far from reality.
- Poorly performing stored proceduresParameter-sensitive plans that suit one customer’s data and fail for the next.
- Index problemsMissing indexes, duplicated and unused ones that slow every write, and statistics gone stale.
- Excessive waitsPAGEIOLATCH, WRITELOG, RESOURCE_SEMAPHORE, CXPACKET and the rest, read in context rather than in isolation.
- CapacityWhether the server is genuinely too small for the workload, and if so which resource, by how much.
- ConfigurationMax server memory, MAXDOP, cost threshold for parallelism, tempdb layout and power settings.
Method
Evidence first, then one change at a time
SQL Server records a great deal about where time goes. We read it before recommending anything.
What we look at
- Wait statistics from
sys.dm_os_wait_stats, compared with a baseline rather than taken at face value - The most expensive queries by CPU, reads and duration from
sys.dm_exec_query_statsand Query Store where it is enabled - Actual execution plans: estimates against actual rows, scans, lookups, spills and parallelism
- Blocking chains and deadlock graphs from the
system_healthExtended Events session - File-level I/O latency from
sys.dm_io_virtual_file_stats, and memory grants and pressure - Index usage, missing index suggestions (treated with caution) and statistics age
What you get
- A plain explanation of what is causing the slowdown and why it has appeared now
- The changes that address it, in priority order, with the risk of each
- Changes made by us or by your team, as you prefer, with before and after measurements
- Where hardware genuinely is the constraint, which resource and roughly how much more
Ask for a fixed-price assessment. We’ll give you a fixed price after a short conversation.
Further reading Why is SQL Server suddenly slow?
Further reading SQL Server blocking and deadlocks explained
Questions
Before you get in touch
Will more memory or faster storage fix it?
Sometimes, but often not. A query scanning a large table because of a missing index or a bad plan will use whatever hardware it is given. We establish which resource is really the constraint before anyone spends money on it.
Do you need access to our live server?
Diagnosis needs to see the real workload, so yes, usually through remote access that your team controls. Most evidence comes from read-only views that have little effect on a running server. Nothing is changed without your agreement.
What if the problem is in the application code?
Then we show exactly which queries or procedures are responsible, with the plans and figures, so your developers or software supplier can act on it. We can also propose rewritten queries or indexes for them to test.
Next step
SQL Server slow and nobody can say why?
Tell us what users are seeing, when it started and what has already been tried.