Inside the Optimizer: Constructing a Plan - Part 1

re: Inside the Optimiser: Constructing a Plan

Alexander Kuznetsov — Wed, 28 Jul 2010 20:04:38 GMT

Welcome aboard, Paul!

This is a very interesting series.

Would you mind sharing with us what exactly did you mean by "enabling the SELonJN optimisation rule" and by "Enabling a couple more rules"?

re: Inside the Optimiser: Constructing a Plan

Paul White — Thu, 29 Jul 2010 01:22:26 GMT

Hey, Alex!

Thanks for the welcome - I'm very excited to be on here, as I've been a big fan of blogs like yours and Adam's for many years.

The current series of posts is building up to answering the exact question you raise. I promise that all will be revealed very soon :)

Thanks so much for the feedback.

Paul

re: Inside the Optimiser: Constructing a Plan

Uri Dimant — Thu, 29 Jul 2010 05:34:30 GMT

Paul

Running your query on AW of SQL Server 2005 I got Index Seek on P.ProductNumber LIKE N'T%' and Clustered Index Seek on ProductInventory..

re: Inside the Optimiser: Constructing a Plan

Paul White — Thu, 29 Jul 2010 05:40:26 GMT

Hey Uri,

Yes you would do - that's the final, fully-optimised, form of the query plan. What I'm trying to show in these series of posts, is how the optimiser incrementally finds improved plans by applying its internal rules.

I've tweaked the optimiser to restrict the rules available to it at each stage, to better illustrate the iterative nature of the process. Hopefully, things will become clearer as the series progresses ;c)

Paul

Another Interesting MERGE Bug

Page Free Space: Paul White — Tue, 03 Aug 2010 18:06:07 GMT

Summary: Investigating an optimiser transformation that exposes a bug in SQL Server’s MERGE implementation.

re: Inside the Optimiser: Constructing a Plan - Part 1

Jay — Wed, 04 Aug 2010 01:15:55 GMT

hello:

Nice article. i ran un-documented DMV & found 377 implementation rules in SQL Sever 2008 Developer Edition SP-1.

I am curious to know if there is a possible way to see the implementation rules for every execution plan when query or Store Procs is executed in SSMS with Actual Execution Plan

Thanks

Jay

Iterators, Query Plans, and Why They Run Backwards

Page Free Space: Paul White — Wed, 04 Aug 2010 18:10:06 GMT

Iterators SQL Server uses an extensible architecture for query optimisation and execution, using ‘iterators’

Page Free Space: Paul White : Inside the Optimiser: Constructing a Plan ??? Part 4

Tue, 10 Aug 2010 22:23:53 GMT

PingBack from http://sqlblog.com/blogs/paul_white/archive/2010/07/31/inside-the-optimiser-constructing-a-plan-part-4.aspx

Page Free Space: Paul White : Inside the Optimiser: Constructing a Plan ??? Part 3

Tue, 10 Aug 2010 22:24:33 GMT

PingBack from http://sqlblog.com/blogs/paul_white/archive/2010/07/31/inside-the-optimiser-constructing-a-plan-part-3.aspx

Page Free Space: Paul White : Inside the Optimiser: Constructing a Plan - Part 2

Page Free Space: Paul White : Inside the Optimiser: Constructing a Plan - Part 2 — Tue, 10 Aug 2010 22:25:04 GMT

PingBack from http://sqlblog.com/blogs/paul_white/archive/2010/07/29/inside-the-optimiser-constructing-a-plan-ii.aspx

re: Inside the Optimiser: Constructing a Plan - Part 1

Jeff Moden — Tue, 24 Aug 2010 00:35:28 GMT

Awesome series, Paul! Thanks for taking the time to post it.

--Jeff Moden

re: Inside the Optimiser: Constructing a Plan - Part 1

Paul White — Tue, 24 Aug 2010 00:50:43 GMT

Thank you Jeff - means a lot coming from you.

re: Inside the Optimiser: Constructing a Plan - Part 1

ColdCoffee — Thu, 26 Aug 2010 14:41:11 GMT

Paul, awesome article.. still dint touch other parts, but will definitely spend the night with them.. thanks for sharing...

re: Inside the Optimiser: Constructing a Plan - Part 1

Paul White — Thu, 26 Aug 2010 15:01:31 GMT

Hey Cold Coffee (from SSC I presume!)

Thanks - and do try to make it to the end, because most of the stuff that makes sense is that way...

Paul

re: Inside the Optimiser: Constructing a Plan - Part 1

Chris Leonard — Tue, 14 Sep 2010 04:26:23 GMT

This is really interesting. May I (with attribution to you, of course) use some of this information in my PASS Summit presentation? The DMV is part 4 could be useful for showing the impact of certain hints.

Also, is there a way to get SQL Server to show the logical operation tree it comes up with to begin with? I've heard that older versions of SQL Server would sometimes rewrite queries (replacing left joins with right joins, for example) but I can't find any evidence of this kind of "query normalization" still happening in SQL 2005/2008.

Thanks again - great series!

re: Inside the Optimiser: Constructing a Plan - Part 1

Paul White — Wed, 22 Sep 2010 22:10:34 GMT

Hi Chris,

Apologies for the slow response. Yes of course that would be fine - in fact I'd be honoured, thank you.

There's no way to expose the initial algebrized tree I'm afraid. The optimizer sure does consider many plan alternatives, and transforming left and right joins is just one of many tricks it can perform.

Paul

Heaps of Trouble?

Paul White: Page Free Space — Thu, 09 Dec 2010 17:17:46 GMT

If you’re not already a regular reader of Brad Schultz’s blog, you’re missing out on some great material. 

Paul White: Page Free Space : Inside the Optimizer: Constructing a Plan - Part 1

Paul White: Page Free Space : Inside the Optimizer: Constructing a Plan - Part 1 — Sun, 27 Feb 2011 01:26:32 GMT

PingBack from http://sqlblog.com/blogs/paul_white/archive/2010/07/29/inside-the-optimiser-constructing-a-plan.aspx

Undocumented Query Plans: The ANY Aggregate

Paul White: Page Free Space — Fri, 01 Jul 2011 14:51:59 GMT

As usual, here’s a sample table: With some sample data: And an index that will be useful shortly: There’s

re: Inside the Optimizer: Constructing a Plan - Part 1

Adriano Nascimento — Tue, 07 Aug 2012 15:11:36 GMT

This is a very Good Series,

When will you write a book about "Inside the Optimizer"?

Thanks,

Adriano Nascimento