| |
 |
|
SQL Server Tips by Burleson |
The SQL Optimization Toolbox
To troubleshoot and optimize SQL and stored procedures, software is
needed that helps the DBA quickly diagnose a particular query,
understand what the optimizer is doing, get basic performance and
execution statistics, and easily test different rewrite
combinations. Software exists that does more than this, but most
optimization pro’s get the majority of their work accomplished with
these basic features.
Fortunately, everything described above can be found in Microsoft’s
Query Analyzer tool, which has been bundled into SQL Server 2005’s
Management Studio. Although many SQL Server professionals use Query
Analyzer to run queries, some do not exploit the various built in
functions of the tool.
Although looking at EXPLAIN plans is important, the ability to see
the performance execution metrics at the start of, and during
optimization sessions is the most valuable aid in troubleshooting.
The trace capability is especially useful when tuning procedures as
the lines of code can be pinpointed that are consuming the most I/O,
CPU, etc. Query Analyzer makes it easy to trace and obtain all
statistics necessary when tuning a piece of SQL code.
Most SQL Server folks rarely use the trace or execution statistics
capability of Query Analyzer. These are powerful tools and should be
utilized during a tuning session. Figure 10.1 is a representation of
the results of using the SQL trace capability in Query Analyzer to
view Procedure Execution Statistics.
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 |
