Sql-server – Why get queryplans recompiled every minute

execution-planplan-cachesql-server-2016

I have a server running SQL Server Enterprise 2016 SP2 and I noticed that every minute several hundred queryplans get invalidated.
I run sp_BlitzCache @expertmode=1 to check the 'Created At'-column to see when the plan is generated.
Every minute about 500 plans are re-created and this continues the entire day and it are always different plans that get recompiled.

When I use the DMV sys.dm_db_index_usage_stats to check the user_updates I see that there happen between 10.000 and 1.000.000 updates on the indexes.
Some tables have more than 10.000.000 records but most of them have between 100.000 and 2.000.000 records.

As far as I know a queryplan gets invalidated in following circumstances:

  • Modifications made to table/view definition by ALTER TABLE/ALTER VIEW
  • Changes made to any indexes used by execution plan
  • Updates to the statistics used by the execution plan (manual or automatically)
  • Dropping an index used by queryplan
  • sp_recompile / with recompile
  • Large numbers of changes to the table
  • Adding/dropping a trigger on a table used by the execution plan
  • Memory pressure

All the queries use KEEP PLAN and KEEPFIXED PLAN, so I think automatic or manual update of the statistics will not invalidate the plan. This is a third-party application so I can not alter the queries.
When I run UPDATE STATISTICS [TableTest] WITH FULLSCAN I don't see a recompilation of the queries that use that table.

Next I used a script by Jonathan Kehayias to check if there was memory pressure but the script only returned RESOURCE_MEMPHYSICAL_HIGH so I do not think this is the problem.

No modification to the table definition, indexes happened nor was the query executed with sp_recompile / with recompile

Is there something else that can cause invalidation of a queryplan?

Update:
After some more research I think the recompiles are triggered by memory pressure on the plancache. This article gives a lot of information about the plan cache internals. This is about SQL Server 2005 but hopefully this still applies to SQL Server 2016.
According to this article you can calculate the pressure limit based on the total amount of visible target memory.

My research advances slow because it is hard to find info about pressure on the plan cache.
I'll keep you posted on the progress

Best Answer

After some extended research I found the cause of my problem. So to calculate the cache pressure limit SQL Server uses following formula:

75% of visible target memory from 0-4GB + 10% of visible target memory from 4GB-64GB + 5% of visible target memory >64GB

Since SQL Server 2012 and above, memory pressure is triggered when a cache store grows to 62.5% of the plan cache pressure limit. When this happens, plans are removed from the cache using an algoritm called eviction policy, which is based on plan cost.
So at certain moments I see a drop in the size of my plan cache and this scenario happens several times in a day, causing every time the compilation of new plans.

Related Question