When working with SQL Server, you might run across a situation where it is just not running fast enough. While there could be many reasons for this, there are tools that can help you track down just what is going on behind the scenes.
SQL Server Management Studio’s SQL Server Profiler or just (Profiler) is a tool that can be used to monitor queries run on your database.
In this blog post I’ll give a brief demonstration of the SQL Server Profiler tool. I’ll show you how to keep an eye on all the queries that are being run against your database, as well as how to use a filter to narrow your search to just slower-running queries.
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.
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.
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 pre-defined 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.
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 1000 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.
In this case we are only seeing calls that took longer than 10 milliseconds, but you can set that value to whatever fits your situation. I usually start with 5 or 10 seconds depending on the database load to find the ‘big fish’ and work down from there.
I hope this has been helpful and informative. If you have any questions, please comment below.