Introduction to SQL Server Performance Tuning
July 12, 2000
I wish I was able to provide you with a comprehensive list of steps that you could follow that would optimize your SQL Server for performance 100%. Unfortunately, life is not so easy. SQL Server performance is a complex interchange among many variables, many of which you have little or no control. For example, here are some of the many variables that affect SQL Server Performance (not comprehensive):
This list goes on and on. As a DBA, all you can do is attempt to isolate the factors you can control, and optimize performance from there.
Another factor that makes performance tuning difficult is that a recommended performance tuning technique may help in one situation, but the same suggestion may actually slow down performance in another situation. Many times performance tuning is a series of compromises, where you, as the DBA, have to decide which factor is the most important.
SQL Server Performance tuning and optimization is a learned skill, just as any "art" is. The better your foundation in the basics and the more experience you have, the better you will be at your job.
Here are some general introductory tips to help you become better at performance tuning SQL Server:
Master the basics. The better you understand how SQL Server works internally, the better you will be able to optimize it. Any attempt at optimization without knowing what is going on under the hood, is like trying to optimize the operation of a nuclear power plant without any understanding of nuclear power.
Read the book, Inside SQL Server 7.0, by Kalen Delaney. This is the best book on the market on how SQL Server works internally.
Performance tuning and optimization is not something that should be left to the end of a project, or after you have gone into production and discovered performance problems, it is something that needs to be considered up front when the project begins.
When performance tuning SQL Server, make only one change at a time, then check to see if the change bought you anything. This assumes you have already some benchmark on which to compare the results after your change. If you don't have good before and after records, then you won't be able to tell if the change you made was helpful.
Record your performance tuning experiments in a log. This forces you to do your experimenting a little more carefully and helps to prevent yourself from repeating mistakes.
Unless the performance issue is obvious, the
best places to
starting looking for performance issues are the following areas, and
in this order:
This may seem unintuitive as first glance. Most DBAs want to start with the hardware. But in the real world, the above order indicates the greatest likelihood of where performance problems may lay. While all these areas can provide performance improvement, the first two often provide the greatest room for performance improvements.
In some cases, finding performance problems is made more difficult because two or more performance problems are conspiring together against you. This is a tough problem to work out. The secret to dealing with this is to try only one fix at a time, testing performance before and after each change. Eventually, you will find all your performance problems.
When attempting to locate a performance issue, find out if the problem is consistent, or if it varies over time. If it does vary over time, the issue may have to do with how busy the overall server is. For example, perhaps there are no performance issues before 8:00AM or after 4:00PM, but between these hours the number of users on the system may max out the server's ability to function quickly. Knowing this can be of great benefit when locating performance issues.
Don't let a single SQL Server act as both an OLTP and a OLAP server. OLTP and OLAP are mutually exclusive uses of a SQL Server, and if you try to do both on the same server, then performance can significantly degrade (unless the server is very lightly used for both). Ideally, OLTP and OLAP activities should be run on separate servers, with each one being tuned for its own specific type of function.
Ideally, you should tune your SQL Servers to meet peak usage levels, not average usage levels. For example, if you know that between 10:00 AM and 11:00 AM every day that usage peaks and causes a bottleneck on the server, you should act to remove that bottleneck, even though it only lasts for a short time. You don't want your users to suffer during that very busy hour. Design your server capacities with peak usage in mind.
Always test any tip you find on this site, and anywhere else for that matter, on non-production SQL Servers first. Not every performance tuning tip is designed for every SQL Server environment.