Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Searching for Plans Expand / Collapse
Author
Message
Posted Saturday, September 11, 2010 6:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:52 PM
Points: 33,268, Visits: 15,440
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
Posted Saturday, September 11, 2010 8:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 21,755, Visits: 15,457
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


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
Posted Sunday, September 12, 2010 10:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 37,101, Visits: 31,653
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #984522
Posted Sunday, September 12, 2010 10:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 37,101, Visits: 31,653
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #984523
Posted Sunday, September 12, 2010 11:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 8, 2013 5:39 PM
Points: 263, 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
Post #984528
Posted Monday, September 13, 2010 1:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,949, Visits: 8,318
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
Posted Monday, September 13, 2010 2:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 23, 2014 6:03 AM
Points: 351, Visits: 1,556
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
Post #984576
Posted Monday, September 13, 2010 2:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 8:16 AM
Points: 295, Visits: 1,011
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
Posted Monday, September 13, 2010 3:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, January 7, 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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:04 AM
Points: 176, 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.
Post #984622
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse