Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Free SQL Server Webcast > Building Really Fast SQL Server VMs
 

Function to quickly find the worst performing SQL Server Stored Procedures


By:   |   Read Comments   |   Related Tips: More > 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)			

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

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.


Last Update:


next webcast button


next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools