Searching for Plans

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Searching for Plans

33 posts, Page 1 of 4

1 2 3 4 »»»

Searching for Plans

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Saturday, September 11, 2010 6:33 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:19 PM
Points: 31,526, Visits: 13,863

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

Post #984330

SQLRNNR

Posted Saturday, September 11, 2010 8:05 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853, Visits: 12,438

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #984337

Jeff Moden

Posted Sunday, September 12, 2010 10:40 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:01 PM
Points: 33,111, Visits: 27,037

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)? Wink

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #984522

Jeff Moden

Posted Sunday, September 12, 2010 10:49 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:01 PM
Points: 33,111, Visits: 27,037

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

The only way you can do that is to practice. A LOT! Wink

--Jeff Moden

Post #984523

James Stover

Posted Sunday, September 12, 2010 11:04 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 10:37 AM
Points: 263, Visits: 859

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) Smile

James Stover, McDBA

Post #984528

Dave Ballantyne

Posted Monday, September 13, 2010 1:29 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:05 AM
Points: 1,943, Visits: 8,229

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
Kent user group

Post #984557

John.Sansom

Posted Monday, September 13, 2010 2:16 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 11:19 AM
Points: 343, Visits: 1,466

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.sqlbrit.com

Post #984576

IceDread

Posted Monday, September 13, 2010 2:32 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, November 16, 2012 3:47 AM
Points: 290, Visits: 988

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.

Post #984582

paul.jones

Posted Monday, September 13, 2010 3:01 AM

Old Hand

Group: General Forum Members
Last Login: Monday, January 07, 2013 2:22 AM
Points: 376, Visits: 728

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.

Post #984601

Posted Monday, September 13, 2010 3:44 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 11, 2013 12:58 AM
Points: 174, Visits: 555

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.

Post #984622

« Prev Topic | Next Topic »

33 posts, Page 1 of 4

1 2 3 4 »»»

Permissions