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/