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

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!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount 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.