| |
 |
|
SQL Server Tips by Burleson |
What is Bottleneck Analysis?
When the SQL Server is up and running, every connected process is
either busy doing work or waiting to perform work. A process that is
waiting may mean nothing in the overall scheme of things, or it can
be an indicator that a database bottleneck exists.
This is when wait-based or bottleneck analysis comes into play. DBAs
use this form of performance analysis to determine if perceived
bottlenecks in a database are contributing to a performance problem.
Bottleneck analysis is a valid method of measuring performance
because it helps a DBA track where a database server, user sessions,
the operating system and etc., have been spending their time. If I/O
completion waits or heavy table-scan activity has been dragging a
database’s performance down, a DBA can use bottleneck analysis to
confirm the actual root cause.
Most likely, a DBA who has to manage Oracle databases in addition to
the SQL Server is no stranger to bottleneck analysis. Oracle
introduced a wait event interface long ago, although no one really
began to notice it until Oracle7. As of this writing, Oracle
Database 10g has been out for about a year, and if the DBA has
migrated to it, it is likely that person has seen the increased
emphasis Oracle has put on bottleneck analysis as the primary method
to use in identifying performance issues.
SQL Server professionals are just now beginning to recognize the
benefits of using a bottleneck approach to performance analysis.
Much like the early days of SQL Server, few SQL Server DBAs have looked
at Microsoft’s wait event interface, but this is beginning to
change. There is more to bottleneck analysis than just examining
wait events, called wait types in SQL Server. Broadly speaking,
there are two major types of bottlenecks for which the DBA should be
on the lookout.
The first type of bottleneck is the “hit the wall” variety. An
example of this would be a SQL Server database running out of space
in its transaction log or a database suddenly going offline. For all
intents and purposes, work grinds to a complete stop, or the impact
of the bottleneck is great enough to stop major activities. These
types of bottlenecks, usually called immediate bottlenecks, are the
kind that should be detected far in advance of their actual
occurrence, because when they do occur, the DBA can expect the phone
to immediately start ringing off the hook with users lodging
complaints.
The second type of bottleneck is more insidious because it is
gradual in nature. The bottleneck starts slowly with hardly anyone
noticing a performance slowdown, but it begins to build in effect
and intensity. It slowly starts to drain the life from the SQL
Server system, and because of this, it has been nicknamed a vampire.
An example of this type of bottleneck is similar to object
fragmentation, in which an object becomes more and more disorganized
until I/O performance is drastically affected.
When troubleshooting immediate and vampire
styled bottlenecks, typically four areas of the SQL Server are
reviewed:
-
Storage
-
System
-
Sessions
-
Operating 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 |
