SQL Server Profiler Trace Tutorial

I recently had a client asked me about how to use SQL Server profiler. I looked online and did not find anything that was concise and to the point for what I wanted to show him so I decided to just make a quick tutorial which is listed below.

This tutorial covers the usage for SQL Server Profiler 2005, 2008, and 2008R2. Profiler has many uses yet this is an introduction to the tracing tool just to get you started. So, it is pretty basic ;)

{Please keep in mind that it is best to complete traces when there is as little activity on the server as possible (to reduce the white noise)}

First, open SQL Server Profiler:

All programs > Microsoft SQL Server > Performance Tools > SQL Server Profiler

Select new trace (1st button on the left)

Connect to Server window appears and connect to the server you want to trace

Trace Properties Appears:

1) Change trace name to whatever you like (it is best not include spaces or dashes when naming)
2) Use the Standard Template
3) Click “Save to file”
     a. Specify the location of the file to be saved
     b. Set maximum file size (MB): to “100”
     c. Make a note of the file name and location
 OR 
3) Click “Save to table” (I have found that saving to a table is the easiest, for me, to analyzing because you write queries to dig out info)
     a. Connect to server window will appear
        - Connect to the server that you wish to save the table in
    b. Specify the table name (best to preface with “TEMP_”)
    c. Make a note of the table name and database location
4) Select “Events Selection” tab


 

Events Selection:
1) Make sure the below events are selected (they should be selected by default)*
          *If those events are not selected, check “show all events” and select them
2) Click show all columns
     a. Scroll to the right and find the DatabaseID and DatabaseName columns (this will help in identifying db sources)
     b. Check all the boxes in those two columns
3) Click “Run”

 

Running the trace:
1) Let the trace run for a little bit to get warmed up (1-2 mins)
2) Record the time prior to executing your process
3) Execute your process
4) Record the time after your process has completed
    a. You can repeat steps 2 through 4 a few times (leave 15 minutes or so between runs)
5) Stop the trace by pressing the red box (shown below)
    a. The trace may take some time to fully stop and then you can keep it open if it is a file or close it and review the table

Now that you have all your data (saved to the table or file that you notated), you can use the execution times as your main windows to analyze and determine what is going on during that process.

I hope that helps