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 Monday, September 13, 2010 6:58 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 04, 2014 8:42 AM
Points: 598, Visits: 1,504
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.
Post #984724
Posted Monday, September 13, 2010 7:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:45 AM
Points: 3,949, Visits: 5,944
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? -- Stephen Stills
Post #984745
Posted Monday, September 13, 2010 8:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 10,910, Visits: 12,545
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #984796
Posted Monday, September 13, 2010 8:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:26 AM
Points: 1,949, Visits: 8,291
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
Kent user group
Post #984799
Posted Monday, September 13, 2010 8:30 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 1,470, Visits: 4,272
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).



"Winter Is Coming" - April 6, 2014
Post #984812
Posted Monday, September 13, 2010 8:30 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 4:56 PM
Points: 32,771, Visits: 14,935
Added my vote. That's a good suggestion, though I wonder how well people would know how to use it.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #984813
Posted Monday, September 13, 2010 9:15 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:16 PM
Points: 80, Visits: 351
At first reading I thought that was a neat idea, but on reflection (and sadly experience), what runs on one machine and one set of data does not mean it could come up with the "optimal" plan for another machine and its data. The whole purpose of recompile is take advantage of changing data and its statistics and change the query plan accordingly. Data normally not only changes in quantity, but usually in its distribution and relationship to each other. What works now may not be the optimal plan 10 minutes later.

Mike Byrd
Post #984869
Posted Monday, September 13, 2010 9:18 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 4:56 PM
Points: 32,771, Visits: 14,935
Mike,

good points, although often we have the plans cached, so I'd expect that if I run a query regularly, I'm getting the same plan every time.

Perhaps this isn't a great idea, and it might be too complex. You'd have to be sure that your data distribution was similar, and that your test machine could duplicate production, or your test instance would have switches to set the # of CPUs, RAM, etc.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #984873
Posted Monday, September 13, 2010 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 10:18 AM
Points: 294, Visits: 1,008
Jack Corbett (9/13/2010)
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.


Two very good points.
Post #984879
Posted Monday, September 13, 2010 9:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:26 AM
Points: 1,949, Visits: 8,291
Maybe one already exists....??

Not sure what "higher authority" Paul set his info from (such as undocumented flags used in http://sqlblog.com/blogs/paul_white/archive/2010/07/31/inside-the-optimiser-constructing-a-plan-part-4.aspx) but it doesnt go beyond the realms of reason that there *could* be a "set good enough costs" flag somewhere considering you can mess with the optimizer to this degree.

This is all pure speculation on my part.




Clear Sky SQL
My Blog
Kent user group
Post #984883
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse