<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQLServerCentral.com
»
Editorials
»
Searching for Plans
33 posts, Page 2 of 4
««
1
2
3
4
»
»»
Searching for Plans
Rate Topic
Display Mode
Topic Options
Author
Message
chrisn-585491
chrisn-585491
Posted Monday, September 13, 2010 6:58 AM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 478,
Visits: 1,260
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
The Dixie Flatline
The Dixie Flatline
Posted Monday, September 13, 2010 7:22 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 10:41 AM
Points: 3,788,
Visits: 5,538
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
Jack Corbett
Jack Corbett
Posted Monday, September 13, 2010 8:15 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 12:22 PM
Points: 10,571,
Visits: 11,871
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
Dave Ballantyne
Dave Ballantyne
Posted Monday, September 13, 2010 8:20 AM
SSCommitted
Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943,
Visits: 8,227
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
Eric M Russell
Eric M Russell
Posted Monday, September 13, 2010 8:30 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 1,162,
Visits: 3,328
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).
"Wise people understand the 10,000 things without going to each one.
They know them without having to look at each one,
and they transform all without acting on each one." - The Tao Te Ching: Verse 47
Post #984812
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Monday, September 13, 2010 8:30 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406,
Visits: 13,722
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
Mike Byrd
Mike Byrd
Posted Monday, September 13, 2010 9:15 AM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:02 AM
Points: 80,
Visits: 341
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Monday, September 13, 2010 9:18 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406,
Visits: 13,722
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
IceDread
IceDread
Posted Monday, September 13, 2010 9:29 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, November 16, 2012 3:47 AM
Points: 290,
Visits: 988
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
Dave Ballantyne
Dave Ballantyne
Posted Monday, September 13, 2010 9:33 AM
SSCommitted
Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943,
Visits: 8,227
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 »
33 posts, Page 2 of 4
««
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
