I have a server running SQL Server Enterprise 2016 SP2 and I noticed that every minute several hundred queryplans get invalidated.
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?
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