Last Execution of Stored Procedure in SQL Server

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.

When the T-SQL is ran, it will result as shown in the above image (Fig 1). The first column results the Schema Name, the second column results the Stored Procedure Name, the third column results the Number of times Stored Procedure is executed and the last column results the Last Execution of Stored Procedure.

Conclusion

Based on these results, we can clean up the Stored Procedure. I hope you all found this article useful. Please share your feedback in the comment section.