Last Run Date of a Stored Procedure in SQL Server

Question

We are starting to get a lot of stored procedures in our application. Many of these are for custom reports, many of which are no longer in use. Does anyone know of a query that we could execute in a system view in SQL Server 2005 that informed us of the last date a stored procedure was executed?

Answer 1

In a nutshell, no.

However, there are “good” things you can do.

• Run a profiler trace with, say, the stored name proc
• Add a line to each proc (create a tab, of course)
  • " INSERT dbo.SPCall (What, When) VALUES (OBJECT_NAME(@@PROCID), GETDATE() "
• Extension 2 with duration also

There are “fun” things you can do:

• Remove it, see who's calling
• Remove rights, find out who is calling
• Add RAISERROR ('Warning: pwn3d: call admin', 16, 1) , find out who is calling
• Add WAITFOR DELAY '00:01:00' , see who's calling

You get the idea. The tried and tested “see who is calling” method, supporting IT support.

If the reports are Reporting Services, you can open the RS database to run the report if you can match the code for the DataSet report.

In any case, you cannot rely on DMV because it is a restart of SQL Server reset om. Query cache / locks are temporary and do not persist for any period of time.

Answer 2

There should be a trick in the code below (> = 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 a look at Jeff Modense's advice below.

Answer 3

Oh, be careful! All that glitters is not gold! All representations and functions of the "stats" dm have a problem for this type of thing. They work only against what is in the cache, and the lifetime of what is in the cache can be measured in minutes. If you used such a thing to determine which SP candidates are candidates for rejection, you may find yourself in a world of resentment when you remove SPs that were used just a few minutes ago.

The following excerpts from the Online Book for viewing data dm ...

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

sys.dm_exec_query_stats The view contains one row for each query in the cached plan, and the row lifetime is bound to the plan itself. When a plan is removed from the cache, the corresponding lines are removed from this view.

Answer 4

sys.dm_exec_procedure_stats contains information about the execution functions, restrictions and procedures, etc. But the row has a life span. When the execution plan is deleted 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 a list of recently completed procedures.

If you want to check if a recently executed stored procedure was running

 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 

Answer 5

This works fine in 2005 (if the plan is in 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') 

Answer 6

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 

Answer 7

You should also be careful with any stored processes or objects that are only "periodically", for example, an SP that is close to EOFY, can be vital - just because it did not start after almost a year, this does not mean that it doesn’t required. Of course, use DMV as a guide (although they only show history since the last server restart), but they don’t use it alone.