| |
 |
|
SQL Server Tips by Burleson |
Viewing Trace Information
Once these commands complete, the trace can be viewed through SQL
Profiler by using the Open Trace File option in the tool and
selecting the trace file created for this purpose.
Another option is to use the fn_trace_gettable function to view
trace results. Once the trace file has been created, a SQL like the
following can be issued to view the output, substituting the trace
file name for the example one shown:
select
*
from
::fn_trace_gettable
('c:\dev\trace\tracetest.trc',default)
The nice thing about trace data is it is granular in that each
execution of a SQL statement is seen as its own atomic unit. The bad
thing about trace data is it is granular in that each execution of a
SQL statement is seen as its own atomic unit.
Contradictory? Not really. There are some analysis situations in
which the DBA needs to see each individual run of a SQL statement
even if it is executed 1,000 times. There are other times, however,
when the DBA wants an aggregation of a trace file so it can be seen,
for example, that a particular SQL statement was run 1,000 times.
This helps troubleshoot situations in which a SQL statement runs
very fast, but runs much more often than it should and is therefore
eating away at resources.
Unfortunately, the DBA cannot use the fn_trace_gettable function and
perform aggregations on the TextData column, which contains the
actual SQL text because the column type is a text datatype. However,
a table from the function where the TextData column is altered to be
a long varchar column such as 3000 or so can be created. The DBA can
then find out execution counts with a simple query like the
following:
select
TextData,
count(*)
from
tracetab
group by
TextData
order by
2 desc
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 |
