Searching for Plans

Question

Searching for Plans

Steve Jones - SSC Editor

SSC Guru

Points: 736260
More actions
September 11, 2010 at 6:33 pm

#141923

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

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

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)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

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)

James Stover Hall of Fame Points: 3393 More actions · Answer 4

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 SSC-Dedicated Points: 33667 More actions · Answer 5

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]

John.Sansom SSCertifiable Points: 7335 More actions · Answer 6

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 SSCertifiable Points: 5050 More actions · Answer 7

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 SSC Eights! Points: 854 More actions · Answer 8

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 SSCommitted Points: 1872 More actions · Answer 9

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.

chrisn-585491 SSCoach Points: 16006 More actions · Answer 10

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.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 11

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 12

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
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 13

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]

Eric M Russell SSC Guru Points: 125561 More actions · Answer 14

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