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 ««1234»»»

Inside the Optimizer: Constructing a Plan - Part 1 Expand / Collapse
Author
Message
Posted Tuesday, September 7, 2010 11:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:14 PM
Points: 35,361, Visits: 31,898
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #981747
Posted Tuesday, September 7, 2010 1:23 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 357, Visits: 915
Great stuff. Looking forward to part 2 on Thursday.
Post #981845
Posted Tuesday, September 7, 2010 1:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:53 PM
Points: 31,177, Visits: 15,623
Excellent job, Paul!

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







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #981853
Posted Tuesday, September 7, 2010 1:53 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 1:09 PM
Points: 478, Visits: 1,417
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.
Post #981873
Posted Tuesday, September 7, 2010 8:28 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:49 PM
Points: 528, Visits: 1,270
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.
Post #982007
Posted Tuesday, September 7, 2010 9:02 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
Anam Verma (9/7/2010)
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.

All covered in the remaining parts of the series, Anam




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #982010
Posted Wednesday, September 8, 2010 1:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 5:49 AM
Points: 12, Visits: 45
Great article, great topic. I belong to the newcomer group and as such I would have liked to see how those rules you talk about, were executed. You move the "where" into the join, but in the end you just mention some rules that puts the Cherry on top of it - and I don't understand what you did.

As a newcomer to the optimizer it is not all clear that the symbols in the execution plan mean table scans or Cartesian products. I can see they cost a lot but not what they in are (well, actually I can but ... I had to think first!). Maybe some kind of symbol explanation could be added? .. or maybe this is found elsewhere in some of the many links you added.

(and I now find that most questions was found on page two in some of the newest posts)
Post #982074
Posted Wednesday, September 8, 2010 1:34 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 4,396, Visits: 10,684
Thank you, Paul. This is one of the topics I always wanted to investigate deeper, but never did.
I look forward to reading the next parts.
I will try to avoid annoying you with my questions and post them once I got through the whole series, as I'm sure I will find the answers along the way.

Great, as usual!


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #982088
Posted Wednesday, September 8, 2010 3:19 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
hrc_public (9/8/2010)
Great article, great topic. I belong to the newcomer group and as such I would have liked to see how those rules you talk about, were executed. You move the "where" into the join, but in the end you just mention some rules that puts the Cherry on top of it - and I don't understand what you did.

I hope the later parts will make things clearer, though this is moderately advanced stuff so it might take a while to fully 'get it'.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #982140
Posted Wednesday, September 8, 2010 5:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:14 PM
Points: 35,361, Visits: 31,898
Anam Verma (9/7/2010)
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.


I had the same problem when I first read Paul's series. The point to understand here is that Paul is not actually modifying the query in this first part... instead, he's actually enabling and disabling rules in SQL Server and running the same query. I'm thick... it took until part 4 for me to figure that out.

As he said, he'll get to how to do that later. I recommend that if you ever do that, you do it only on a test server.

I also recommend keeping track of the parts of the series... when you read part 4, you'll be itching to start from the beginning again.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #982191
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse