Query Optimization: List Out Store Procedures That Executes Frequently


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

 SELECT * FROM SYS.procedures  

SQL server also provide a view where you can get the status of database store procedure. status in the sense, 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.

Kailash Chandra Behera

No comments:

Post a Comment