SQL Profiler
may produce a massive amount of output on a busy database system if you do not introduce
filtering. The first level of filtering is to select the events
to be filtered such as query batch (SQL:BatchCompleted) or remote procedure call completed (RPC:Completed). Once you targeted the events, you can set filters
on duration for example to 3,000 (3 sec) and over or on the number of reads at 10,000 and over.
When you start optimizing a system you can catch the slow queries/sprocs with a filter setting of duration > 10,000 (10 sec). As system performance improves you can progress toward 2,000 (2 sec).
It is best to run SQL Profiler on a different computer, not on production, provided network band is available. Results can be saved to a table on the different computer or to a file.
You can also run server-side tracing with the same considerations as above on the production computer saving trace to a disk file. You can load trace file into table with a special function:
-- Load trace from file to table
USE AdventureWorks;
GO
SELECT * INTO #TR20120315
FROM fn_trace_gettable('F:\data\serversidetrace\TR20120315.trc', default);
GO
Articles:
http://www.sqlusa.com/bestpractices/createtrace/
http://www.sqlusa.com/bestpractices/default-trace/
|