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?