List Out Store Procedures That Executes Frequently

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

Kailash Chandra Behera

An IT Professional with 12 years experience in development life cycle in windows, service and Web based application using Microsoft.Net technologies. Proven record of developing all phases of projects in Microsoft.Net technology from initiation to closure aligning with the company's Business objectives to drive process improvements, competitive advantage and bottom-line gains. -> Good exposure of independently working and developing multiple projects ->Committed to efficient and effective development of projects in a fast-paced and deadline driver environment. Skill :- Develop and design projects in various technologies of Microsoft Technology. Total IT Experience- 13+

Previous Post Next Post

نموذج الاتصال