While plan caching is an important performance optimization in the query processing engine, there are cases where it can be harmful. Imagine, for example, a query where there are many different optimal plans as you vary the parameters across the input parameter space. Today, the system will cache whichever plan happens to be optimal for the first few instances of the query shape. Subsequent examples of this query are likely to trigger replanning – or worse, may fail to trigger replanning and end up using the wrong plan. Applications could experience this as unpredictable query latency, since the latency depends on the current state of the plan cache.

In such situations, we could give users the option of bypassing the plan cache for the affected query shape. This could be specified as a new form of query hint specified in application code. Alternatively, this would be a useful extension to the persistent query settings feature that is currently under development as of this writing. This would allow an operator to specify that all instances of a particular query shape should ignore the plan cache (without altering application code).