Last Execution of Stored Procedure in SQL Server


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.

          SCHEMA_NAME(sysobject.schema_id) AS SchemaName,
          OBJECT_NAME(PS.object_id) AS ProcedureName, 
          execution_count AS NoOfTimesExecuted,
          PS.last_execution_time AS LastExecutedOn
         sys.dm_exec_procedure_stats PS
         INNER JOIN sys.objects sysobject ON sysobject.object_id =   
         sysobject.type = 'P'
         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

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with

Up ↑

%d bloggers like this: