Inside the Optimizer: Constructing a Plan - Part 1

Question

Inside the Optimizer: Constructing a Plan - Part 1

Paul White

SSC Guru

Points: 150467
More actions
September 6, 2010 at 8:11 pm

#226538

Comments posted to this topic are about the item Inside the Optimizer: Constructing a Plan - Part 1[/url]
Link to Part 2[/url]
Link to Part 3[/url]
Link to Part 4[/url]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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}

For fast, accurate and documented assistance in answering your questions, please read this article.
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

SQL Curious SSC Journeyman Points: 99 More actions · Answer 2

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?

george sibbald SSC Guru Points: 104210 More actions · Answer 3

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) 🙂

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

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/

Paul White SSC Guru Points: 150467 More actions · Answer 5

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 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 6

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 7

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!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 8

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/

CSS: http://blogs.msdn.com/b/psssql/

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

White Papers: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

SQLCAT: http://blogs.msdn.com/b/sqlcat/

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/

QO Team: http://blogs.msdn.com/b/queryoptteam/

Joe Chang: http://www.qdpma.com/CBO/SQLServerCostBasedOptimizer.html

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 9

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

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/
CSS: http://blogs.msdn.com/b/psssql/
Query Processing: http://blogs.msdn.com/b/sqlqueryprocessing/
White Papers: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
SQLCAT: http://blogs.msdn.com/b/sqlcat/
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/
QO Team: http://blogs.msdn.com/b/queryoptteam/
Joe Chang: http://www.qdpma.com/CBO/SQLServerCostBasedOptimizer.html
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.

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

NickDBA Hall of Fame Points: 3792 More actions · Answer 11

Great stuff. Looking forward to part 2 on Thursday.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 12

Excellent job, Paul!

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

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 13

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.

Anam Verma SSCertifiable Points: 5365 More actions · Answer 14

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.