Inside the Optimizer: Constructing a Plan - Part 1

Question

Inside the Optimizer: Constructing a Plan - Part 1

Viewing 15 posts - 16 through 30 (of 31 total)

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

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

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
SQLkiwi blog
@SQL_Kiwi

hrc Old Hand Points: 323 More actions · Answer 2

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)

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

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

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

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
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003899 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hrc Old Hand Points: 323 More actions · Answer 6

Thanks everyone. I'll look forward to the next articles. Hope you're alright down there (:hehe:). I can see it has been a bit shaky in Christchurch some 350km away.

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

hrc_public (9/8/2010)
Hope you're alright down there (:hehe:). I can see it has been a bit shaky in Christchurch some 350km away.

Yes we're fine - ChCh is going to need a bit of work though. All very sad.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 8

It's amazing how you 'uncomplicate' things. That's what I like the most about your posts.

I saw Jeff's mention that rules should be enabled only on a test server. Does it mean that the improvements, that you make to queries this way, cannot be used in a real-world scenario?

Thanks for the links 🙂

- arjun

https://sqlroadie.com/

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

Jeff Moden (9/8/2010)
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:

I'll try to remember to include direct links to the previous parts in the opening comment of the discussion thread. That said, Steve is normally pretty good about adding links to previous parts to the article text too, so I'll check that first.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Arjun Sivadasan (9/8/2010)
I saw Jeff mention that rules should be enabled only on a test server. Does it mean that the improvements, that you make to queries this way, cannot be used in a real-world scenario?

Nope, the optimizer normally produces a very good plan by using all of the rules.

What I am doing in this first part, is showing you what horrible plans can be produced if we prevent the optimizer from using all of its tricks.

The point is to show the process the optimizer goes through when searching for a great query plan.

Part 2 will be published in a few hours. In fact it's already an hour late in my time zone 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 11

Paul White NZ (9/8/2010)
What I am doing in this first part, is showing you what horrible plans can be produced if we prevent the optimizer from using all of its tricks.
The point is to show the process the optimizer goes through when searching for a great query plan.

Okay, I get it now. BTW 'mention' can also be used as a noun, so Jeff's mention is grammatically correct. 😀

- arjun

https://sqlroadie.com/

peter-757102 SSCertifiable Points: 6877 More actions · Answer 12

peter-757102

SSCertifiable

Points: 6877

September 8, 2010 at 7:24 am

#1217920

Me likes 🙂

onkelfela Grasshopper Points: 15 More actions · Answer 13

onkelfela

Grasshopper

Points: 15

September 9, 2010 at 4:25 am

#1218473

+

Douglas Osborne-456728 SSCommitted Points: 1569 More actions · Answer 14

Paul,

I knew you were in rarefied air when a Google search on 'NormalizeGbAgg SQL' returned 36 results and Bing returned 2 - both yours!

10 Star article

Doug

SQLRNNR SSC Guru Points: 281334 More actions · Answer 15

Nice article - good to see it published at SSC.

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