When was Stored Procedure Last Compiled? – Interview Question of the Week #292

Question: When was a stored procedure last compiled? sys.dm_exec_procedure_stats.cached_time tells us when a currently cached procedure plan entered the cache. That is a useful clue, not a complete history of every statement compilation or recompilation.

In the original answer I was careful about this distinction: we may not know every compilation time, but we can inspect the current plan’s cached time. That caution is still the most useful part of the answer. A timestamp with the wrong interpretation is worse than no timestamp.

Inspect the current cached plans

-- Run in the database containing the procedures you want to inspect.
SELECT OBJECT_SCHEMA_NAME(s.object_id, s.database_id) AS SchemaName,
       OBJECT_NAME(s.object_id, s.database_id) AS SPName,
       s.cached_time, s.last_execution_time, s.execution_count,
       CAST(s.total_elapsed_time / 1000.0 / NULLIF(s.execution_count,0)
            AS decimal(18,3)) AS avg_elapsed_ms,
       s.plan_handle
FROM sys.dm_exec_procedure_stats AS s
WHERE s.database_id = DB_ID() AND s.type = 'P'
ORDER BY s.last_execution_time DESC;

The database filter matters. Object IDs repeat across databases, so joining an instance-wide DMV to the current database’s objects without checking database_id can attach the wrong procedure name. The average above is explicitly in milliseconds; the DMV’s elapsed-time counters are in microseconds.

A procedure can have more than one cached plan. Cache eviction or a restart removes its rows. A procedure that has not completed execution may have no usable aggregate statistics here. A statement recompile is not necessarily the replacement of the entire procedure’s cache entry.

For an exact event, collect an event

If the question is why a statement recompiles repeatedly, collect an appropriately filtered Extended Events session, including sql_statement_recompile, while reproducing the workload. Do not clear the production plan cache just to make this query easier to interpret.

SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE for this DMV; earlier SQL Server versions use VIEW SERVER STATE.

My recent procedure execution post and its original video show the related monitoring task:

For a performance problem, I use this information alongside the plan and workload during a Comprehensive Database Performance Health Check.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.