Introducing sp_QuickieStore: Find Your Worst Queries In SQL Server’s Query Store

Posted on by Erik Darling

Querying Query Store Sucks

If you’ve ever tried to write a query to find stuff in Query Store and quickly gotten frustrated, you’re not alone.

Whomever designed that schema hates you. It’s probably the same person who designed Extended Events.

I know, there’s a GUI. The stuff you can find in there is definitely useful, but I often find myself wishing it did a little bit more. Asking for additional features often feels like porridge-begging, and even if something new did get added eventually, it wouldn’t help you in the moment.

With all that in mind, I wrote sp_QuickieStore to try to take some of that pain away. As of this post, it’s V1. There will likely be changes and fixes and all that good stuff, but right now it’s good enough for an introduction.

You can get it here.

What You Can Do

Out of the box, you get a lot of choices about what you want to get and how you want to get it.

You can choose:

  • Which metric you want to focus on:
    • CPU, duration, reads, writes, memory, tempdb, executions
  • How many queries you want to get back
  • The start and end dates of your search
  • Minimum number of executions
  • Minimum duration
  • A specific stored procedure
  • Plan IDs to include or ignore
  • Query IDs to include or ignore
  • Query text to search for
  • Additional query details
    • Compilation metrics, wait stats (2017+), plan cache data, query store options
  • If you want your numbers formatted

Where You Can Do It

sp_QuickieStore is compatible with SQL Server 2016 SP2+, 2017 CU3+, any version of 2019, and probably Azure SQL DB. I designed it to work there, but long term who knows what changes will occur that make support not possible. No promises.

It’ll take advantage of new columns added to SQL Server 2017 and 2019, and the wait stats tracking added in SQL Server 2017.

Testing on my end is done on case-sensitive instances from 2016 up. If you find bugs, please report them on GitHub. I do not offer private email support.

GitHub is also where you can go to ask for features, with the usual caveats that open source is not free development or consulting.

Thanks for understanding, and all that.

Getting Started

sp_QuickieStore has a bunch of documentation built in to the procedure, and there are a number of example calls in the repo.

Now, I know. It’s called sp_QuickieStore, so you expect it to be fast. I’ve done what I can to make things as efficient as possible, but gosh darn it all, sometimes Query Store is just gonna be Query Store.

SQL Server Query Plan
enema

It would be nice if the maintainer of a database that promises high performance maybe put some useful indexes on their views to analyze performance so the queries that touch those views to analyze performance run quickly.

I know, I know. Take off your Crazy Pants, Erik.

Anyway, I’ll have some more posts coming this week to fill in details.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts

19 thoughts on “Introducing sp_QuickieStore: Find Your Worst Queries In SQL Server’s Query Store”

  2. Hey Erik, awesome stuff! Do you know of anyone that makes an equivalent to these scripts for Oracle systems?

    2. For root & branch investigations into Oracle, check out Jonathan Lewis’s site.

      I guess that plans will be of most interest: https://jonathanlewis.wordpress.com/explain-plan

      Getting up-to-speed will be… involved.

      In “real life”, it’s common (and, naturally, hideously expensive) to use Oracle Enterprise Monitor. It’s as GUI as can be and (last time I used it) useful.

  3. Awesome, tomorrow I will try on my personal server !
    Thank you very much!!!

      1. I am enthusiast, his proc has a perfect export into excel, seems not a lot, but to me (european, with commas instead of dots in values) it’s a lot.
        Futhermore, the export of plans in excel is perfect, in sp_ etc from Brent, the exporting in excel fails, in this one is ok
        Maybe I can put it on a table with different times filtered to see if there is a positive or negative delta in the mean time of execution of a query , seems to you a good or a trashy idea?

  4. Pingback: sp_QuickieStore in Action – Curated SQL

  5. Hey Erik,
    This came at a really good time for me as I’m trying to make sense of the info in query store, so thanks a lot its really appreciated. I’ve been running through the various examples, and spotted that two variable names had changed in the 3rd and 4th example –
    @plan_id is now @include_plan_ids and @query_id is @include_query_ids.

    (I realise that if I were github savvy I could probably take some sort of action, but I’m not.)

    Still trying to make sense of things, but am def liking sp_QuickieStore

  7. It’s quick! This is the first I’ve found that returns results back for my problem QS db.
    I have a database that generates upward of 2GB of QueryStore per day. (excessive amount of adhoc queries) GUI won’t load, and most queries I run against the query_store tables I end up killing after a few minutes of spinning. (including sp_BlitzQueryStore) Your script is returning results after a few sec. Magic.

      1. with @expert_mode = 1 runs in 6 seconds, and I have almost this situation , it’s great!
        Sorry for the lack of adjectives 🙂

          1. In addition I can say that on the query store interface the wait stats go often in timeout (in my system), you can contact Pedro Lopez for an official addition maybe 🙂

Comments are closed.