Age distribution of plans in cache

Did you ever consider what the age distribution of your plan cache is? Maybe you should. Here are a couple of examples:

You search for expensive queries

Some of you might be using query store and run queries or use a GUI on top of query store. No problems: the queries, plans and run-time statistics for query store are persisted in your database. (There is the aspect of how you configure query store, but that is a different topic.)

Or some of you are using some tracing mechanism like SQL Trace/Profiler or Extended Events to capture your queries and their costs. Fine, you obviously now work on the data captured in this trace.

But how about Googling and finding some neat query on the Internet that gives you your most expensive queries? Perfect! It took you 30 seconds to Google it, and 30 more seconds to copy, paste and run that query. Or you use a GUI, for instance in SSMS, something like:

The thing is that these tools/queries uses the plan cache to find expensive queries.

So what if your oldest plan is 3 only days old? Or 3 hours? Or 3 minutes?

Finding your most expensive queries based on such short “tail” is pretty meaningless and you end up wasting time tuning a set of queries that in the end wasn’t the most important ones, after all.

Or you might have some issue with your workload, generating lots and lots of plans.

Did you say ad-hoc queries? You might just want to be aware of that fact, and see if there is anything you can to do about it! Talk to the developers, consider “forced parameterization” database setting, or “optimize for ad-how workloads” server or database setting. Here is a good blog post by Randolph West and Erik Darling on that topic: https://bornsql.ca/blog/dont-optimize-for-ad-hoc-workloads-as-a-best-practice/.

Finding the oldest plan in cache is easy

Search for the oldest plan in cache using sys.dm_exec_query_stats:

SELECT MIN(creation_time)
FROM sys.dm_exec_query_stats;

To get a histogram you can generate a column with how old in minutes/hours/days each plan is and then just use GROUP BY and COUNT. Below I store the age of each query in a temp table and then you can use whichever of the subsequent queries that suits your age distribution best. You probably want to start with the first one to see age distribution in days. If you find that the plans are only a day or two old, then go by hours, and possibly the third one, by minutes.

DROP TABLE IF EXISTS #t

SELECT
DATEDIFF_BIG(MINUTE, creation_time, GETDATE()) AS minutes_
,DATEDIFF_BIG(HOUR, creation_time, GETDATE()) AS hours_
,DATEDIFF_BIG(DAY, creation_time, GETDATE()) AS days_
INTO #t
FROM sys.dm_exec_query_stats

SELECT days_, COUNT(*) AS plans FROM #t GROUP BY days_ ORDER BY days_
SELECT hours_, COUNT(*) AS plans FROM #t GROUP BY hours_ ORDER BY hours_
SELECT minutes_, COUNT(*) AS plans FROM #t GROUP BY minutes_ ORDER BY minutes_

Here’s an example of the result. This is from my machine where I pretty recently cleared the plan cache, using the last query: