| |
 |
|
SQL Server Tips by Burleson |
Creating Traces
Every DBA is going to have their own preferences as to what events
they want to trace, as well as what columns they want to see in
terms of statistical metrics. That said, the up_wl_trace_sql
procedure below allows the DBA to capture the SQL executions likely
needed (individual SQL executions, procedure runs, plus SQL calls
from each procedure run) to obtain the majority of statistics
desired; excluding Microsoft system operations (SQL Agent, etc.) and
allowing filters to be set on CPU, Reads, and Duration.
* up_wl_trace_sql
-- Script is available in the Online Code Depot
The procedure’s one required input is a trace file name written to
the server. The trace will have to be manually stopped with a couple
of commands that require the trace number. This trace number is
outputted when the above procedure is run. If, for example, the
trace number was one, the following commands would need to be issued
for the file to be closed and available for analysis:
exec sp_trace_setstatus 1,0
exec sp_trace_setstatus 1,2
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 |
