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

Dealing With The Registry From SQL Server

Wayne Sheffield shows how to read and modify registry entries using SQL Server: xp_instance_regread In this example, I used xp_regread to read the direct registry path. If you remember from earlier, there are SQL Server instance-aware versions of each registry procedure. A comparable statement using the instance-aware procedure would be: 1 2 3 4 EXECUTE […]

Read More

Taking Control In The Cloud

Arun Sirpal advises you to enjoy the change in control when moving to Azure: The key for me has been to “embrace the change”. I have come from a traditional DBA background, backups, consistency checks, server level configuration at the heart of things. Yes, I no longer care about SQL backups and things of that […]

Read More


June 2016
« May Jul »