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
- The statistics exist only for procedures whose plans are cached. They are reset when a plan leaves the cache or when the instance restarts.
total_elapsed_timeandavg_elapsed_timeare in microseconds.