How To Find Long Running Stored Procedures In SQL Server?

Performance issues on a production SQL Server can be tracked using various means. In case if you want to track and find the stored procedures which are running for long time say more than a second, you can use the tools like extended events, profiler or server-side trace. In this article, I’ll use both the extended events method and the profiler method to find long running stored procedures on SQL Server.

Jump to any of the methods as you wish:

Find Long Running Stored Procedures Using Extended Events

In my earlier article, I wrote a brief details about SQL Server Extended Events and a simple illustration on how to use them. In this article, I’ll explain with step by step instruction on how to use extended events using SSMS to find the stored procedures running for more than a second on any database on a SQL Server. Follow the below steps.

Creating an Extended Events Session

Creating A Session

  1. In SQL Server Management Studio 2016 or higher, go to Object Explorer > Management > Extended Events > Sessions.
    Find Long Running Stored Procedures Using Extended Events 0
  2. Right-click the Sessions folder and from the right-click menu select New Session...
  3. In the New Session pop-up window, select General from the left panel and enter Session Name. I’ve named it LongRunningSP.
    Find Long Running Stored Procedures - Extended Events 01

Add Events & Actions

  1. Now, choose Events page from the left panel. In the Event Library, search box, type module. From the filtered events, select module_end event and click the right arrow. The module_end event is now listed under the Selected events list in the right.
  2. Then, select the Configure button at the right top, above the selected events. The configuration section will emerge from the right side.
    Find Long Running Stored Procedures - Extended Events 02
  3. Now, from the Global Fields (Actions), select the actions by checking the check box. For this illustration I’ve selected, database_name and sql_text. If needed you can select more events.

Add Filters

  1. Now, go to the Filter(predicate) panel. Click inside the filter panel to add a filter clause. In Field, select duration. Under operator select >=. Under Value enter 1000. This filter clause will track and collect only the queries which are running for 1000 Milliseconds (1 second) or higher.
    Find Long Running Stored Procedures - Extended Events 03

Set Storage Location

  1. You need to set a location and method to store the collected action and events. So, choose the Data Storage page from the left panel. In the main panel, under Type, select event_file. Select a folder and file name in File name on server field. Change the Maximum file size if needed. By default the maximum file size will be 1 GB. For this illustration I’ve changed it to 10 MB.
    Find Long Running Stored Procedures - Extended Events 04
  2. Now, go to the Advanced page. Change the event retention mode to Multiple event loss. Enter Maximum dispatch latency as 1 second. Change the Maximum event size.
    Find Long Running Stored Procedures - Extended Events 05

Save The Session

  1. Finally, press the OK button.
  2. To check whether the session is created properly, go to Object Explorer > Management > Extended Events > Sessions. You will see the new session LongRunningSP. You can edit the session by right-clicking and select edit in right-click menu.
    Find Long Running Stored Procedures - Extended Events 06

Starting the Extended Event Sessions & Collecting Data

By default, the session will be in inactive state. Follow the below steps to start the session and track the events.

  1. To start the xEvent session, right-click the session LongRunningSP and select Start Session in the right-click menu. The session will start collecting the events and store them in the location you have set.
  2. For illustration purpose, I’ve created a stored procedure which executes a select statement after a delay of 2 seconds. I’ve executed this stored procedure couple of times.
    CREATE PROCEDURE MyTecBitsToDelay
    AS
    BEGIN
    	WAITFOR DELAY '00:00:02';
    	Select getdate()
    END
    GO
    
  3. To check the long running stored procedures collected by the session, double-click on the event file package0.event_file underneath the session. A query window will open with the list of tracked events. On selecting an event, you can see the details of the stored procedure in the result window.
    Find Long Running Stored Procedures - Extended Events 07
  4. To disable the session and to stop it from collecting the data, right-click the session name and select Stop Session.

Find Long Running Stored Procedures Using Profiler

  1. Launch Profiler.
  2. From the File menu, select New Trace… .
  3. In the login window, select the sever and login.
  4. In the trace profiler window, Enter a name for the trace under the General section.
    Find Long Running Stored Procedures - Profiler01
  5. After entering a name for the trace, go to the Events Section and un-select all the pre-selected events.
    Find Long Running Stored Procedures - Profiler 02
  6. Now, select the Show all events and Show all columns check box, so as all the vents are displayed in the grid above.
  7. In the events grid, go to Stored Procedures group and select the event SP:Completed.
    Find Long Running Stored Procedures - Profiler 03
  8. After selecting the event, you have to add a filter to trace the procedures running for more than a second i.e. 1000 milliseconds. To set the filter, press Column Filters button at the right bottom.
  9. In the Filter window, select Duration from the left panel. From the right panel, select Greater than or equal and enter 1000 in the box. Then, press OK on the filter window.
    Find Long Running Stored Procedures - Profiler 04
  10. Finally, press the Run button at the bottom.
  11. The trace will be running and you can watch the live data. For illustration purpose, I’ve created a stored procedure called MyTecBitsToDelay and executed it couple of times. On execution, this stored procedure will wait for 2 seconds and run a select statement.
  12. If you notice the trace window, You can see the captured stored procedure.
    Find Long Running Stored Procedures - Profiler 05
  13. Once you have completed the tracing, don’t forget to stop the trace by pressing the Stop icon at the tool bar or by closing the trace window.

After identifying the long running stored procedure, you can fine tune it. Do not forget to clear the cache an buffer of the stored procedure while analyzing it.

Related

Reference


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.