When was last time Stored Procedure Executed?

All SQL Statement sent┬áto SQL Server Engine is Parsed,Compiled and Executed.The Compiled statements are stored in┬ásystem tables and can be retried using DMV’s

sys.dm_exec_query_stats:Returns aggregate performance statistics for cached query plans in SQL Server. 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.

SELECT qs.sql_handle,qs.statement_start_offset,qs.statement_end_offset,
qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid,st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.text like'%sp_Who%'

Im1

sys.dm_exec_cached_plans:Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

SELECT UseCounts,RefCounts, Cacheobjtype, Objtype,
ISNULL(DB_NAME(),'ResourceDB') AS DatabaseName, TEXT AS SQL,plan_handle
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
where TEXT like '%sp_Who%'
ORDER BY dbid,usecounts DESC;

Im2

0 thoughts on “When was last time Stored Procedure Executed?

  1. The execution time column does not show the last time when the proc is executed, it is showing the current datetime. May be you selected wrong column??

Leave a Reply

Your email address will not be published. Required fields are marked *