SQL Server Performance Tuning

Slow systems frustrate your staff and your customers, and they cost you money. People stop trusting the system, start keeping their own spreadsheets, and work around the problem instead of reporting it.

Most SQL Server performance problems are fixable in days, and the fix is usually cheaper than the new hardware someone has already quoted for. ☎

Our 4 point plan

[IMG-1] — Before/after on a real workload. Execution plan comparison, or a table of durations: report run time, form load, overnight batch, before and after. Redacted. Performance work is invisible, and your own case study notes make the same point — this is the only way to show it.
Alt: SQL Server query performance before and after tuning

1. Monitor workload

• Measure database speed and query use under normal operation, not just when someone complains
• Identify memory, CPU, storage and resource bottlenecks
• Establish what “normal” looks like, so there’s something to compare against
• Find out whether it’s slow all day, at 9am, or only during the overnight run

2. T-SQL queries

• Analyse and streamline the most used and most resource-intensive queries, views and stored procedures
• Fix the ones scanning whole tables where they should be seeking
• Sort out parameter sniffing, implicit conversions and non-SARGable predicates
• Replace cursors and row-by-row processing with set-based operations
• T-SQL development and optimisation

3. Indexes

• Review and update indexes to make the database structures more efficient
• Add the ones actually needed, and remove duplicates and unused ones that only slow writes down
• Identify where indexed views would optimise complex calculation and data processing
• Fix fragmentation and statistics that haven’t been maintained

4. Settings

• SQL Server’s default settings can lead to bad performance, and most installations are still running them
• Max memory, MAXDOP and cost threshold for parallelism
• tempdb configuration, which is wrong on a surprising number of servers
• Storage layout, autogrowth settings and instant file initialisation
• Maintenance plans that actually maintain something

Fine tuning the configuration alone often gets 20–30% back before anyone touches a query.

Blocking, locking and timeouts

Different problem from slow, and it needs saying separately: the database isn’t slow, it’s waiting.

Users report the system freezing rather than crawling. Applications time out. One person running a report stops everyone else working. It’s usually worse at month end.

We find what’s blocking what, and why — long-running transactions, missing indexes causing lock escalation, reports running against live tables, isolation levels nobody chose deliberately, and deadlocks the application is quietly swallowing.

Capacity and growth

Performance problems that appear gradually are usually capacity problems that nobody was watching.

• How fast the database is actually growing, and when that becomes a problem
• Whether the server has the memory for the working set it now has
• Storage headroom and where the bottleneck really is
• What happens at your busiest point, not your average one
• Whether the next twelve months need hardware, tuning, or nothing

Better to know in advance than to find out at year end.

Monitoring, not just fixing

One-off tuning fixes today’s problem. Systems drift — data grows, usage patterns change, someone deploys a new report.

We can keep an eye on it: baselines tracked over time, alerting on the things that actually matter, and a quarterly look at what’s changed. That way you hear about it from us rather than from your users.

SQL Server support and consultancy

Find out what’s wrong before paying to fix it

If you’re not sure whether the problem is queries, indexes, configuration, capacity or the application on top, don’t guess and don’t buy a bigger server yet.

A health check gives you a written report on where the problem actually is, at a fixed price → SQL Server health check

If it’s gone slow suddenly rather than gradually, that’s usually something that changed — and it’s an emergency, not a tuning project → emergency SQL Server support

If the version is old enough that it’s out of support, tuning may not be the right spend → SQL Server migration and upgrades

Links block

Related case studies