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

Forcing MAXDOP In Azure SQL DB

Arun Sirpal shows us that you can change MAXDOP in Azure SQL Database: In this quick post I will show you my parallel plan and how I use MAXDOP = 1 to suppress parallel plan generation so the operation will be executed serially. (Disclaimer – I am not saying this is the right thing to […]

Read More

Comparing Instance Configurations With sys.configurations

Jana Sattainathan has a script to compare two SQL Server instances’ configuration settings: Steps: 1. Create a Linked Server to 2nd instance on 1st Instance 2. Create an empty table to hold the comparison data 3. Insert the 1st instance sys.configurations data into the table 4. Insert the 2nd instance sys.configurations data into the table […]

Read More

Categories

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