Introducing sp_QuickieStore: Find Your Worst Queries In Query Store

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+. 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.

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!



12 thoughts on “Introducing sp_QuickieStore: Find Your Worst Queries In Query Store

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

    1. 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.

      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?

  2. 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

Leave a Reply

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