Query Store And Cruel Defaults

Gosh Darn

Now, look, it’s no secret that I think Query Store is cool. Heck, I went and wrote sp_BlitzQueryStore so you can examine it similarly to how sp_BlitzCache looks at the plan cache.

For a variety of reasons, though, we run into people who can’t or won’t turn it on, or who had to turn it off.

From the can’t and won’t camp, we have people worry about size, PII (especially in the age of GDPR), if it might cause them to fail an audit, etc.

From the had to turn it off crew, we often hear that CPU went through the roof, or that the server got measurably slower.


Now, this won’t help with the PII/GDPR fears, but there is a setting that can reduce the resource consumption of having Query Store enabled.

And, for some reason, it’s not the default.

Death of Autotune

When you turn on Query Store, Capture Mode is set to All. That means you’re gonna collect everything, no matter how inconsequential.

If you change that setting to Auto, you can filter out some of that junk. From some documentation:

Auto – Infrequent queries and queries with insignificant compile and execution duration are ignored. Thresholds for execution count, compile and runtime duration are internally determined.

Cheese and Crackers

Now, look, any monitoring method has some overhead.

Your favorite monitoring tool, the monitoring tool your company bought instead, Profiler, XE, any script you copy and paste from the internet and hit F5 with glee on.

All of’em.

Every last one.

The thing is, many of them (even the scripts we write) make some effort to reduce that overhead. Query Store can too, just not by default. Which I find completely baffling, for about a million reasons.

If you’ve tried Query Store in the past, and found there was a bit too much overhead, give it another try with the Auto setting. You might find it brings things down to an acceptable level.

Thanks for reading!

Previous Post
Sniffed Nulls and Magic Numbers
Next Post
Do Functions Stop You From Using Indexes?

4 Comments. Leave new

  • For me Auto capture mode does not work properly. On the few productions I set it to I was not able to generate any report out of Top consuming queries nor run my own similar scripts. It’s just stuck without query finish waiting for hours. I was trying to solve it with Microsoft but we didn’t came to reason or solution. Only “workaround” was to left capture mode set to All 🙁 I wonder if anybody experience the same problem?

  • […] to set the Query Capture Mode to Auto, especially if you have an ad-hoc workload. There’s a great post by Erik Darling on BrentOzar.com about this setting. In summary, I recommend Auto to control the speed at which the […]

  • Tim Konecky
    June 16, 2021 2:21 pm

    Quick and dirty dbatools.io way to know which Db’s have this default turned on.

    $SqlInstance = ‘YourInstanceName’

    Get-DbaDbQueryStoreOption -SqlInstance $SqlInstance | Select-Object -Property ComputerName, Database, QueryCaptureMode | Sort-Object {$_.Database}


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.