SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Searching for Plans


Searching for Plans

Author
Message
chrisn-585491
chrisn-585491
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3972 Visits: 2565
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
The Dixie Flatline
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13364 Visits: 6903
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
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46399 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7424 Visits: 8370
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
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29883 Visits: 11559
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. :-P 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).


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Steve Jones
Steve Jones
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151170 Visits: 19455
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
My Blog: www.voiceofthedba.com
Mike Byrd
Mike Byrd
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 389
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151170 Visits: 19455
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
My Blog: www.voiceofthedba.com
IceDread
IceDread
SSC Eights!
SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)

Group: General Forum Members
Points: 979 Visits: 1145
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.
Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7424 Visits: 8370
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search