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. Pokémon Nut. 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