Last execution date for stored procedure

Question

Last execution date for stored procedure

tt-615680

SSCertifiable

Points: 7835
More actions
January 25, 2018 at 5:22 am

#410103

Is there an other query which can be used in order to get a creation date and last execution date for a stored procedure and the query I execute is giving the wrong information e.g. both the creation and execution dates are today:
SELECT
COALESCE(DB_NAME(t.[dbid]),'8') AS [DB Name],
ecp.objtype AS [Object Type],
t.[text] AS [Adhoc Batch or Object Call],
SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
,qs.[last_execution_time] AS [Last Exec Time]
,qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
    JOIN sys.dm_exec_cached_plans ecp
            ON qs.plan_handle = ecp.plan_handle
            CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
where
    ecp.objtype = 'Proc'
order by [Last Exec Time] desc

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

That will only show the most recent plan for the stored procedure in the plan cache. Query Store in SQL Server 2016 and above may help. Otherwise, you'll need to include some logic in the stored procedure itself to record individual executions.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

For when the procedure was created, look at the create date in sys.procedures. SQL doesn't track the last execution date except as tied to the plan (which is not necessarily in cache). Implement some custom monitoring, or put something into the procedure to track its executions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

The best way to monitor this in 2012 would be using Extended Events. However, it does require that you set it up and maintain it. There's nothing automatic that does this for you.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Abhishek SSCommitted Points: 1713 More actions · Answer 4

maybe this helps to get the last execution time from the cache available , try

SELECT DB_NAME(database_id)
,OBJECT_NAME(object_id)
,cached_time
,last_execution_time
,execution_count
FROM sys.dm_exec_procedure_stats