Press "Enter" to skip to content

Optimize For Ad Hoc With Querystore

Grant Fritchey investigates the combination of using Query Store and turning on Optimize For Ad Hoc Workloads:

In short, the plan is stored in the query store, even though the plan isn’t stored in cache. Now, this has implications. I’m not saying they’re good and I’m not saying they’re bad, but there are implications. If you’re in a situation where you need to use Optimize For Ad Hoc to help manage your cache, now, you’re going to possibly see negative impacts on your Query Store since it’s going to capture all the plans that you avoided. There are mechanisms for managing Query Store behavior.

I’d consider this correct behavior.  I want to be able to see those one-off query plans.  A quick note on Query Store, though:  it chews up a lot of disk space in a busy environment, so if you’re planning on holding query store entries for a while, keep plenty of disk space available.