Monday, July 21, 2014

Find last execution time of Stored Procedure

SQL Server stores the execution plan of statements before executing any statement.In order to find the last execution we just need to query the SYS.dm_Exec_query_stats DMV.

SELECT ST.Text AS SQL, QS.creation_time,QS.lASt_Execution_time,QP.dbID,QP.ObjectID
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_Name>%'

No comments:

Post a Comment