Query Plan for which usecounts and creation_time keeps on going up

Question

We have a plan for which the usecounts (from sys.dm_exec_cached_plans) as well as the creation_time (from sys.dm_exec_query_stats) keep on going up.

According to documentation:

Creation_time: Time at which the plan was compiled.

I find creation_time and the description somewhat confusing. The verb "compile" can be interpreted in different ways. Since it seems to keep changing, I assume it does not mean the moment the plan was originally created? That it keeps on changing, does that mean it is each time recompiled, so the creation_time is a last compile time?

Usecounts: Number of times the cache object has been looked up. Not incremented when parameterized queries find a plan in the cache. Can be incremented multiple times when using showplan.

The plan is about a query that is parameterized, still the usecounts keep on going up. What can be the reason for that? If the query has constantly changing hard-coded parameters you would think it would have a different query hash and plan?

I hope someone can help me out here.

Answer 1

You are kind of comparing apples and oranges.

sys.dm_exec_cached_plans looks at the plan_handle as such. There is one single plan handle for the module, and this one only changes if the module is dropped or altered. So its usecounts will keep on growing.

sys.dm_exec_query_stats has a row for every query within the plan handle. The plan for a single query can be replaced, but that will not change the plan handle. There are many possible reasons for statement recompile, including autostats. When a query recompiles, you can expect creation_time to be updated, plan_generation_num being incremented by 1, and execution_count to restart on 1.

As for why the plan is being evicted from the cache, a possible reason is memory pressure. But it could also be that someone runs DBCC FREEPROCCACHE or an sp_configure operation that clears the cache.

Answer 2

One more thing to add to that. Despite the use counts that keep on going up, after a while the plan is removed from the cache and it starts over again (same query hash, but new plan and plan handle). This query is constantly executed in the system, so it doesn't seem to make sense it is removed if it is in constant use. Someone who has an explanation for this?

Answer 3

Hi, ErlandSommarskog

I know about the plan_handle remaing the same, but of course a recompile can change the plan itself, so therefor the change in the creation_time. I was a bit puzzled about the definition of creation_time. With your explanation I understand now that creation_time is about the plan itself, not the plan_handle. Thank you for clarifying. I will further look into why the plan is being evicted from the cache.

Thank you for your reply.