Searching for Plans

  • Comments posted to this topic are about the item Searching for Plans

  • 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[/url]
    Learn Extended Events

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

    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)

  • 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. 😛 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.

    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)

  • 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

  • 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[/url]

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

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

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

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

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

    The problem is that the laptop would be I/O limited and couldn't compare to a properly setup server with a slower CPU but better storage.

  • I wouldn't mind having an option to let the optimizer run longer to find a "best" execution plan, as long as the result was cached and became usable by later queries that didn't have the option set. This is something that could be run during off hours and could potentially pay dividends. I say "potentially" because I agree with the author, and Jeff, that HOW you ask for equivalent results is the true key to performance gains.

    That said, it would be an interesting challenge to try to write code that would analyze queries and suggest alternative coding that might run faster.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • I think that this would be nice, but as many of us know, the best plan for one set of parameter values for a query is not always the best plan when the parameters values, so you may not actually get the best plan for your application. Now if this worked off of a workload file created using a trace where the optimization process takes into account the parameter values provided for each query in the workload and produces the best plan for that workload. So if you have a query where you are selecting customers by country, the optimizer takes into account that 60% of the time the query is for customers in the US, then you might have something.

    Something I think is somewhat related, I'd love for MS to allow you to tell Developer Edition what mode to run in, i.e. Standard Edition/Workgroup/Enterprise, so I could be sure I'm not developing something using Enterprise only features when my production server is Standard. This might be something you need to select when you install.

  • Jack Corbett (9/13/2010)


    Something I think is somewhat related, I'd love for MS to allow you to tell Developer Edition what mode to run in, i.e. Standard Edition/Workgroup/Enterprise, so I could be sure I'm not developing something using Enterprise only features when my production server is Standard. This might be something you need to select when you install.

    Try upvoting this

    https://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version



    Clear Sky SQL
    My Blog[/url]

  • Jeff Moden (9/12/2010)


    The "best plan" is dictated only by four things and only two of those really matter where performance is concerned...

    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. 😛 The only way you can do that is to practice. A LOT! 😉

    In some cases, the Database Tuning Advisor will suggest minor revisions to the T-SQL it's analyzing, like replacing a paramerized sp_executesql call with a straight SQL statement. I'm sure it could be made smart enough to recognize when someone is using a cursor or while loop to do something like populate a column with a comma seperated list of values, and then suggest they use a FOR XML sub-query instead, or it could simply advise them to do it on the front end. It could also suggest the removal of hints that have proven unbenefitial or the removal of unneeded distinct clauses (by confirming the presence of unique keys and join types).

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 15 posts - 1 through 15 (of 42 total)

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