As many of you know, Query Store is a wonderful tool for identifying query regressions, which in turn makes it an equally wonderful tool for migrations involving an upgrade to SQL Server. You can toggle between compatibility levels, observe which queries perform worse, and then proceed to troubleshoot any, uh, troublesome queries.

But what if you wanted to use Query Store for a different reason in a migration? What if to see which stored procedures are actually being used, and which are just sitting there unused like that box of commercial toilet paper you panic purchased last month?

What if you set the capture mode to “All”, but then noticed you weren’t actually capturing All of the queries in your database?

Call Me

There is a bit of advice from Erin Stellato and Erik Darling out there regarding capture mode and Query Store, and how you should prayerfully consider setting it to “Auto” instead of “All” because most likely you really don’t want the execution plans for ALL queries. Your database may be afflicted with zillions of “trivial” queries and you probably aren’t ever going to review those.

It’s not bad advice, but there are definitely some circumstances where you might want to capture plans for all queries. For instance, what if your most heavily called queries are deemed “insignificant compile and execution duration” by the “Auto” setting?

You’re Driving Me Crazy

Which brings us back to the scenario we had: using Query Store to track usage of queries over time. Some developers I work with turned on Query Store with capture mode set to “All” prior to a migration in an attempt to check the number of executions of all stored procedures.

What they found, after a few days, was that Query Store wasn’t capturing all of the query executions. Inconceivable!

I took a look at some of the examples and soon noticed the queries being excluded all had one thing in common: they didn’t actually read or write any data. In fact, every operator in their execution plan had a cost estimate of zero. Every. Single. Operator.

For example, we had one procedure that simply called for a formatted result of GETDATE(), while another one only executed a handful of other stored procedures and nothing else.

Why did we have stored procedures like this? Because reasons. But that’s not important right now.

All or Nothing at All

On the one hand, I can see why these queries wouldn’t have their execution plans captured in Query Store. With operations that effectively had no cost they weren’t exactly going to regress in performance. To my knowledge, selecting GETDATE() or executing EXEC are not commands you can tune to improve performance.

But on the other hand, “All” should mean “All”, right?

I couldn’t find any Microsoft documentation regarding this phenomenon, so I asked the Queen of Query Store herself, Erin Stellato. She didn’t know off hand, but promised consult with the Query Store Pentavirate to see if they could offer a response. Sure enough, in a reply the truth was revealed.

Query Store by design does not capture queries which contain only scalar expressions, given that their plans are trivial and not really relevant for performance troubleshooting. For example SELECT 1 is not captured, but SELECT TOP 1 1 is.

So there you have it. The “All” capture setting does not actually result in capturing all queries. You can decide if this is a bug or a feature.