Inside the Optimizer: Constructing a Plan - Part 2

Question

Post reply

Inside the Optimizer: Constructing a Plan - Part 2

Paul White

SSC Guru

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

#238037

Comments posted to this topic are about the item Inside the Optimizer: Constructing a Plan - Part 2[/url]
Link to Part 1[/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 15 total)

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 1

My sole critisicm of this article is that it's much too short.

I can't wait to read the parts three and four!

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/

skyline6969 SSC Veteran Points: 276 More actions · Answer 2

Good article Paul, look forward to reading more on it!

I also would like to see more articles like this as I had studied general database engine low-level workings and theories (17 years ago I wrote a thesis on using compressed caches in database engines, never thought it would take this long to see even basic compression used - SQL 2008 being Microsoft's first real go at this lol).

It is great to see how SQL Server applies them, as outside open source code and Microsoft white papers it is rarely discussed now :(.

bpportman 52825 SSC Eights! Points: 870 More actions · Answer 3

Good article, but there is not enough information.

How are these done?

"The first one is achieved by a rule called JNtoIdxLookup. The second requirement is a correlated loops join - also known as an Apply. The rule needed to transform our query to that form is AppIdxToApp."

"We can remove this Compute Scalar, and the need to compute COUNT(*), by normalising the GROUP BY using a rule called 'NormalizeGbAgg'."

aeyates Valued Member Points: 70 More actions · Answer 4

Do you have before/after timings?

Was the query statement unchanged?

Did you add an index?

It is not clear how the query was improved.

Rick-153145 SSCrazy Points: 2823 More actions · Answer 5

Great series, looking forward to the next two instalments.

I feel I must point out this great typo though, made me chuckle:

"1. Convert the naive nested loops join to an index nested loops join "

:-D:-D

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

bpportman 52825 (9/9/2010)
Good article, but there is not enough information. How are these done?

That would leave me nothing to say in Parts 3 & 4! 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

aeyates (9/9/2010)
Do you have before/after timings?

No.

Was the query statement unchanged?

Yes.

Did you add an index?

No.

It is not clear how the query was improved.

The series is about how the query optimizer works internally. I am showing some of the plan alternatives it considers on the way to finding the final plan. Part 3 and Part 4 of this series will reveal the magic.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Rick-153145 (9/9/2010)
I feel I must point out this great typo though, made me chuckle:
"1. Convert the naive nested loops join to an index nested loops join "

I still can't see a typo there, Rick.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Hugo Kornelis (9/9/2010)
My sole critisicm of this article is that it's much too short.
I can't wait to read the parts three and four!

Thank you, Hugo.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Once again, great job.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Paul White NZ (9/9/2010)
Rick-153145 (9/9/2010)
I feel I must point out this great typo though, made me chuckle:
"1. Convert the naive nested loops join to an index nested loops join "
I still can't see a typo there, Rick.

I'm missing it too. Maybe it was read as "Knave" instead as "naive"

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

CirquedeSQLeil (9/9/2010)
I'm missing it too. Maybe it was read as "Knave" instead as "naive"

On reflection, I think the problem is simply lack of familiarity with the term. I put a Books Online reference on the same line in the article to explain "naive nested loops join"...:Whistling:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Paul White NZ (9/9/2010)
CirquedeSQLeil (9/9/2010)
I'm missing it too. Maybe it was read as "Knave" instead as "naive"
On reflection, I think the problem is simply lack of familiarity with the term. I put a Books Online reference on the same line in the article to explain "naive nested loops join"...:Whistling:

Possible. The reference should help to clarify.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 14

Definately looking forward to parts 3 and 4, thanks for the articles!

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA