Skip to content

sp_QuickieStore: provide emergency troubleshooting template in Expert Mode #678

@ReeceGoding

Description

@ReeceGoding

Is your feature request related to a problem? Please describe.
One of the best features of sp_BlitzCache is that it provides columns like Remove Plan Handle From Cache, so that I have some SQL to copy and run to fix my problem when I'm in an emergency. This means that in a parameter sensitivity emergency, I'm turning to the plan cache rather than Query Store.

Emergency troubleshooting with Query Store is harder than the plan cache, because it's better. When you kick a plan from the cache, you're doing no thinking and just rolling the dice to get a better plan. When you pin a plan or hint a query in Query Store, you need to spend time looking at the Expert Mode data and thinking. After you've done that, you need to waste time looking up the syntax for either sp_query_store_force_plan or sys.sp_query_store_set_hints and filling in the values for this query/hint/plan. In the style of sp_BlitzCache, I would like to eliminate that final frustration.

Describe the solution you'd like
In Expert Mode, sp_QuickieStore should provide a column (I'm not sure of the name yet) that gives copy-and-pasteable T-SQL for:

  • Forcing the plan on this row.
  • Un-forcing the plan on this row if it's already forced.
  • Hinting the query on this row.
  • Maybe un-hinting? I don't recall if that clears automatic things like CE feedback or whatever needs to be done to clear adaptive memory grants.

I sometimes find that query hints don't stick until I've kicked the current plan out of the cache. We may need to provide syntax for that as well, or maybe I just need to understand the requirements for query hints to apply.

Describe alternatives you've considered
I suppose this could be two columns instead? One for hints and one for forced plans?

Are you ready to build the code for the feature?
As much as we'd love to build everything that everyone wants for free, we need your help. Open source is built with your help and code. Are you ready to commit time to this project? Have you got existing code you can help contribute to solve the problem?
Yeah, it sounds fairly easy.

IMPORTANT: If you're going to contribute code, please read the contributing guide first.
https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md
Sure, it's been a while.

Metadata

Metadata

Assignees

Labels

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions