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.

Doggone

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.

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!