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.

Related Posts

Finding Memory-Rich Queries

Matthew McGiffen wants to find the queries which demand the largest memory grants: I had a server that looked like it had been suffering from memory contention. I wanted to see what queries were being run that had high memory requirements. The problem was that it wasn’t happening right now – I needed to be […]

Read More

Query Store Required Permissions

Andreas Wolter takes us through exactly which permissions are required for Query Store to work: Typically, there are 3 aspects to the work with the Query Store, which can be reflected in roles: 1) Configuration – turning Query Store on and off, clearing the contents, flushing its contents to disk and changing its settings.2) Viewing the […]

Read More

Categories

March 2016
MTWTFSS
« Feb Apr »
 123456
78910111213
14151617181920
21222324252627
28293031