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):
-
SQL Server (the program
code itself) -
SQL
Server Configuration -
The SQL Server
Application (the Transact-SQL running on the
server) -
Server Hardware
-
Operating System
-
Network Hardware
and Bandwidth -
Client Hardware
-
Client Application
-
Number of Clients
-
Client Usage Patterns
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:
-
The Application (includes the client and server code)
-
Database
Design (logical and physical) -
SQL Server (configuration settings)
-
Hardware (bottlenecks, bad design, bad configurations, etc.)
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.
