| |
 |
|
SQL Server Tips by Burleson |
SQL Statement Analysis
While it might not be true that 80% or more of a database server’s
performance is derived from the code that runs inside it with
physical design as the number one contributor to performance, one or
more bad SQL statements can certainly affect SQL Server’s speed at
servicing requests. Therefore, it is important the DBA stay on top
of what SQL is issued on the servers and be very adept at ferreting
out problematic SQL statements and stored procedures.
Primary attributes used when determining whether or not a particular
set of SQL or a stored procedure is worth looking into as well as
tuning have already been covered. So, the question is, “How does the
DBA find such code?” Unlike some database engines such as SQL Server,
SQL Server 7 and 2000 do not have dynamic query views that contain
issued SQL statements and their execution statistics.
In SQL Server 2005, a new view, sys.dm_exec_query_stats appears to
contain performance execution statistics for issued SQL. As of this
writing, it appears the best way to get usable information is to
issue a query such as the following sql_stats_2005.sql script:
* sql_stats_2005.sql
-- Script is available in the Online Code Depot
The DBA may not see every query issued against the server in the
above query because only cached statements and statistics will be
shown. Therefore, whether using SQL Server 7, 2000 or 2005, the one
concrete way of capturing code executed against the server is
through the use of traces.
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 |
