Introduction to Profiler
SQL Server Profiler — or just Profiler — is a tool that can help monitor all the queries that are being run on your database. This allows you to see a lot of information including CPU time, reads, writes, duration, and even the text or stored proc being called along with the parameters.
That last part can be especially useful. When you see a stored procedure that is taking a particularly long time to run, you can always copy the TextData section (which will have the query along with its parameters) and paste it into SQL Server Management Studio. The nice part about this is you can then run the same stored procedure with the same parameters and get an execution plan to tell you what is slowing things down.
Using Profiler
First off, we need to open up a session of Profiler. There are two ways to do this. If you have SQL Server Management Studio open already, then you can go to the Tools drop-down on the menu bar and open SQL Server Profiler.
The other approach is to open Profiler directly from the Windows menu, as it can run as a standalone application without SSMS.
Either one of these approaches will open Profiler to the normal Connect to Server login page. You should already have a login for this as you’re supposed to be working on this database. Once you’re past the login page, you’ll get a Trace Properties page.
Trace Properties
The first tab (General) can usually be ignored if you just want to do a quick trace on something. But if you are a more frequent user, this tab lets you set up several options.
Templates are predefined traces that either came with Profiler as a built-in template or were set up in a previous session by someone else.
Save to File/Table allows you to do just that. You can save the trace to either a file or table for review later. Please note that you can also save off your trace manually if you’re so inclined.
Enable Trace Stop Time allows you set a date/time where Profiler will stop. This is a good option for long-running traces that you may not want to run through a heavy load period. Don’t forget, running Profiler does add some additional overhead to your database so leaving it running all the time is not the best idea.
Selecting Events
The last tab, Events Selection, allows us to select all of the actual events we want to see.
If you selected one of the templates from the previous tab (or if you just cycle through them), you’ll notice there are a lot of events to watch for. Of course, there is always the Show All Events checkbox, but this can be overwhelming if you’re just looking for your slower-running queries.
For most cases where I am simply looking for slow-running queries, I will go with all of the Stored Procedure events as well as the TSQL events. You can even throw in the Lock events for good measure.
Narrowing Our Search
Before we hit run, there are a couple of more things we need to do to narrow our search down.
If you run the Trace as it is, we will see a limited set of things but it will still be a lot. There are many queries and processes that run and most of them take only a few milliseconds. But as milliseconds and sub-millisecond running-queries are not what we are after, we need a way to filter those out. That’s where the Column Filters button comes in handy.
After clicking on the Column Filters button, we get the following dialog:
This is helpful in that it allows us to filter out rows that would otherwise clutter things up. As mentioned before, we are not looking for the queries that are running fast already. (Although it’s always interesting to see just how many times those are called, but that can be another performance issue altogether…)
As you can see, there are several items we can filter. For our purposes, we are interested in the duration. The duration field is length of time it takes the stored procedure to run in milliseconds.
So, if we want to screen out the stored procs that took less than a second to run, we would set the Greater Than or Equal value to 1,000 and check the Exclude rows that do not contain values checkbox.
After hitting OK, we return to our Events Selection screen. At this point, we can hit Run and get a results screen that looks like one below:
On a normal, active database, you would see something pop up here after just a few seconds as there is always plenty of traffic. The higher you set the duration value, though, the less you will see simply because most queries run fairly quickly.
Here is an image of the results of a trace with a filter set to 10 milliseconds. Note that you can see the duration, reads, writes, and more importantly, the TextData, which tell us the SP and parameters of the queries that run slower than our filter.
