| |
 |
|
SQL Server Tips by Robin Schumacher
|
What is Bad SQL?
Before the DBA can identify problem SQL in the database, the
question of “What is bad SQL?” needs to be asked. What criteria
should be used when the hunt for problem SQL in critical systems is
performed?
Even seasoned experts disagree on what constitutes efficient and
inefficient SQL, so there is no way to sufficiently answer this
question to every Microsoft professional’s satisfaction. The
following list contains general criteria that can be used when
evaluating the output from various database monitors or personal
diagnosticScripts:
-
Overall Response (Elapsed) Time: This is
how much time the query took to parse, execute, and fetch the
data needed to satisfy the query. It should not include the
network time needed to make the round trip from the requesting
client workstation to the database server.
-
CPU Time: This is how much CPU time the
query took to parse, execute, and fetch the data needed to
satisfy the query.
-
Physical I/O: Often used as the major
statistic in terms of identifying good vs. bad SQL, this is a
measure of how many disk reads were caused by the query in order
to satisfy the user’s request. While controlling disk I/O is
desired, it is important the DBA not focus solely on physical
I/O as the single benchmark of inefficient SQL. Make no mistake,
disk access is slower than memory access and also consumes
processing time making the physical to logical transition, but
the entire I/O picture of a SQL statement should be considered.
This includes looking at a statement’s logical I/O as well.
-
Logical I/O: This is a measure of how
many memory reads the query took to satisfy the user’s request.
The goal of tuning I/O for a query should be to examine both
logical and physical I/O and use appropriate mechanisms to keep
both to a minimum.
-
Repetition: This is a measure of how
often the query has been executed. A problem in this area is not
as easy to spot as the others unless the DBA is very familiar
with the application. A query that takes a fraction of a second
to execute may still be causing a headache on the system if it
is executed erroneously. One example would be a query that
executes in a runaway T-SQL loop over and over again.
There are other criteria that can be
examined. Examples include criteria such as sort activity, temp
table usage, or access plan statistics that show items such as
Cartesian joins and the like; however, more often than not, these
measures are reflected in the criteria listed above. Fortunately,
SQL Server records most of the above measures. This makes tracking
the SQL that has been submitted against Microsoft databases much
easier than other database engines.
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 |
