I cannot find documentation anywhere on what would trigger this recompilation reason. We are investigating a sudden query performance drop and the only thing that I can think of is that the plan got recompiled for the parameterized query when executed against a small dataset which resulted in messing with row estimates. We noticed that when this process was running (after it started taking hours instead of seconds) it was hitting tempdb pretty hard. Statistics on consumed tables were not changed and the only other reason that makes sense in the list of recompilation reasons is #12 "Parameterized plan flushed".
The process in question was calling a view and filtering on a single INT column. This was done through Entity Framework. There is only 1 Entity Key on the class and it is the PK of the main table in the view. All records are unique.
I am curious if anyone can point me to any documentation out there that explains why a plan might be recompiled due to "Parameterized plan flushed".
Best Answer
Query Plans are flushed from the cache for several reasons, including being aged out, being flushed due to memory pressure, flushed due to user action (DBCC FREEPROCCACHE etc), flushed due to restart and flushed due to explicit recompilation (OPTION (RECOMPILE or sp_recompile).
If you can see no evidence of forced recompilation or a manual flush, then it is most likely the plan was aged out or it was flushed due to memory pressure. From Docs: Plan Cache Internals:
Take a look at this StackExchange answer which provides heaps of great links and information on plan reuse and caching. You'll have to put in place monitoring to catch the exact cause if this reoccurs, but you should also look at leveraging Query Store which will allow you to force a known good plan for this query and track regressions across your database as this may be affecting other queries as well.