erikdarlingdata
sp_QuickieStore: Add a way to check for queries that have recently changed in performance
Is your feature request related to a problem? Please describe. This is absolutely something that I would use. I frequently use Query Store to check how my instance/database/query has changed since I made a change. This kind of A-B testing is even one of the suggested use cases for Query Store and there are two examples in the official docs and one dashboard in SSMS for it. Microsoft clearly want you to use Query Store for this and there is no greater pleasure than turning this data in to an argument-winning slideshow. Given that Erik is a consultant himself, I'm sure that he knows this very well... Along with the copy and pasting to Excel that it brings.
To my knowledge, sp_QuickieStore does not support this at all. If I want to use sp_QuickieStore to find queries that have recently changed in performance, I have to conduct awful hack like this:
- Make a note of when I made the change that I want data about.
- Search
sys.query_store_planfor any queries that have at least two plans and have one that compiled after I made that change. Be super careful with what time I'm using forinitial_compile_start_timebecause it doesn't handle UTC as gracefully assp_QuickieStore. - Use
STRING_AGGto turn thequery_ids in to a comma separated list. - Call
sp_QuickieStorewith mySTRING_AGGlist as the@include_query_idsparameter and@start_dateat the earliest point in time that I want to compare my new data against.
Describe the solution you'd like
Somehow, make sp_QuickieStore able to find queries that have changed in performance since a particular point in time. If I can hack it in and Microsoft can do it from the Query Store DMVs, then there must be a way to build in to to sp_QuickieStore.
Describe alternatives you've considered Continue using my hacks, but beg for them to be added to the examples file.
Are you ready to build the code for the feature?
Yes... But it wouldn't be quick. I think I'd have to properly go line-by-line through sp_QuickieStore and really learn how it works before I can develop this one.
IMPORTANT: If you're going to contribute code, please read the contributing guide first. https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md Absolutely yes.
Just for the record, I've begun work on this recently. The parts that I thought about ahead of time have been much easier than expected. The parts that I never considered have been very hard.
@erikdarlingdata Do you happen to have a link to a publicly available small database that has a very busy query store history? I think that I have this feature working, but throwing nonsense queries at StackOverflow2010 isn't a great way to test it.
