T-SQL Tuesday #124: Using Query Store (and why you might not be…yet)

This month’s T-SQL Tuesday topic is courtesy of Tracy Boggiano and it’s related to using Query Store!  Tracy asked people to write about their experience adopting Query Store (or not, if you haven’t tried it yet or have had issues). I have so much to share… 😊

I can spend a full day talking about this favorite feature of mine, but will do my best to succinctly summarize the usage patterns I’ve seen with customers.

Successful Adoption

We have several customers that turned Query Store on the moment they upgraded to SQL Server 2016 or higher and were off and running. We have one that uses Enterprise Edition and also uses Automatic Plan Correction, and there are some plans that we force manually as well.  Those customers have parameterized queries and procedure-based workloads.

Delayed Adoption

There are a few customers that upgraded to SQL Server 2016 or higher and had concerns about Query Store. In one case they already had a third-party monitoring tool in place and didn’t think they needed it. Other customers were very hesitant to make changes to the environment, and wanted to do testing with it – after the upgrade – to make sure there were no adverse effects. In all cases, the customers finally implemented Query Store, with success.

Successful Adoption…but not without some struggles

Last fall we had a previous customer reach out for help after they had to wait 45 minutes for a database to come online after a server reboot. The database queries were blocked by QDS_LOADDB waits. There were three things in play here – the first was that they had CAPTURE_MODE set to ALL, and it should be AUTO. Second, they didn’t have trace flag 7752 enabled (the behavior of which is now the default in SQL Server 2019). And the third was that their Query Store was 100GB in size. The workload was fairly ad hoc, so these three things together caused the problem initially described. They implemented the TF, made multiple changes to the settings (set CAPTURE_MODE to AUTO, changed MAX_STORAGE_SIZE_MB to 10GB, decreased CLEANUP_POLICY to 3 days), and then Query Store was usable for them.

Failed Adoption

Lastly, there are a few customers that have been unable to implement Query Store because of their extremely ad hoc workload. For one customer, it was very frustrating because they benefited from plan forcing. At the time, they were running the latest version of SQL Server 2017 but ran into the bug where the transaction log would fill up (fixed in 2017 CU16 and 2016 SP2 CU8). Even after applying this CU and having optimal settings, they were still unable to use Query Store because of their workload.

Query Store is *still* a possibility

For anyone with an ad hoc workload that is at the point of dismissing Query Store, I recommend that you verify you are following best practices, and it might be necessary to look at SQL Server 2019 or Azure SQL Database. There is new option, CUSTOM, for QUERY_CAPTURE_MODE that allow you to control exactly what queries are captured based on execution frequency, compile CPU, or execution CPU. Further, the SQL Server team continues to actively evolve the Query Store feature and address issues as they are found. If this is a feature you want to use, and you are running into a problem, you need to open a User Voice item.  The SQL Server team can only fix the problems and limitations they know about.