Why are the majority of cached plans missing from sys.dm_exec_query_stats?

I'm trying to understand some execution plan caching metadata on a SQL Server 2016 SP3 system, and I can't reconcile what I'm seeing with the docs.

The docs for sys.dm_exec_cached_plans say it contains:

a row for each query plan that is cached by SQL Server for faster query execution.

On the system I'm observing, this view has 41,283 rows in it right now. The vast majority of these (37,594 rows) are cacheobjtype = "Compiled Plan" and objtype = "Adhoc".

The docs for sys.dm_exec_query_stats say it contains:

one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.

I would expect there to be at least 37,594 rows in this view (one per cached plan, potentially more if some of the cached plans have multiple statements). However, this view has 6,867 rows total.

This discrepancy is so great that I must assume that I'm misunderstanding what is supposed to be in these views.

Can someone help me understand why there are so few rows in sys.dm_exec_query_stats compared to sys.dm_exec_cached_plans?

I tried inner joining the tables together on plan_handle, and the only matches were 1:1 - in other words, there are tens of thousands of cached plans with no "query stats" rows.

I also thought the difference might be explained by many rows being in sys.dm_exec_procedure_stats or sys.dm_exec_trigger_stats, but that was not the case (93 and 2 rows, respectively).

For anyone curious about the "why" of this question, I'm trying to see how old the various plans in the cache are, and I'm not sure of a way to do that beyond joining to sys.dm_exec_query_stats and checking creation_time.

Here are the queries I used to get the numbers referenced above:

```
-- total cached plans
SELECT COUNT_BIG(*) AS total_cached_plans
FROM sys.dm_exec_cached_plans decp

-- totals by type
SELECT decp.cacheobjtype, decp.objtype, COUNT_BIG

Many of your queries qualify for simple parameterization.

SQL Server creates a parameterized version of the statement and caches that as a prepared plan.

The ad hoc plans you see are just shells pointing to the parameterized version.

Entries in sys.dm_exec_query_stats are only associated with the prepared plans.

For more background see my article Simple Parameterization and Trivial Plans.

StackExchange Database Administrators Q#309983, answer score: 3