List Out Store Procedures That Executes Frequently

Kailash Chandra Behera | Thursday, February 23, 2017

Introduction

This blog describes how to list out the store procedures in SQL that executes frequently and The location where the store procedure details are being store in SQL.

Getting Started


SQL server store procedures are being stored in the SQL server in sys. Procedures table with special identification number called object id. It is called the metadata table, It stores metadata details of SQL store procedure like the number of parameters used in a store procedure and parameters details of a store procedure. Using the below code you can list out all the store procedures available in your database.

 SELECT * FROM SYS.procedures

SQL Server also provides a view where you can get the status of the database store procedure. status in the sense, a number of times store procedure is executed, last executed time of store procedure etc.

The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.

The following example returns information about the top ten stored procedures identified by average elapsed time.

 SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',    
 d.cached_time, d.last_execution_time, d.total_elapsed_time,   
 d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],   
 d.last_elapsed_time, d.execution_count   
 FROM sys.dm_exec_procedure_stats AS d   
 ORDER BY [total_worker_time] DESC;

Statistics in the view are updated when a stored procedure execution completes.

Related Articles

  1. Enable SQL Remote Connection
  2. Encrypt data across communication channels
  3. Recovery Database From Warm-Standy Mode
  4. Install certificate to enable encrypted connections to the Database Engine in SQL Server
  5. Create SQL Job in SQL Server 2017“

Summary

Hope this article may be helpful to you. Happy coding and enjo.......y.

Thanks

By at