All we need is an easy explanation of the problem, so here it is.
Running SQL Server Express 2019.
Have a complicated stored procedure containing 11 sql statements (when selected from
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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';
|Set option change|
|Temp table changed|
|Remote rowset changed|
|For browse permissions changed|
|Query notification environment 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|
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