September 6, 2010 at 8:11 pm
Comments posted to this topic are about the item Inside the Optimizer: Constructing a Plan - Part 1[/url]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 7, 2010 at 2:56 am
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 π
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
September 7, 2010 at 7:35 am
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?
September 7, 2010 at 7:39 am
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) π
---------------------------------------------------------------------
September 7, 2010 at 8:01 am
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.
September 7, 2010 at 9:08 am
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
September 7, 2010 at 9:13 am
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
September 7, 2010 at 10:24 am
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
September 7, 2010 at 10:58 am
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
September 7, 2010 at 11:12 am
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
September 7, 2010 at 11:16 am
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
Change is inevitable... Change for the better is not.
September 7, 2010 at 1:23 pm
Great stuff. Looking forward to part 2 on Thursday.
September 7, 2010 at 1:29 pm
Excellent job, Paul!
I'm sure people will love the next 3. I know I learned a few things.
September 7, 2010 at 1:53 pm
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.
September 7, 2010 at 8:28 pm
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 32 total)
You must be logged in to reply to this topic. Login to reply