Happy Little Operators
Instead, let’s talk about a different one: Editable Execution Plans.
We already have this to some degree via query hints and turning optimizer rules on and off.
The problem is that you have to remember all those crazy things, and some hints can affect multiple parts of the plan that you don’t want changed.
If the query you’re changing is in the middle of a big ol’ stored procedure, this process is even more tedious.
Let’s say you wanna experiment with different things, but not without re-running a query over and over to check on the plan with your written hints.
You could change:
- Join order
- Join types
- Index choices
- Seeks or Scans
- Memory grants and fractions
Basically any element exposed in the XML would be up for grabs — I won’t list them all here, because I think you get the point.
Then you can run your query with your new plan.
If it’s a stunning success, you can force that plan.
This has downsides, of course.
You could make things worse (but you could do that anyway — trust me, I do it all the time), you could get incorrect results, or errors if you remove certain operators (again, these are all things you can do by being silly anyway).
But, like query hints, this could be a really powerful tool in the hands of experienced query tuners, and people looking to get better at query tuning.
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 on 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.
- SQL Server 2017 CU 30: The Real Story With SelOnSeqPrj Fixes
- SQL Server 2022 Parameter Sensitive Plan Optimization: Does Not Care To Fix Your Local Variable Problems
- SQL Server 2022 Parameter Sensitive Plan Optimization: Sometimes There’s Nothing To Fix
- SQL Server 2022 Parameter Sensitive Plan Optimization: Does PSP Work With Dynamic SQL?