Function to quickly find the worst performing SQL Server Stored Procedures

By:   |   Comments   |   Related: > Monitoring


The requirement is to create a function to quickly return the TOP @N worst performing stored procedures on a given SQL Server instance. 


The solution involves creating a T-SQL function that queries the sys.dm_exec_procedure_stats dynamic management view to get the worst performing procedures.  There are a few parameters that can be passed to limit what is returned.

Code Explanation

The DMV, sys.dm_exec_procedure_stats was introduced in SQL Server 2008 and this returns information from the procedure cache about store procedure executions.  We can find out how many times the procedure was executed, the total duration, reads, writes and more.

With this DMV, we will query the data and pass in parameters to limit the scope of what is returned.

The function takes the following parameters:

  • @n - the number of rows to return (default is 10)
  • @dbname - a specific database we want to focus on (default is all user databases)
  • @avg_time_threshold - this will return any procedure where the microseconds is greater than this value (default is 0)

I have excluded all of the system databases from the results, but you can changed this if needed.

Also, the results for Avg_Elasped_Time are in microseconds, so you will need to do the math to figure out seconds.

T-SQL code for TOP N Worst Performing Stored Procedures

-- ============================================================
-- Author:       Eli Leiba
-- Create date:  2018-02
-- Description:  Returns TOP N worst performing stored procedures	
-- ====================================================+========
CREATE FUNCTION [dbo].[fn_GetWorstPerformingSPs] (
   @n SMALLINT = 10,
   @dbname SYSNAME = '%',
   @avg_time_threshhold INT = 0
   SELECT TOP (@n) 
      DB_NAME (database_id) AS DBName,
      OBJECT_SCHEMA_NAME (object_id, database_id) AS [Schema_Name],
      OBJECT_NAME (object_id, database_id) AS [Object_Name],
      total_elapsed_time / execution_count AS Avg_Elapsed_Time,
      (total_physical_reads + total_logical_reads) / execution_count AS Avg_Reads,
      execution_count AS Execution_Count,
      t.text AS Query_Text,
      H.query_plan AS Query_Plan
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) T
      CROSS APPLY sys.dm_exec_query_plan(plan_handle) H
      LOWER(DB_NAME(database_id)) LIKE LOWER(@dbname) 
      AND total_elapsed_time / execution_count > @avg_time_threshhold 
      AND LOWER(DB_NAME (database_id)) NOT IN ('master','tempdb','model','msdb','resource')
       avg_elapsed_time DESC

Example Use of Function

Finding the top 5 worst performing procedures for the test database:

SELECT * FROM dbo.fn_GetWorstPerformingSPs(5, 'test', 0)			

query results

Finding the top worst performing procedures for the all databases using the default values.

SELECT * FROM dbo.fn_GetWorstPerformingSPs(default, default, default)			
query results


  • The data in the DMV will be cleared and reset if SQL Server is restarted.
  • Depending on how heavily SQL Server is used and the number of objects, some older data be removed from the cache and not present in the DMV.
  • The function was tested on SQL Server versions 2014 and 2017, but the code should be compatible with SQL Server 2008 and later.
Next Steps
  • You can create this simple function in your master database and use it as a general function to check the worst performing procedures. This is easier to remember how to run this versus having to write the entire query each time.
  • Tweak the function to further meet your needs.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

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

View all my tips

Comments For This Article

get free sql tips
agree to terms