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