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