Last Run Date on a Stored Procedure in SQL Server

We starting to get a lot of stored procedures in our application. Many of them are for custom reports many of which are no longer used. Does anyone know of a query we could run on the system views in SQL Server 2005 that would tell us the last date a stored procedure was executed?

The below code should do the trick (>= 2008)

SELECT o.name, 
       ps.last_execution_time 
FROM   sys.dm_exec_procedure_stats ps 
INNER JOIN 
       sys.objects o 
       ON ps.object_id = o.object_id 
WHERE  DB_NAME(ps.database_id) = '' 
ORDER  BY 
       ps.last_execution_time DESC  

Edit 1 : Please take note of Jeff Modens advice below. If you find a procedure here, you can be sure that it is accurate. If you do not then you just don't know - you cannot conclude it is not running.

In a nutshell, no.

However, there are "nice" things you can do.

1. Run a profiler trace with, say, the stored proc name
2. Add a line each proc (create a tabel of course)

• "INSERT dbo.SPCall (What, When) VALUES (OBJECT_NAME(@@PROCID), GETDATE()"

3. Extend 2 with duration too

There are "fun" things you can do:

1. Remove it, see who calls
2. Remove rights, see who calls
3. Add RAISERROR ('Warning: pwn3d: call admin', 16, 1), see who calls
4. Add WAITFOR DELAY '00:01:00', see who calls

You get the idea. The tried-and-tested "see who calls" method of IT support.

If the reports are Reporting Services, then you can mine the RS database for the report runs if you can match code to report DataSet.

You couldn't rely on DMVs anyway because they are reset om SQL Server restart. Query cache/locks are transient and don't persist for any length of time.

Oh, be careful now! All that glitters is NOT gold! All of the “stats” dm views and functions have a problem for this type of thing. They only work against what is in cache and the lifetime of what is in cache can be measure in minutes. If you were to use such a thing to determine which SPs are candidates for being dropped, you could be in for a world of hurt when you delete SPs that were used just minutes ago.

The following excerpts are from Books Online for the given dm views…

sys.dm_exec_procedure_stats Returns aggregate performance statistics for cached stored procedures. The view contains one row per stored procedure, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

sys.dm_exec_query_stats The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.

sys.dm_exec_procedure_stats contains the information about the execution functions, constraints and Procedures etc. But the life time of the row has a limit, The moment the execution plan is removed from the cache the entry will disappear.

Use [yourDatabaseName]
GO
SELECT 	
    	SCHEMA_NAME(sysobject.schema_id),
    	OBJECT_NAME(stats.object_id), 
    	stats.last_execution_time
    FROM   
    	sys.dm_exec_procedure_stats stats
    	INNER JOIN sys.objects sysobject ON sysobject.object_id = stats.object_id 
    WHERE  
    	sysobject.type = 'P'
    ORDER BY
           stats.last_execution_time DESC  

This will give you the list of the procedures recently executed.

If you want to check if a perticular stored procedure executed recently

SELECT 	
	SCHEMA_NAME(sysobject.schema_id),
	OBJECT_NAME(stats.object_id), 
	stats.last_execution_time
FROM   
	sys.dm_exec_procedure_stats stats
	INNER JOIN sys.objects sysobject ON sysobject.object_id = stats.object_id 
WHERE  
	sysobject.type = 'P'
	and (sysobject.object_id = object_id('schemaname.procedurename') 
	OR sysobject.name = 'procedurename')
ORDER BY
       stats.last_execution_time DESC  

If you enable Query Store on SQL Server 2016 or newer you can use the following query to get last SP execution. The history depends on the Query Store Configuration.

SELECT 
      ObjectName = '[' + s.name + '].[' + o.Name  + ']'
    , LastModificationDate  = MAX(o.modify_date)
    , LastExecutionTime     = MAX(q.last_execution_time)
FROM sys.query_store_query q 
    INNER JOIN sys.objects o
        ON q.object_id = o.object_id
    INNER JOIN sys.schemas s
        ON o.schema_id = s.schema_id
WHERE o.type IN ('P')
GROUP BY o.name , + s.name 

This works fine on 2005 (if the plan is in the cache)

USE YourDb;

SELECT qt.[text]          AS [SP Name],
       qs.last_execution_time,
       qs.execution_count AS [Execution Count]
FROM   sys.dm_exec_query_stats AS qs
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE  qt.dbid = DB_ID()
       AND objectid = OBJECT_ID('YourProc') 

I use this:

use YourDB;

SELECT 
    object_name(object_id), 
    last_execution_time, 
    last_elapsed_time, 
    execution_count
FROM   
     sys.dm_exec_procedure_stats ps 
where 
      lower(object_name(object_id)) like 'Appl-Name%'
order by 1