Critical query plans removed from sys.dm_exec_query_stats while trivial items are kept [duplicate]

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 “prime” 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 “priming” 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?

I “prime” the stored procedure with optimal values

You can do better than that.

Instead turn on the Query Store and force the plan you want.

Or use a query hint like OPTION (RECOMPILE) or OPTIMIZE FOR.

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';

Your SP has recompiled – could be a schema change but most likely you reached a row change threshold & triggered an auto statistics update. Could be a set option see link

https://www.red-gate.com/hub/product-learning/sql-prompt/changing-set-options-in-a-procedure-or-trigger-leads-to-recompilation-pe012