When was a stored procedure last executed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

When was a stored procedure last executed

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2010-02-12 : 06:46:26

Hi,

is there anyway of telling when a stored procedure ws last executed, my understanding is that you can only tell this if you are collecting trace information.

Sean

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 07:38:35

Another method (taken from BOL)

SELECT TOP 5 creation_time, last_execution_time, total_clr_time,
total_clr_time/execution_count AS [Avg CLR Time], last_clr_time,
execution_count, (SELECT SUBSTRING(text, statement_start_offset/2,
(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),
text)) * 2
ELSE statement_end_offset END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CLR Time] DESC;
GO

Madhivanan

Failing to plan is Planning to fail