Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_QuickieStore: Add a way to check for queries that have recently changed in performance #448

Closed
ReeceGoding opened this issue Jun 25, 2024 · 4 comments
Assignees
Labels
enhancement New feature or request sp_QuickieStore For the loving of Query Store

Comments

@ReeceGoding
Copy link
Contributor

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:

  1. Make a note of when I made the change that I want data about.
  2. Search sys.query_store_plan for 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 for initial_compile_start_time because it doesn't handle UTC as gracefully as sp_QuickieStore.
  3. Use STRING_AGG to turn the query_ids in to a comma separated list.
  4. Call sp_QuickieStore with my STRING_AGG list as the @include_query_ids parameter and @start_date at 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.

@ReeceGoding
Copy link
Contributor Author

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.

@ReeceGoding
Copy link
Contributor Author

ReeceGoding commented Sep 8, 2024

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

@erikdarlingdata
Copy link
Owner

@ReeceGoding no, sorry.

@erikdarlingdata
Copy link
Owner

Closed by #486

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request sp_QuickieStore For the loving of Query Store
Projects
None yet
Development

No branches or pull requests

2 participants