SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inside the Optimizer: Constructing a Plan - Part 1


Inside the Optimizer: Constructing a Plan - Part 1

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)

Group: General Forum Members
Points: 119114 Visits: 41482
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? :-P

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
NickDBA
NickDBA
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 Visits: 938
Great stuff. Looking forward to part 2 on Thursday.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84712 Visits: 19224
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
My Blog: www.voiceofthedba.com
Goldie Lesser
Goldie Lesser
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1016 Visits: 1501
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
Anam Verma
SSC Eights!
SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)

Group: General Forum Members
Points: 835 Visits: 1318
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.
Paul White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20712 Visits: 11359
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
hrc
hrc
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 52
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)
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13042 Visits: 13351
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
Paul White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20712 Visits: 11359
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)

Group: General Forum Members
Points: 119114 Visits: 41482
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. Hehe

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search