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