As a SQL Server enthusiast, I often find inspiration for new blog ideas from the questions and challenges posed by my readers. Recently, one of my clients asked me if there was a way to identify the oldest query plans stored in the cache for their server. In this article, I will share with you a simple script that can help you achieve this.

To begin, let’s take a look at the script:

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

This script retrieves the oldest 10 query plans from the cache and displays relevant information about each query, including the query name, execution count, maximum elapsed time, average elapsed time, creation time, frequency per second, and the query plan itself.

By ordering the results by the creation time, you can easily identify the oldest query plans stored in the cache. This information can be valuable during performance tuning engagements, as it allows you to assess the effectiveness of the cache in retaining execution plans.

In a recent engagement, we encountered a situation where the server’s cache was recycling quickly, despite not having been restarted for a while. By utilizing the above query, we were able to analyze the recycling pattern and identify the culprit responsible for the cache flush.

I encourage you to try out this script on your own server and see what insights you can gain from it. Feel free to share your thoughts and experiences with me.

Remember, understanding the behavior of the query plan cache can greatly contribute to optimizing the performance of your SQL Server environment. Stay tuned for more SQL Server tips and tricks in future blog posts!