Find Long Running SQL Server Queries Using Profiler

By: Ben Snaidero | Updated: 2016-08-05 | Comments (4) | Related: > Profiler and Trace

Problem

Every DBA faces the issue of long running SQL Server queries running against their database systems. Ideally any long running queries would be identified and fixed during the development process, but as we all know this isn't always the case. Whether some new T-SQL slips through without being thoroughly tested or data differences between your development/testing and your production environment cause you to have a long running query executing in production or simply someone trying to troubleshoot a production issue executes an ad-hoc query that runs forever. Whatever the reason, we as DBAs need a way to identify these long running transactions. The following tip will provide one method you can use to identify any long running queries executing in your environment.

Solution

If you've read any of my other tips or my tutorial you'd notice that I am a big fan of using the SQL Profiler tool to identify or measure query performance. This tip will go through an example and show how we can use this tool to identify any long running queries that are running against your SQL Server database instance.

Test Setup

In order to demonstrate how to capture a long running query in your database instance we will create a couple simple stored procedures. One will simply have a call to the WAITFOR DELAY command, this will be our slow query and a second that will call this stored procedure. We have this second stored procedure so that we can show how to drill down within a stored procedure in SQL Profiler as in many cases there could be many different statements in a stored procedure and it is usually just one that causes the slowness. Below is the T-SQL to create these two stored procedures.

-- create test stored procedures
CREATE PROCEDURE spTestSubProcedure
AS
BEGIN
 WAITFOR DELAY '00:00:15'
END
GO
CREATE PROCEDURE spTestProcedure
AS
BEGIN
 SELECT getdate()
 EXEC spTestSubProcedure
 SELECT getdate()
END
GO

SQL Profiler Session Setup

For details on launching SQL Profiler and for more details on completing the General tab of the Trace Properties screen please refer to my tutorial. For this example we will strictly concentrate on configuring the "Events Selection" tab of this screen including setting the appropriate filter in order to capture any long running queries.

There are a few different events that are needed in order to capture all the SQL statements that could be executed against your SQL Server instance. First there are batch commands which are captured by including the SQL: BatchCompleted event in your trace. The other events are stored procedure calls which can also be captured with the previous event depending on how they are called, but to ensure they are captured we will include the RPC: Completed and SP: Completed events. A good description of the differences between the types of calls to our database can be found here. This is an older article, but the descriptions are still relevant.

It's also a good idea to include the SP: StmtCompleted event in your trace as in a lot of cases it's a single statement in your stored procedure that will cause the slowness and this event will allow you to capture that detail.

With all the events above you can also include the corresponding "Starting" event to capture when a statement is started. This can come in handy when a statement takes a really long time to complete. If you run your SQL trace session for a fixed amount of time a query could technically not finish during your trace session so you would not capture the "Completed" event. Below is a screenshot showing the "Events Selection" tab with the "*:Completed" events mentioned above.

One other thing to note on the "Events Selection" tab above is you can also add some columns to help you find what application/user is executing the query. I usually just have LoginName as shown above as that is usually enough, but you could also add things like HostName or clientprocessid to help you track down the application/user.

Next we need to configure our statement filter since we don't want to collect every statement executed against our SQL Server instance, only the slow queries. Defining what a slow query is will depend on your application and the SLAs defined for it, but in most cases for the applications I have dealt with something around 1 second is a good starting point. Remember that SQL Profiler uses milliseconds so when setting the filter to find queries taking longer than 1 second to execute we will have to enter 1000. Below is a screenshot of this configuration.

SQL Server Profiler Filter for Duration Greater Than 1000

SQL Profiler Session Example

Now that we have our SQL Profiler session configured we can start it and execute the stored procedure we created during the test setup, spTestProcedure, using SSMS. After executing the stored procedure you should see the following in your SQL Profiler output window.

SQL Profiler Output for Long Running Queries

Notice in this output window we can see the cascading call to the stored procedure that is called from the main stored procedure. Since we also have the SP: StmtCompleted event we can also see that it's the "WAITFOR" statement within this stored procedure that is causing the slowness. This is a really simple example, but it shows how with just a few events and a single filter you can use SQL Profiler to find long running queries in your SQL Server instance.

Next Steps

Read my tutorial on using SQL Server Profiler
Read other tips on SQL Profiler
Read other tips on performance tuning

About the author

Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2016-08-05

Comments For This Article

Monday, December 26, 2022 - 9:40:11 AM - vsql	Back To Top (90783)
Hello, Can you help to get the long running data from the trace which we already have customised.

Friday, March 13, 2020 - 1:22:32 PM - Benji

I particularly use this command in my ssms. and it tells me the delay and time taking by the query because profile is kind of hard to understand.

SELECT sqltext.TEXT,

req.session_id,

req.status,

req.command,

req.cpu_time,

req.total_elapsed_time

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Monday, August 8, 2016 - 9:13:43 AM - Ben Snaidero

Hi Jeff

I agree with you that a server side trace or using extended events are more lightweight options for running a trace but they take a little more time to setup than using profiler. I disagree with you on your first point though. I've always run profiler from a remote server and have never had an issue. With testing I've done I've only ever noticed a 3-5% increase in load on the server. I would not run profiler on the production database server itself as it can use a lot of memory and that would definitely have more of an impact on you database instance. I try never to have any other applications running on my database server as it's always best to have a server dedicated to your database to avoid any of these performance issues.

thanks for reading

Ben

Friday, August 5, 2016 - 6:00:29 PM - jeff_yao

I think this tip aims at junior DBAs, so I'd like to throw a few cautions here (for production system)

1. Try to avoid running Profiler against your production system, esp. when you run the profiler from your local computer connecting to production system. Otherwise, you may have a big impact on the production. The best way here is to RDP into your production box and run Profiler locally on the production box.

2. If you really like Profiler, to minimize the impact, I'd suggest that your 1st step should be targeted against Stored Procedures only, i.e. capture only SP:Completed with a filter on duration, your next step can be capturing SP:Completed and SP:StmtCompleted BUT with a filter to filter only on the SPs that you captured in step 1.

3. Personally, I'd recommend setting up server side trace to capture events of your interests. The better way is probably using Extended Events (for sql server 2008+) which have much less overhead cost.