1.1 What is performance tuning?
What is the goal of tuning a SQL Server database? The goal is to improve performance until
acceptable levels are reached. Acceptable levels can be defined in a number of ways. For a
large online transaction processing (OLTP) application the performance goal might be to
provide subsecond response time for critical transactions and to provide a response time of
less than two seconds for 95 percent of the other main transactions. For some systems,
typically batch systems, acceptable performance might be measured in throughput. For
example, a settlement system may define acceptable performance in terms of the number of
trades settled per hour. For an overnight batch suite acceptable performance might be that it
must finish before the business day starts.
Whatever the system, designing for performance should start early in the design process and
continue after the application has gone live. Performance tuning is not a one-off process but
an iterative process during which response time is measured, tuning performed, and response
time measured again.
There is no right way to design a database; there are a number of possible approaches and all
these may be perfectly valid. It is sometimes said that performance tuning is an art, not a
science. This may be true, but it is important to undertake performance tuning experiments
with the same kind of rigorous, controlled conditions under which scientific experiments are
performed. Measurements should be taken before and after any modification, and these
should be made one at a time so it can be established which modification, if any, resulted in
an improvement or degradation.
What areas should the database designer concentrate on? The simple answer to this question
is that the database designer should concentrate on those areas that will return the most
benefit. In my experience, for most database designs I have worked with, large gains are
typically made in the area of query and index design. As we shall see later in this book,
inappropriate indexes and badly written queries, as well as some other contributing factors,
can negatively influence the query optimizer such that it chooses an inefficient strategy.
To give you some idea of the gains to be made in this area I once was asked to look at a query
that joined a number of large tables together. The query was abandoned after it had not
completed within 12 hours. The addition of an index in conjunction with a modification to the
query meant the query now completed in less than eight minutes! This magnitude of gain
cannot be achieved just by purchasing more hardware or by twiddling with some arcane SQL
Server configuration option. A database designer or administrator's time is always limited, so
make the best use of it! The other main area where gains can be dramatic is lock contention.
Removing lock bottlenecks in a system with a large number of users can have a huge impact
on response times.
Now, some words of caution when chasing performance problems. If users phone up to tell
you that they are getting poor response times, do not immediately jump to conclusions about
what is causing the problem. Circle at a high altitude first. Having made sure that you are
about to monitor the correct server use the System Monitor to look at the CPU, disk
subsystem, and memory use. Are there any obvious bottlenecks? If there are, then look for the
culprit. Everyone blames the database, but it could just as easily be someone running his or
her favorite game! If there are no obvious bottlenecks, and the CPU, disk, and memory
counters in the System Monitor are lower than usual, then that might tell you something.
评论0
最新资源