# Inside the Optimizer: Constructing a Plan - Part 1

• Comments posted to this topic are about the item Inside the Optimizer: Constructing a Plan - Part 1[/url]

• Feature-packed and very readable article as always Paul.

It's a little like watching your favourite tv program - just as you're really getting into it, you reach the end and the credits roll. Can't wait for the next installment π

βWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Great article. I look forward to the series.

Can you help me understand the difference in the Nested Loops operator between the first and second plan? Other than the '!' point warning symbol in the first plan they look the same to me. How can you tell one is doing a Cartesion Join while the other is doing an Inner join?

• should make for an interesting series.

How do you turn off rules in the optimiser?

Or is that something its best not to know? (or put in print) π

---------------------------------------------------------------------

• I agree with the previous comments - great article!

jim.jaggers1 (9/7/2010)

Can you help me understand the difference in the Nested Loops operator between the first and second plan? Other than the '!' point warning symbol in the first plan they look the same to me. How can you tell one is doing a Cartesion Join while the other is doing an Inner join?

The exclamation point symbol is not related. This simply indicates an warning from the optimzier - usually an indication of missing statistics.

In the exectution plan, you can find the difference by checking the properties of the operators (you can see them by hovering your mouse over them, or by right-clicking, selecting "properties", then clicking the operators you want to check).

If the filtering is done before the join, then you will see a "predicate" property on the scan before (to the right of) the join operator. (Or a "seek predicate" property if it's a seek). In this case, the join itsself is technically still a cartesian join, but on pre-filtered inputs (as if you write ... FROM (SELECT ... WHERE ...) AS a JOIN (SELECT ... WHERE ...) AS b ON ...)

If the filtering is done during the join, then you will see a "predicate" property and/or "outer references" property on the join operator. This is a true non-cartesian join (inner join, unless the operator is an outer join operator).

If the filtering is done after the join, you'll see a "predicate" or similar property on one of the operators after (to the left of) the join operator. Usually a filter operator. In these cases, the join was a "true" cartesian product.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

• george sibbald (9/7/2010)

How do you turn off rules in the optimiser?

Everything (in detail) will be revealed in subsequent parts...stay tuned π

• jim.jaggers1 (9/7/2010)

Can you help me understand the difference in the Nested Loops operator between the first and second plan? Other than the '!' point warning symbol in the first plan they look the same to me. How can you tell one is doing a Cartesion Join while the other is doing an Inner join?

The exclamation point is shown where compiler warnings occur. In this case the warning is: "No Join Predicate". A cartesian product is a join with no join predicate. The other visual clue is that the size of the arrow on the output of the join is *huge* - indicating a very large number of rows.

• It's too bad we're limited to just 5 stars...

Great article Paul.

Have you got some references you can pass on about how the optimizer works?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

• WayneS (9/7/2010)

Have you got some references you can pass on about how the optimizer works?

For sure:

Craig Freedman: http://blogs.msdn.com/b/craigfr/

SQL Server Storage Engine: http://blogs.msdn.com/b/sqlserverstorageengine/

Query Processing: http://blogs.msdn.com/b/sqlqueryprocessing/

Conor vs. SQL: http://blogs.msdn.com/b/conor_cunningham_msft/

Programmability & API: http://blogs.msdn.com/b/sqlprogrammability/

Bart Duncan: http://blogs.msdn.com/b/bartd/

Conor @ SQLskills: http://www.sqlskills.com/blogs/conor/

The SQL Server Internals books edited by Kalen Delaney are also excellent sources. Optimizer stuff is covered particularly well in 2008 Internals (Conor Cunningham) and 2005 Query Tuning & Optimization (Craig Freedman).

• Chris Morris-439714 (9/7/2010)

Feature-packed and very readable article as always Paul.

It's a little like watching your favourite tv program - just as you're really getting into it, you reach the end and the credits roll. Can't wait for the next installment π

Thank you, Chris. Steve did an excellent job with the scheduling on this four-part series, so you'll get Part 2 on Thursday, and Parts 3 & 4 on Tuesday & Thursday next week. The graphics are best in Part 1, but the content gets better in the later parts π

• Paul White NZ (9/7/2010)

WayneS (9/7/2010)

Have you got some references you can pass on about how the optimizer works?

For sure:

Craig Freedman: http://blogs.msdn.com/b/craigfr/

SQL Server Storage Engine: http://blogs.msdn.com/b/sqlserverstorageengine/

Query Processing: http://blogs.msdn.com/b/sqlqueryprocessing/

Conor vs. SQL: http://blogs.msdn.com/b/conor_cunningham_msft/

Programmability & API: http://blogs.msdn.com/b/sqlprogrammability/

Bart Duncan: http://blogs.msdn.com/b/bartd/

Conor @ SQLskills: http://www.sqlskills.com/blogs/conor/

The SQL Server Internals books edited by Kalen Delaney are also excellent sources. Optimizer stuff is covered particularly well in 2008 Internals (Conor Cunningham) and 2005 Query Tuning & Optimization (Craig Freedman).

BWAA-HAAA!!!! Been at this for a while, huh? π

I haven't changed my mind since the first time I saw this series and agree that 5 stars isn't enough. Glad you brought it to SSC. Thanks, Paul.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Great stuff. Looking forward to part 2 on Thursday.

• Excellent job, Paul!

I'm sure people will love the next 3. I know I learned a few things.

• Thanks for the excellent article! It was very clear and informative!

I think a deeper understanding of how the optimizer works helps us to write better queries.

Looking forward to the rest of the series.

• Paul,

In the part1, you have mentioned about SELonJN operation, how it can be enabled?

It will be really handly if you'll also post the changes you made to the indexes or query to achieve the better performing execution plans.

Viewing 15 posts - 1 through 15 (of 31 total)