Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Searching for Plans


Searching for Plans

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: Administrators
Points: 51399 Visits: 19004
Comments posted to this topic are about the item Searching for Plans

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
SQLRNNR
SQLRNNR
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27386 Visits: 18343
I think something like that could be quite useful. TOAD for SQL server does a similar thing - if you let it. It can take a query and suggest 20-50 or more rewrites of a query to test and see if you can make it go faster. Why not have something like that with the optimizer for query plans.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70345 Visits: 40733
CirquedeSQLeil (9/11/2010)
Why not have something like that with the optimizer for query plans.


I believe because it's mostly not worth it. How many query plans do you want the optimizer to come up with on a cursor or a set of non sargeable predicates (for example)? ;-)

--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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70345 Visits: 40733
The "best plan" is dictated only by four things and only two of those really matter where performance is concerned...

1. Hardware - Nill effect. Double the speed on the hardware on a 12 hour slug query and you still have a 6 hour slug query.

2. Design - I certainly don't mean to downplay this but it's a simple fact that once a design has been cast, it takes an act of Congress to change it. So, usually, Nill effect because it can't be affected.

3. Indexes - Huge effect IF they can be used properly.

4. Code - This is where the true performance lies. Without doing this correctly, nothing else matters. Not hardware, not design, and usually not indexes. Write code the right way at all times. If it takes you too much time, then you need to get better at writing good code. :-P The only way you can do that is to practice. A LOT! ;-)

--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
James Stover
James Stover
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 862
So, kind of a query "factory". Sounds good to me. Would you feel confident using an "optimal" query plan that's generated on sub-optimal hardware? You'd have to account for operators like parallelism and spooling which might be affected by hardware, if so.

Also, I'm assuming that the query you run 10,000x per day is run in parallel and the time savings is an aggregate as there are only 86,400s in one day (10s X 10,000 = 100,000s) :-)


James Stover, McDBA

Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2536 Visits: 8370
Personally, i would like an "OPTIMIZE FOR (CONCURRENT) " option.

In an ideal world this would create a plan that executes more slowly , but touch fewer rows and therefore reduce (b)locking and allow more tasks to execute.



Clear Sky SQL
My Blog
John.Sansom
John.Sansom
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

Group: General Forum Members
Points: 609 Visits: 1558
Hmmm interesting....

What about a hybrid solution perhaps, whereby the initial query plan provided is as is currently I.E. a good enough plan is provided quickly, whilst in the background SQL Server continues to calculate what is "the" most optimal plan.


John Sansom (@sqlBrit) | www.johnsansom.com
IceDread
IceDread
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 Visits: 1145
I totally agree.

I do a lot of reports. Some of which are used very often and to have the best plan for those would be very neat.
paul.jones
paul.jones
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 730
The problem is, what counts as "best"? Letting the optimiser work through all possible plans may well find one with a lower cost, but would it be quicker? It would just be the quickest plan for a mythical machine where the cost of reading from the disk vs. cost of CPU time was what optimiser thought it was and where no relevant data was still in memory. The fastest plan on your cheap PC with 1 hard drive may not be the best for the production server.
Open Minded
Open Minded
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 567
I wouldn't be surprised if the 3GB RAM, 250GB HD, quad-core i5 laptop will run the processes (including the messy ones) faster than the production server of 3 years ago.
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