Introduction

In an ERP project or any other big applications, there may be many Stored Procedures used. But we are not sure that all the stored procedures are required or not or valid ones. While the requirement(s) has changed, we may ended with another new stored procedure or based on the performance we may have created a new stored procedures and those old or unused stored procedures may not be deleted.

In this article, let’s have a look at, the list of stored procedures with last execution time. This will help us to know whether the stored procedure is required or not in the Database and also helps in clean up process.

Last Execution of Stored Procedure

Let’s run a simple T-SQL to get the list of stored procedures with number of times executed and when it was last executed.

SELECT  
          SCHEMA_NAME(sysobject.schema_id) AS SchemaName,
          OBJECT_NAME(PS.object_id) AS ProcedureName, 
          execution_count AS NoOfTimesExecuted,
          PS.last_execution_time AS LastExecutedOn
    FROM   
         sys.dm_exec_procedure_stats PS
         INNER JOIN sys.objects sysobject ON sysobject.object_id =   
         PS.object_id 
    WHERE  
         sysobject.type = 'P'
    ORDER BY
         PS.last_execution_time DESC  

When the above T-SQL is ran, it will select or display the data from the SQL server Cache.

Note – If the Cache is Cleared or SQL services restarts, the data will reset.

Fig 1. List of Stored Procedures with Last Executed Time

#sql server #last execution of stored procedure #sp last executed #sql #sql query #t-sql