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?
This question is related to
sql-server
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')
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
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
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
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.
Source: Stackoverflow.com