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.
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.