Function to quickly find the worst performing SQL Server Stored Procedures

By: Eli Leiba   |   Updated: 2018-04-19   |   Comments   |   Related: > Monitoring

Problem

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

Solution

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
	)
RETURNS TABLE
AS
RETURN (
   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
   FROM 
      sys.dm_exec_procedure_stats
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) T
      CROSS APPLY sys.dm_exec_query_plan(plan_handle) H
   WHERE 
      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')
   ORDER BY 
       avg_elapsed_time DESC
       )
GO

Example Use of Function

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

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

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

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

Notes

• 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.

About the author

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