Why aren’t you using Query Store?

Way back in 2016 I wrote a post trying to find out why people were not using Extended Events.  See, I really like Extended Events, and I’d been presenting on the topic for several years, but I kept running into people who wouldn’t even try XE.  There were also a lot of individuals who declared they were “Profiler for Life”.  Ok, but I wanted to understand why.

I find myself asking people the same question now about Query Store. I’ve presented a lot of sessions on the topic, with the primary goal of teaching people how to use Query Store.  As such, most of the people in my sessions are not using Query Store.  As an example, if I have 30 people in a session, typically only 25-30% are running SQL Server 2016 or higher.  And of those 7 to 10 people, maybe 2 to 5 are using Query Store.  Does that mean that only 5%-15% of SQL Server users are using Query Store?  No.  I have no idea how many people who are running SQL Server 2016 or higher are using Query Store.  I’m pretty sure Microsoft doesn’t have an accurate number either, except for Azure SQL Database.  Query Store is turned on for nearly all 2 million user databases on Azure SQL Database.

So the primary purpose of this post is to provide a place (the comments) for people to share why they are not using Query Store.  I’m very interested in hearing this information, so please share!  And please comment even if it’s the same as what someone else has written.

In addition, if you don’t know much about Query Store but want to get started because you’re running SQL Server 2016 or higher (or are looking to upgrade), I’ve included some links below to help you get started.  Some links are for documentation or blog posts, but if you’re not a believer of RTFM 🙂 , there are also links to my Pluralsight courses (requires a subscription) and an introductory session I’ve given (free).  If you have questions not answered in a post or video, let me know this in the comments as well!

Lastly, I’ve had multiple questions related to the performance impact of enabling Query Store.  This is a great question – look for a post next week that provides more detail.  But until then, please let me know why you’re not using Query Store, and thanks for reading!

 

