It isn't stored in sys.dm_exec_cached_plans
, nor is it buried anywhere in the plan XML that I can find. There is useful information in other DMVs however.
For stored procedures we can get the time a plan was cached from sys.dm_exec_procedure_stats
:
SELECT TOP(250)
p.name AS [SP Name]
, ps.execution_count
, ps.cached_time
FROM
sys.procedures p WITH (NOLOCK)
INNER JOIN
sys.dm_exec_procedure_stats ps WITH (NOLOCK)
ON p.[object_id] = ps.[object_id]
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.cached_time DESC
OPTION
(RECOMPILE);
For ad-hoc queries, there is the creation time in sys.dm_exec_query_stats
:
SELECT TOP(250)
st.[text] AS [QueryText]
, qs.execution_count
, qs.creation_time
FROM
sys.dm_exec_cached_plans cp WITH (NOLOCK)
INNER JOIN
sys.dm_exec_query_stats qs WITH (NOLOCK)
ON qs.plan_handle = cp.plan_handle
CROSS APPLY
sys.dm_exec_sql_text(cp.plan_handle) st
WHERE
cp.objtype = N'Adhoc'
ORDER BY
qs.creation_time DESC
OPTION
(RECOMPILE);
According to @SqlKiwi... the change from creation_time to cached_time was just because procedures and triggers were added in 2008 and the opportunity was taken to choose a more descriptive name. The created/cached time reflects the last compilation, not the creation time of the original plan.