Critical query plans removed from sys.dm_exec_query_stats while trivial items are kept Running SQL Server Express 2019.
Have a complicated stored procedure containing 11 sql statements (when selected from sys.dm_exec_query_stats by plan_handle).
9 of those statements are simple variable setting based on passed-in parameters, the last 2 statements (IF/ELSE) do all the work and return data (e.g. query_hash 0x111... and 0x222...).
There's quite a variability in data volume based on input parameters, so I &quot;prime&quot; the stored procedure with optimal values to ensure quick execution.
I've noticed that every few days (sometimes more often), my last 2 statements (query_hash 0x111... and 0x222...) get dumped from sys.dm_exec_query_stats and then re-generated based on whatever parameters come from the users on the next stored procedure call.
I know this by looking at creation_time column, and it's reflecting the time of &quot;priming&quot; for first 9 queries, and a later time for query_hash 0x111... and 0x222... .
From what I understand, cached plans get cleared under memory pressure, and less used plans get cleared first. My queries are being used a lot, and I have a lot of other cached plans with 1 execution that stick around for a while.
Question: why are my most used queries get dumped form cache and how do I stop it from happening?
There are a bunch of different reasons why a statement might be recompiled, including both performance and correctness-related reasons.
For instance, an index used by the query may have been dropped, in which case the plan must be recompiled because it genuinely can't be run in that state.
Or statistics may have been updated enough that an automatic recompilation occurs.
Depending on the load on your server, and how frequently this statement really gets recompiled, you might be able to capture the actual reason using this extended events session:
CREATE EVENT SESSION [recompiles] ON SERVER 
ADD EVENT sqlserver.sql_statement_recompile
(
    WHERE ([sqlserver].[query_hash]=(111))
)
ADD TARGET package0.event_file (SET filename=N'recompiles')
WITH (STARTUP_STATE=OFF)

This will fire for every recompile on the system, but will only store the ones with a query hash that matches what you put in the filter.
To be clear: don't start this thing up and then leave for the weekend.  Try it on a test system, try it briefly on production and make sure it's not too disruptive, etc.  Don't take down your server just to track this down 
In case it's helpful, the XE defines all of these statement recompile reasons on SQL Server 2019:
SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = N'statement_recompile_cause';





map_value




Schema changed


Statistics changed


Deferred compile


Set option change


Temp table changed


Remote rowset changed


For browse permissions changed


Query notification environment changed


PartitionView changed


Cursor options changed


Option (recompile) requested


Parameterized plan flushed


Test plan linearization


Plan affecting database version changed


Query Store plan forcing policy changed


Query Store plan forcing failed


Query Store missing the plan


Interleaved execution required recompilation


Not a recompile


Multi-plan statement required compilation of alternative query plan