| |
 |
|
SQL Server Tips by Burleson |
The Impact of Speed
One might look at this performance model and think, “Well, speed
doesn’t seem to matter much at all in this model.” This simply is
not true. If there is a database server with accessibility nearly
always ensured and with resource availability at peak levels around
the clock, does that mean peak efficiency is not at risk? No, of
course not. Speed becomes the all-important component of the
performance model when and only when availability is under control.
After availability is assured, speed becomes king.
The phone call most DBAs hate is the one that starts off with the
complaint “Gee, the database sure seems to be running slow!” Of
course, the network, application, and various other stress points
could not be the problem. It always has to be the database, right?
For the moment, remove all other mitigating factors and consider a
situation where there actually is something going on at the database
level.
What does slow mean? Slow could mean results from a SELECT statement
are not coming back in reasonable amount of time, the heavy-duty
procedure that is doing some serious number crunching is not
completing within historical timeframes, or a process just seems to
hang. The first two symptoms generally fall into the area of speed,
while the last problem is typically one of resource availability.
Speed’s impact on the overall peak efficiency model will seldom
reduce the overall measure to zero or near zero, but low speed
ratings in the performance model can certainly make life miserable
for users. How can the overall impact of speed be measured? First,
availability has no such dependencies tied to speed.
It is important to understand that speed, as it is defined in the
performance model, does not come into play with issues like how fast
an incident of lock contention is resolved or the quickness at which
a database’s free space is restored. Speed is comprised of how
quickly and effectively data is accessed, the efficiency of a
database’s code line, and the success of the physical database
design.
As with availability, the various components and building blocks of
speed cannot be weighted in some static nature. Some will count more
than others, depending on the particular personality of the database
and the total work being done at the moment. For example, nearly
every SQL Server professional has watched database performance
suffer at the hands of a user who has executed a massive procedure
or runaway query.
Such a situation could signal problems in either the access or code
efficiency components of the performance model. More than likely, it
is a code imbalance in the system, which would fall under the code
efficiency portion of the model. While the problem procedure or
query would temporarily depress measures like a cache hit ratio or
other memory statistics, the root cause of overall speed degradation
is a piece of code out of balance with the normal work being
conducted on a system.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
