| |
 |
|
SQL Server Tips by Burleson |
Obtain a Baseline
Before commencing any optimization attempts, the DBA should obtain a
performance baseline that provides the execution metrics of the
query or stored code. At a minimum, elapsed execution time, CPU
utilization, and I/O resource usage should be understood.
The DBA may already have such metrics if SQL was identified from
trace activities, but if not, these statistics are easy to get. All
one is required to do is to turn on the SHOW SERVER TRACE and SHOW
CLIENT STATISTICS options of Query Analyzer, run the code, and SQL
Server will then give the baseline needed.
Another piece of information important to have, yet often ignored,
concerns the number of times SQL or stored code is run on a daily
basis. Knowing this information is critical for a couple of reasons.
First, the DBA should not waste valuable time tuning SQL that is
seldom run on the server because it is likely to have little impact
on overall performance levels.
Next, the DBA should not be fooled by something that appears to run
quickly and is run a lot. Taking a query down from two seconds to
one will have quite an impact if the SQL is run thousands of times
per day on SQL Server.
Unfortunately, this piece of information is not easy to obtain in
SQL Server 7 – 2000 even with SQL Profiler or tracing. In SQL Server
2005, however, a new dynamic management view has been introduced
that does track the execution count of routinely executed SQL.
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 |
