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

Preventing Server Manager From Loading

Steve Stedman shows how to prevent the Server Manager app from loading whenever you RDP into a Windows Server machine: If you frequently connect to many different SQL Server as I do, you are probably used to the Server Manager loading slowly when you log in with Remote Desktop. The Server Manager has a bad […]

Read More

Listing SQL Server Instances On A Server

Lori Brown has a script which lists installed SQL Server instances: It is pretty common that I frequently am asked to take over a server with SQL on it sight unseen. Many times shops many not be fully aware if additional instances are or have been installed on their server. Since I am working on […]

Read More


June 2016
« May Jul »