Paul van Oordt  SQL Services

Freelance SQL Server specialist and troubleshooter, Utrecht / Antwerpen

+31 627400408 - WhatsApp - info@vanoordt.nl
LinkedIn - curriculum vitae
vanoordt.nl - Nederlands
newsletter

Checklist SQL Server performance tuning

For a newer and more complete survey see what to know about SQL Server performance.

Below you find a large number of things to monitor and tips that you can use to improve performance of Microsoft SQL Server. If you want to gain thorough knowledge and successfully deal with performance problems, have a look at the possibilities for a in-house SQL Server crash course.

In my experience the biggest improvements in performance can usually be obtained by identifying and carefully optimizing a few problem queries. This is where improvements of a factor 10, 100 or even more can be obtained, both in cpu usage, I/O as in respons times. At least in half of all contracts I did such gains occur. Obviously the whole system benefits, not only the queries that were tuned. Also it should be clear that these sorts of problems cannot be dealt with just by adding hardware.

It is not only important to know what can cause performance problems, is is also important to know what doesn't (or need not). For instance, an adequate backup strategy is not a performance killer. Quite often I find production databases with a simple recovery model apparantly for better performance. This can lead to hours of lost work in case of emergency. Therefore: do not sacrifice essential functionality for performance. Especially not when the system hasn't been tuned by a good specialist.

General

Server

Database settings and files

Indexing

Queries

Concurrency

Database design

Data transfer

Scaling out, denormalisation, et cetera

(c) Paul of Oordt, vanoordt.nl
 

vanoordt.nl Last modified: 18 June 2021