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?

147537 次浏览
小开
最佳答案

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.

小开

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.

小开

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

小开

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
小开

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

提交答案