In environments where complex queries can get bad plans due to parameter sniffing, it would help to say that all queries with an estimated cost over X should be recompiled every time they run.
For example, in environments where most of my workload is small OLTP queries, I’m fine with caching queries that cost under, say, 500, but above 500, I don’t care how good we think the plan is – it’s worth recompiling because those high-cost reports don’t run often, and I need them to get accurate plans for their own parameters every time they run.
This would help environments with parameter-sensitive queries where Adaptive Memory Grants backfire (like reports sometimes use small date ranges and sometimes years of data.)
I would love this feature. If you would too, cast an upvote.