Press "Enter" to skip to content

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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;