Moving to Query Store

  • Comments posted to this topic are about the item Moving to Query Store

  • Hi Steve - sounds interesting, in what way is the store different to using profiler - is it kind of like that but gives you statistics on everything ever?

  • We had quite a significant performance increase on 2017 at one job I had:
    https://tracyboggiano.com/archive/2018/06/query-store-usage-and-adaptive-plan-tuning-usage/

    I've already contacted Bob Ward about this and I'm currently using it on 2016 at my new job and have used to get better performance from certain queries by forcing plans.  I do recommended being on the latest CU there were some fixes in one CU for systems with lots of tempdb usage.  We were early adopters to 2016 and due to the tempdb issues we have to at one point turn Query Data Store off.

  • The Query Store is a warehouse of query plans and stats, essentially capturing activity of plans that lets you analyze how well things perform. You can also force plans from here, which was possible in earlier versions, but not as easy.

    Trace (Profiler) captures various types of metrics, but doesn't store these efficiently. Trace and Extended Events are more like logs of activity.

  • I enabled Query Store on a 3 TB sized database that is bulk loaded nightly. During the day it receives a fairly high volume of queries (ie: 100 per second) at a steady pace between 9am - 6pm from a call center application and also sporadic ad-hoc (sometimes overly complicated) queries from the BI and other data analysts throughout the day and into the evening. We do use SentryOne montitoring, which is great for a lot of things, but this tool is configured to only capture execution plan with a runtime > 10 seconds. For historical usage statistics on high volume sub-second execution plans, like the ones coming from the call center, I turn to Query Store.

    I wouldn't say it's the greatest thing to ever happen to SQL Server, data management views and plan guides were introduced back in 2005, but persisting the dmv data to a more star-schema data model is a big step forward and a foundation for building better query analysis and optimization tools.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Umm, not sure how to approach this. I read the article, but was confused in a few places. Some of this is due to the fact that I really don't know what the SQL Server Query Data Store is, but also part of it is, well I think something got past the editor. For example, the first paragraph ends with:

    We were excited by the chance to actually gather some information on the .

    On the what?

    Then in the fourth paragraph, it starts by saying:

     I don't expect that a lot of improvement at SQLServerCentral from changing this, as our third party forums and much of the internal code is batch SQL, and quite a bit generated on the fly. However, there are some stored procedures, and I might be very wrong.

    I'm sorry, but I'm not following you here. So, you've got some stored procedures and how might that make you wrong? I'm probably being obtuse in my understanding, so please bear with me and help me by clearing up these two things. Thank you.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Ugh, edit error. Capture data on the performance of the database queries.

    Query Store captures metrics on queries that run often. It can determine which plans were used and help you prevent plan regressions, or better understand query processing.

    It's less helpful if all your queries are ad hoc, dynamic SQL.

  • Ah, thank you, Steve! OK, now I'm getting your drift, as to why Query Store is so useful.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Query Store is to query performance metrics what Service Broker is to message queuing; it's just a standardized, optimized, and extensible way of doing something that many of us have been doing for years. It's good at what it does (archiving, aggregating, and visualizing query metrics and plans), and I don't see a need to deploy any of the equivalent in-house stuff myself and others have built in the past that do basically the same thing. What really great about getting everyone on Query Store is that developers can then create SSMS plugins that we all can share.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply