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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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