Get Stored Procedure Last Run Time

Hello!

Quick script to get the last time a stored procedure was executed in the database. The reason for the seemingly over-engineered script is that different query plans can be generated, meaning that stored procedures can appear more than once in the list.

SELECT OBJECT_NAME(qt.objectid) AS [StoredProcedure]
,qs.execution_count AS [Execution Count]
,qs.creation_time AS [Age in Cache]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
INNER JOIN (
SELECT OBJECT_NAME(qt.objectid) AS [sproc]
,qs.execution_count AS [count]
,MAX(qs.creation_time) AS [latest]
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()
GROUP BY OBJECT_NAME(qt.objectid)
,qs.execution_count
) AS subquery ON OBJECT_NAME(qt.objectid) = subquery.sproc
AND qs.creation_time = subquery.latest
WHERE qt.[dbid] = DB_ID()
AND subquery.sproc LIKE '%%'
GROUP BY OBJECT_NAME(qt.objectid)
,qs.execution_count
,qs.creation_time
OPTION (RECOMPILE);

Happy scripting!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s