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


Tune SQL Server 2012 Databases Using Database Engine Tuning Advisor


Tune SQL Server 2012 Databases Using Database Engine Tuning Advisor

Author
Message
prettsons
prettsons
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3057 Visits: 1422
Comments posted to this topic are about the item Tune SQL Server 2012 Databases Using Database Engine Tuning Advisor

SQL Database Recovery Expert :-)
Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25121 Visits: 12584
The article is a good "how-to" on operating the DTA, but it unfortunately fails to put up a large disclaimer, that the DTA is far from perfect. It is a perfectly fine tool to get a first rough idea of where to start tuning, but please never just blindly implement the recommended indexes. Look at them, try to understand them, then use them as the input to come up with your own set of indexes.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
wshmstr
wshmstr
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 80
DTA is really far far from being perfect.
Every time I try to use it, getting "DTA engine unexpectedly stopped". Also every time this happens, I have to manually delete hypothetical indexes created by DTA.
3 years ago I was getting this error in SQL2008, now I'm still getting this error in SQL2012 (of course with full updates and cumulatives applied).
And there's no solution to that. Some said, if you use temp tables this will happen. Some said, if you use functions not real tables, this will happened. Some said, if you use xml fields this will happen. bla bla bla.
No real explanation, no real solution.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146043 Visits: 33199
Congratulations on your first article. Nice work.

For the future, a few suggestions that might help. Doing a how-to article like this is great. But you also need to include guidance for when and where you can run these tools. For example, you show how to tune a query or a workload, but in neither do you suggest that this could cause considerable load on a production system and maybe shouldn't be run there. You should also address the shortcomings of the tool. Many people who use the DTA find few helpful suggestions.

Just a couple of things to think about for next time. Other than that, great job. Keep it up.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Rob Ashton
Rob Ashton
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 133
Indeed, following on from wshmstr's comment, I ended up raising a MS Connect ticket (http://connect.microsoft.com/SQLServer/feedback/details/694338/instance-collations-with-supplementary-character-option-lead-to-a-failure-to-install-the-deta-sprocs) over an issue with DETA not working on instances using the new Unicode supplementary character collation option in SQL Server 2012.

I almost considered rewriting the SPs that DETA is based on since they looked to have had no maintenance on them since SQL Server 2000-era (ntext data type... *shudder*), but wondered what the dev team would actually do with the bug report.

Thankfully, it's fixed, but I still haven't found the time to go back and work out what they actually did. Maybe this weekend... Smile
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