Find the oldest query plans in the cache
List the ten oldest query plans still in the cache with their execution statistics.
The query below lists the ten oldest query plans still in the plan cache, with their execution statistics.
SELECT TOP 10
t.TEXT AS QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec,
query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) u
CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t
ORDER BY creation_time;
Notes
MaxElapsedTimeandAvgElapsedTimeare based ontotal_elapsed_time, which is in microseconds; the query divides by 1000 to return milliseconds.FrequencyPerSecdividesexecution_countby 1000 before dividing by the plan age in seconds. The result is therefore 0 for plans executed fewer than 1000 times in total. To get an execution rate, divideexecution_countby the plan age in seconds instead.