SQL SERVER – Finding The Oldest Query Plan From Cache

Often people ask where do I come up with a new blog idea everyday. Well, answer is that I am not the one who comes up with new idea, it is all of you who inspire everyday with a new idea. Recently one of the healtch check client Comprehensive Database Performance Health Check asked me if there is anyway can see the oldest query plan from the cache for his server.

Honestly, this is very easy process. Run the following script and it will give you all the necessary information about the query along with the query plan.

SELECT TOP 10
t.TEXT 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

As you can notice that the query is ordered by the creation_time. Above query will list the oldest 10 query plan from the cache on the screen. I personally use this query during my performance tuning engagement as it helps me to understand how far cache is capable to hold the execution plan.

In one of the recent engagement, we had noticed even though the server was not restarted for a while their cache was recycling very quickly. We used above query to determine the recycling pattern and found the culprit which was actually doing cache flush.

Try the query out for your server and let me know what you think of it.

Reference: Pinal Dave (https://blog.sqlauthority.com)