T-SQL Tuesday: Draw Your Own Execution Plans

Happy Little Operators

This month’s T-SQL Tuesday is a fun one. There’s only one problem: I’ve already blogged about my idea.

Instead, let’s talk about a fun 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.

Glamorous

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
  • Aggregations
  • 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.

Spool Removal

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!

1 thought on “T-SQL Tuesday: Draw Your Own Execution Plans”

Leave a Reply

Your email address will not be published. Required fields are marked *