Stored Procedure Last Run Times

Richie Lee has a script to see when stored procedures were last executed:

Quick script to get the last time a stored procedure was executed in the database. The reason for the seemingly over-engineered script is that different query plans can be generated, meaning that stored procedures can appear more than once in the list.

The query doesn’t quite work as-is, but making qs.execution_count into an aggregation and removing it from the GROUP BY would work.  I’d probably rewrite it to look a bit more like:

WITH querystats AS
(	SELECT	OBJECT_NAME(qt.objectid) AS ProcedureName,	SUM(qs.execution_count) OVER (PARTITION BY OBJECT_NAME(qt.objectid)) AS ExecutionCount,	qs.creation_time AS CreationTime,	ROW_NUMBER() OVER (PARTITION BY OBJECT_NAME(qt.objectid) ORDER BY creation_time DESC) AS rownum	FROM sys.dm_exec_query_stats AS qs	CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt	WHERE	qt.[dbid] = DB_ID()	AND OBJECT_NAME(qt.objectid) LIKE '%%'
)
SELECT	qs.ProcedureName,	qs.ExecutionCount,	qs.ExecutionCount
FROM querystats qs
WHERE	qs.rownum = 1;

Related Posts

Finding Long-Running Transactions

David Fowler has a dream: It was 3am in the morning and I was asleep and enjoying a delightful dream (I knew it was a dream because I was surrounded by drifting clouds, singing angels and hundreds of softly humming SQL Servers where the hardware had been sensibly provisioned and all code carefully optimised) when […]

Read More

Maintain MSDB

Lori Brown points out that there are some SQL Server service tables which can bloat your msdb database: I recently received a panicked call from a client who had a SQL instance go down because the server’s C drive was full. As the guy looked he found that the msdb database file was 31 GB […]

Read More

Categories

June 2016
MTWTFSS
« May Jul »
 12345
6789101112
13141516171819
20212223242526
27282930