DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to reduce SQL Profiler's output?

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/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE