Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Inside the Optimizer: Constructing a Plan - Part 2 Expand / Collapse
Author
Message
Posted Wednesday, September 8, 2010 8:11 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Comments posted to this topic are about the item Inside the Optimizer: Constructing a Plan - Part 2

Link to Part 1
Link to Part 3
Link to Part 4




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #982753
Posted Thursday, September 9, 2010 12:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 6,048, Visits: 8,328
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #982847
Posted Thursday, September 9, 2010 4:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 5:17 AM
Points: 4, Visits: 81
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 .
Post #982945
Posted Thursday, September 9, 2010 6:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 6, 2014 7:48 AM
Points: 22, Visits: 315
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'."
Post #983029
Posted Thursday, September 9, 2010 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 7:41 AM
Points: 10, Visits: 21
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.
Post #983044
Posted Thursday, September 9, 2010 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 178, Visits: 295
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 "

Post #983076
Posted Thursday, September 9, 2010 8:05 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #983102
Posted Thursday, September 9, 2010 8:15 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #983111
Posted Thursday, September 9, 2010 8:24 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #983128
Posted Thursday, September 9, 2010 8:34 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #983152
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse