sp_QuickieStore: added expert mode T-SQL for plan forcing and hinting#684
Merged
erikdarlingdata merged 3 commits intoerikdarlingdata:devfrom Feb 28, 2026
Conversation
Appears in the same output table as the one detailing hints, so it is quite naturally placed.
Owner
|
Thanks for the contribution, Reece! This is a really clean, well-thought-out addition. The conditional toggle between force/unforce based on current plan state is a nice touch, and having the |
Merged
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Closes #678. In Expert Mode, this gives the main
sp_QuickieStoreoutput two new columns. One is for adding hints and the other is for either forcing or unforcing that row's plan. Unconditionally, the special output table that shows existing hints now also has a column for removing that row's hint. That output table is already gated off by Expert Mode or@Only_Queries_With_Hints = 1.This was all a bit too easy. Maybe Erik's hard work has paid off? I worry that I've missed something big and obvious. It passes the tests that I've tried, but this all worked so well that I never needed to build any nasty cases.
I was unsure if the template that I've added for
sp_query_store_set_hintsshould have had an actual hint rather than a placeholder. I know how much Erik likesFORCE_LEGACY_CARDINALITY_ESTIMATION, so I would understand having that rather than mynewer_hints_go_here.Whether you see the code for forcing a plan or unforcing is conditional on if that row's plan is forced. This makes more sense if you know that calling
sp_query_store_force_planunforces whatever plan is currently forced.A
@disable_optimized_plan_forcing = ?placeholder is deliberately left in place. I always start with= 0and then switch to= 1only if that fails. If there is a best practice for this that we should put in the template, then I do not know it.Note that I have made no attempt to touch the
@replica_group_idstuff. Even on SQL Server 2025, I believe that's pretty obscure? Maybe somebody should come back to it when the feature becomes more used.I have not tested with exotic database names. Erik may know some good ones.