LLI Wiki — My Digital Knowledge Base

Recent stored procedure execution statistics

List stored procedures by most recent execution with their average elapsed time, from sys.dm_exec_procedure_stats.

The sys.dm_exec_procedure_stats view keeps execution statistics for the stored procedures whose plans are still in the cache. The query below lists the procedures by most recent execution, with their average elapsed time.

Query

SELECT
    SCHEMA_NAME(sysobject.schema_id) AS SchemaName,
    OBJECT_NAME(stats.object_id) AS SPName,
    cached_time,
    last_execution_time,
    execution_count,
    total_elapsed_time / execution_count AS avg_elapsed_time
FROM sys.dm_exec_procedure_stats stats
    INNER JOIN sys.objects sysobject
        ON sysobject.object_id = stats.object_id
WHERE sysobject.type = 'P'
ORDER BY stats.last_execution_time DESC;

Notes

Mastodon LLIMastodon SocialFosstodon