Tuesday, July 29, 2014

Can you tell when a stored procedure was last executed?

Yep.  You certainly can.  The sys.dm_exec_query DMV returns aggregate performance statistics for cached query plans.  Important note:  It is only cached query plans.  When a plan is removed from the cache (or you restart the SQL Server service), the corresponding rows are eliminated from this reference.

     SELECT 
          qt.[text] [ProcedureName], 
          qs.last_execution_time [LastRan], 
          qs.execution_count [ExecutionCount]   
     FROM 
          sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
     WHERE 
          qt.text LIKE '%your procedure name%'

To perform my test, I fired sp_who2 several times, and sp_who a couple times.  The above captured these results:  

     ProcedureText                             LastRan                                ExecutionCount
     create procedure sys.sp_who       2014-07-29 18:10:19.850          2
     create procedure sys.sp_who2     2014-07-29 18:11:27.520           5  
     create procedure sys.sp_who2     2014-07-29 18:11:27.523          5

Please take a look at these for more information:

    sys.dm_exec_query_stats -
         http://msdn.microsoft.com/en-us/library/ms189741(v=sql.110).aspx
    sys.dm_exec_sql_text -
         http://msdn.microsoft.com/en-us/library/ms181929(v=sql.110).aspx

No comments:

Post a Comment