Performance Tuning using SQL Profiler

The standard method of capturing SQL within SQLserver is to use Profiler. The output produced from the profiler can be saved to a file or to a table.
Once this information has been captured you can start checking out what SQL has the longest duration, one way to start SQLserver performance tuning.

Reading around the various SQLserver websites it is better to save the output from the profiler to a file.

On all the instances I look after I have setup an automated job which runs the profiler on a weekly basis. I have to test whether I can run the profiler more often. However as the databases are reasonable stable production environments there is little need to run every day.

I have written a couple of scripts to create stored procedures to run the profiler and create the job. I will add the scripts as a separate post.

After all that, what do you look for?

SQL and Stored procedures which have a long duration per call and large overall duration.

This is only part of the whole tuning picture which should always include looking at waits, however I prefer to look for the low hanging fruit during the tuning process. This low hanging fruit is the SQL or procedure missing an index, or whose logic is flawed from a performance perspective.

I will provide more scripts in the near future to perform the analysis based on the trace files produced from SQL Profiler. In SQLserver 2000 SP3, Microsoft provided a function which can be called directly from the database called ::fn_trace_gettable.
The best method is to use that function to grab the trace data from the trace file and insert it into a table.

The scripts to automate the tracing will follow this post.

Have Fun

profiler
SQL