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 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.
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!
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.
- How To Find Poorly Performing SQL Server Queries To Tune Using Query Store
- SQL Server Community Tools: The Wrap Up And Combined Link
- SQL Server Community Tools: How To Dig Deeper With Expert Mode With sp_QuickieStore
- SQL Server Community Tools: Formatting sp_QuickieStore Output So It’s Easier To Understand