Query Store Usage and Automatic Plan Correction Usage

Recently, there are have been a few articles about the lack of adoption of Query Store. Note the following:

  1. Brent Ozar blogged about it in this article Building SQL ConstantCare®: Why People Aren’t Using Query Store.
  2. Erin Stellato blogged about it in this article Why Aren’t You Using Query Store?
  3. Bob Ward gives us a nice blog post on how it works SQL Server Automatic Tuning in the Clouds…

To add my official input from the article Bob wrote I will provide this chart that shows the performance improvements we are seeing our systems that we are using Automatic Tuning on:

My company was an early adopter of SQL 2016 and we barely noticed any overhead in turning on Query Store.  Our biggest overhead came in the use of tempdb because we process over 20,000 transactions per second on the system causing PATCHLATCH_EX contention in the tempdb. This was resolved in CU5 of 2017 and will not affect your system if you are not processing a high number of transactions per second.

In short, I believe everyone should enable this feature it provides way too much valuable information to not being using in either version of SQL Server 2016 or 2017 to not be using it.

Related Posts

3 thoughts on “Query Store Usage and Automatic Plan Correction Usage

  1. Some reasons for Query Store not being used:
    1. The GUIs are at the database level; not at the instance level. That’s a lot of clicking around to do for any troubleshooting or to see what’s regressed & why.
    2. It appears to behave a little strangely. e.g. I force a plan, but QS then starts using a new plan, even if Automatic Plan Correction isn’t enabled. A bit of meta data along the lines of “QS ignored this forced pan because…” would be useful.
    3. Not a lot of blogging, other than rehashes of very basic “Her’s how to enable it”, which suggests that it’s being ignored across the board.
    4. The various permutations in the GUI can easily lead to “What am I looking at here?” situations.

    Having said all that, I like QS and use it on our instances, but a bit more love from Microsoft to make it shine would be great.

    1. In reference to #2 if a query even contains a space to make it different then it will produce different plans because the queries are different. It’s highly recommended to use stored procedures without dynamic SQL so you get the best aggregation for data. Yes her interface could still use some work. I address that in my presentation that’s it’s annoying that you have to know which database to go to. Behind the scenes it is actually using plan guides hence why they queries have to match exactly. I have a Azure item up for the instance level view if you want to up vote it.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.