29 thoughts on “Why aren’t you using Query Store?

  1. Hello Erin!

    I think the 17 spinlock types added with Query Store are a bit daunting. Kind of funny, because I think its also a *good thing* the spinlocks adjacent to Query Store are that granular. But at the outset it makes it look like understanding the potential and actual impact of Query Store may be more involved than collecting the “Query Store CPU usage”, “Query Store physical reads”, “Query Store logical reads” and “Query Store logical writes” available in perfmon.

    SELECT LEFT(@@version, PATINDEX(‘% – %’, @@version) – 1) [name]
    UNION ALL
    SELECT [name] FROM sys.dm_os_spinlock_stats
    WHERE [name] LIKE ‘%query|_store%’ ESCAPE ‘|’
    ORDER BY [name];

    Microsoft SQL Server 2017 (RTM-CU6) (KB4101464)
    QUERY_STORE_ASYNC_PERSIST
    QUERY_STORE_ASYNC_QUEUE_TLIST
    QUERY_STORE_CAPTURE_POLICY_INTERVAL
    QUERY_STORE_CAPTURE_POLICY_STATS
    QUERY_STORE_CAPTURE_POLICY_THRESHOLD
    QUERY_STORE_CURRENT_INTERVAL
    QUERY_STORE_HT_CACHE
    QUERY_STORE_LIST
    QUERY_STORE_PLAN_COMP_AGG
    QUERY_STORE_PLAN_LIST
    QUERY_STORE_READ_ONLY_FLAGS
    QUERY_STORE_SELF_AGG
    QUERY_STORE_STMT_COMP_AGG
    SPL_QUERY_STORE_CTX_INITIALIZED
    SPL_QUERY_STORE_EXEC_STATS_AGG
    SPL_QUERY_STORE_EXEC_STATS_READ
    SPL_QUERY_STORE_STATS_COOKIE_CACHE

    1. Hey Lonny,

      So to make sure I understand, you don’t want to enable Query Store because there are new spinlocks in SQL Server specific to the feature?

      The design of Query Store lends itself to having these spinlocks – remember that new data is stored in memory until it is flushed to disk. If you think about the potential volume of activity (frequency of query execution) and having to capture – potentially – the query, the plan, and track the runtime stats, it’s a lot of data that needs to be carefully protected, but with as little impact as possible. So, the additional spinlocks are just the nature of the beast. I don’t know that I’ve heard of any customer that has used this information for troubleshooting, but I would bet Microsoft has as part of R&D, particularly in Azure.

      Erin

  2. Thanks to your training, I am using Query Store across all test databases, and rolling out across production (25% of a thousand or so). One reason I haven’t just flipped the switch for all databases is I notice a heavy uptick in the Query Store perfmon counters for the first 15 min to hour of having QS enabled on a single database. Pretty sure the cause of this is our heavy ad-hoc workload with lots of different plans.

    But for our on-prem customers with a single database on 2016+ I don’t hesitate a second telling them to enable Query Store. Already helped so many times.

    1. Thanks for sharing Ethan! The temporary uptick in counters makes sense initially – when Query Store is initially enabled, it has no queries or plans, so it has to capture all of that. If you have an ad-hoc workload, the load is higher than a SP workload – something I’m working on for a post. But I’m glad to hear you’re rolling it out and that it’s helped!

  3. Any news on the two trace flags you had blogged about last year? We use Query Store on our 2016 databases and use Brent Ozar’s utilities to examine the data.

  4. Hi Erin!

    I’m not using Query Store at the moment because we’re not upgrading to a release that supports it until later this year (hopefully this summer).

    Once we do upgrade, we *may* enable it for a sampling of our databases; we’ve so many databases on our production instance that I’m afraid it won’t be worth the overhead to enable it for all. Hopefully we’ll be able to find a handful of databases that we can use it to get some additional data about our queries that we aren’t getting from our monitoring system, but using it to force plans or enable automatic query tuning (which isn’t a factor as we won’t be going to 2017) probably won’t be practical on a wide scale.

  5. I am not using Query store because I cant use the top resource consuming queries report. When I select my top 5 slowest queries by duration in a chart, I cant see the query text

    1. Do you have the same behavior with a different version of Management Studio, or does this happen against EVERY user database with Query Store enabled, from EVERY install of Management Studio?

      Erin

  6. Hi Erin,

    You mentioned you were working on an article on the performance impacts of enabling Query store and have an example of one for you. We enabled the query store on all the databases on a Smaller SQL Instance and after a period of time we started to get Resource Semaphore waits. We found that the MEMORYCLERK_QUERYDISKSTORE_HASHMAP had become the largest consumer of memory greater than the MEMORYCLERK_SQLBUFFERPOOL value for that instance. We disabled the Query store on several databases and were able to increase memory for that instance. Max Server Memory was 16 GB and we increased to 24 GB. We were seeing 6-7 GB of memory taken up by MEMORYCLERK_QUERYDISKSTORE_HASHMAP prior to us disabling the query store in several databases. I tried looking for more information on the MEMORYCLERK_QUERYDISKSTORE_HASHMAP wait and only found a reference to it in a Bob Ward presentation. https://www.sqlsaturday.com/SessionDownload.aspx?suid=13796 The SQL Instance has a large number of adhoc queries in the cache and we have optimize for adhoc queries enabled. I suspect that the adhoc queries are the cause of the large number of hashes but wanted to see if you have seen similar issues and if there is a way to gauge which instances are going to need more memory allocated when enabling Query Store. Thank you for all of your informative posts! David S.

    1. Hi David-

      This is one of the examples I’ll discuss in the post, thank you for sharing. Hopefully my write up will explain what you’re seeing and why, thank you for sharing!!

      Erin

      1. In addition to the situation described above, I see QDS_STMT and RESOURCE_SEMAPHORE_QUERY_COMPILE waits at the same time.

      2. Hi Erin, Did you ever get a chance to answer David S question on why the MEMORYCLERK_QUERYDISKSTORE_HASHMAP growing so big, I know you mentioned you would address it in a blog the following week but I just can’t seem to find it. Many thanks.

        Richard

        1. Richard-

          I may not have written it in a post, I apologize, but that memory_clerk can grow very large with a highly adhoc workload. Query Store has to track every individual query that executes, and it does via a unique hash that it creates for a query. The more adhoc (truly unique) queries you have, the larger that hashmap.

          Erin

          1. Erin,
            Do you know maybe if it is possible to clear this hashmap? I have disabled and cleared QS on all databases and this memoryclerk is using still 2GB of RAM.
            Server is a production one and I don’t want to perform SQL Server instance restart.

          2. Hi Erin!
            Just to see if I undertood.
            So, the MEMORYCLERK_QUERYDISKSTORE_HASHMAP is relation with adhoc and the Query Store on?

            Because, if I have a highly adhoc workload in an instace, but not turn on the Query Store, the clerk that SQL Server will use is the CACHESTORE_SQLCP

            I’m in the rigth way?

            I question, because I have this cenário….
            The top 3 Memory Clerk Type:

            MEMORYCLERK_SQLBUFFERPOOL -> 80357.90 Memory Usage (MB
            CACHESTORE_SQLCP -> 6689.99 Memory Usage (MB)
            MEMORYCLERK_QUERYDISKSTORE_HASHMAP -> 4546.10 Memory Usage (MB)

            This is in environment that have 102.000 MB in server max memory. The server has a total of 128.000(MB)

            Thaks a lot!

  7. Erin,

    I’ve explained the Query Store to wary peers and customers this way.

    The name says it all, Query Store. It’s not the Ad-Hoc Query Store. In this case, special orders do upset us…at least in regards to standard, repeatable, “vanilla” queries. Unique queries require full overhead. Repeatable ones? Not so much. You want a Platinum White Pearl Honda Accord? They have them. You want a Nouvelle Blue Pearl Acura NSX? You might need to wait a while…and find a 2nd job.

    Know your environment. Activate appropriately. Always MONITOR! You’ll be driving that Accord for a long time.

    David

    1. Hi David-

      I’m a little bit confused. Query Store DOES capture ad hoc queries by default. You can change the QUERY_CAPTURE_MODE from ALL to AUTO, and then it won’t capture queries which execute less than 3 times or have an insignificant compile or execution CPU time.

      Beyond that, I’m not a car person so I don’t quite understand the analogy…and I don’t quite understand why (if?) you don’t use Query Store.

      Thanks,

      Erin

  8. Sorry about the car analogy. My point was that the Accord is the repeatable and oftened used search, the Acura is rare…and expensive.

    I’m all for using the query store. I was trying to make the point that peers and management are skeptical of change. The bigger the shop, the more “change” is avoided…or at least strongly frowned upon.

  9. declare
    @sql nvarchar(max)
    set @sql =

    print db_name()+” setting up query store”
    ALTER DATABASE ‘+QUOTENAME(db_name())+’
    SET QUERY_STORE = ON
    (OPERATION_MODE = READ_ONLY);
    ALTER DATABASE ‘+QUOTENAME(db_name())+’
    SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 14336); — default is 100
    ALTER DATABASE ‘+QUOTENAME(db_name())+’
    SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);
    ALTER DATABASE ‘+QUOTENAME(db_name())+’
    SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 60); — default is 900 (lower is better when a system experiences memory pressure, and we see memory pressure in spades)
    ALTER DATABASE ‘+QUOTENAME(db_name())+’
    SET QUERY_STORE (MAX_PLANS_PER_QUERY = 100); — default is 200
    ALTER DATABASE ‘+QUOTENAME(db_name())+’
    SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60); — default is 60 (lower values will increase the stored query size)
    ALTER DATABASE ‘+QUOTENAME(db_name())+’
    SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 1)); — default is 30
    ALTER DATABASE ‘+QUOTENAME(db_name())+’
    SET QUERY_STORE (WAIT_STATS_CAPTURE_MODE = OFF); — default is ON
    ALTER DATABASE ‘+QUOTENAME(db_name())+’
    SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);
    ALTER DATABASE ‘+QUOTENAME(db_name())+’
    SET QUERY_STORE CLEAR;
    ALTER DATABASE ‘+QUOTENAME(db_name())+’
    SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE);

    exec(@sql)
    –print @sql

    — every few days:
    SELECT actual_state_desc, readonly_reason, max_storage_size_mb ,
    100.*current_storage_size_mb/max_storage_size_mb as [pcnt_full]
    FROM sys.database_query_store_options;

    actual_state_desc readonly_reason max_storage_size_mb pcnt_full
    —————– ————— ——————– ——————-
    READ_ONLY 65536 14336 209.263392857142857

    — Both Nhibernate and Entity Framework are heavily used (I work in a development shop with No SQL Developers).

    — 4 TB of user databases (mainly OLTP), 12 physical cores hyperthreaded, 192 GB RAM (our budget currently does not allow these instances to be upsized). SQL Server 2017 CU17

    select cacheobjtype , sum(cast(size_in_bytes as bigint))/1048576 ‘size (MB)’
    from sys.dm_exec_cached_plans
    group by cacheobjtype
    order by sum(cast(size_in_bytes as bigint))/1048576 desc

    cacheobjtype size (MB)
    —————— ———
    Compiled Plan 8646
    Parse Tree 92
    Compiled Plan Stub 7
    Extended Proc 0

    — Cache and PLE get flushed multiple times per day.
    — Some of our cheapest-to-compile plans (that frequently get flushed out of cache) suffer from a parameter sniffing issue that has significant user impact and are usually rooted in skewed distributions within an EAV-designed table (our developers have known of that design problem for over a year).
    — sp_query_store_remove_query and sp_query_store_remove_plan are too slow for the number of query store entries out systems have
    — Running ALTER DATABASE … SET QUERY_STORE CLEAR flushes the SQL Server’s cache.
    — SET QUERY_STORE CLEAR removes forced plans.
    — I will have query store enabled, but I will be clearing it out every night.
    — Because query store can flip to READ_ONLY during daytime prod hours, I cannot rely on Adaptive Query Processing to save a given day.

    — I will use Force Plan Guides for immediate relief (if/when Query Store is not READ_ONLY). But because forced plans are cleared by SET QUERY_STORE CLEAR (which I really do not want to happen), I also manually script out its forced plans as parameters that I pass to sp_create_plan_guide. The need to save plan guides would be easier, if the parameters for sp_create_plan_guide were added to sys.query_store_plan and (better yet) Query Store had code that adds forced plans to sys.plan_guides.

    — Overall, I like Query Store, but it does not seem to be a good fit for our systems. It is more like ‘meh’ – maybe it will get better in a post-SQL Server 2019 version.

    1. Hi-

      You have a lot of statements in your comment, and I will do my best to address all of them.

      You included the output from a query which I’m guessing you ran against your production database, showing that Query Store was in a READ_ONLY state. Based on other information you provided, my assumption is that because you are using Nhibernate and Entity Framework you have an extremely ad hoc workload. It is not uncommon for ad hoc workloads to seem as though they don’t perform well with Query Store enabled, purely due to the nature of the workload (very high number of single-use queries and plans) and the way Query Store works. I talk about it here:
      https://www.sqlskills.com/blogs/erin/query-store-performance-overhead/

      I also see that your Query Store is over 10GB in size. I generally recommend keeping QS 10GB or less in size. My guess is that you exceed what you have set for the max because, again, of the nature of the workload.
      https://www.sqlskills.com/blogs/erin/query-store-best-practices/

      If you are able to parameterize a few of the ad hoc queries which execute most frequently, or use plan guides to create a parameterized version, it can help performance. In addition, the new options in SQL 2019 are designed to help with ad hoc workloads.
      https://www.sqlskills.com/blogs/erin/query-store-in-sql-server-2019-ctp-3-0/

      Hope that helps!

      Erin

  10. Hi, I find that a major limitation of the Query Store is that it does not provide session-context information: SessionID, User, ProgramName, HostName etc.
    As a result, it is difficult for a DBA to circle back to a dev/app team and communicate to them any findings on slow-performing queries and other information collected from the Query Store. I find that I have to combine the Query-Store info with session-context info from 3rd-party monitoring tools (eg. SolarWinds DPA) to get a full picture, and this type of analysis is laborious and time consuming.

    Thanks,
    Marios

Leave a Reply

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

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.