I review Database Engine Tuning Advisor recommendations against the workload before applying them. That was the point behind my original opinion.
In one consulting case, a table accumulated many suggested structures. We removed more than 100 _DTA indexes and statistics after reviewing their impact. Performance improved in that case. The prefix alone didn’t establish safe removal.
DTA considers indexes, indexed views and partitioning, depending on options and release. I previously described its scope too narrowly. Representative workloads and tested recommendations can make it useful.
My preference for a small index set, often around five, is a heuristic. It is not a SQL Server limit. Balance read benefit with writes, storage, maintenance and constraints. Usage DMVs can reset or miss occasional important work.
Review overlapping indexes, plans and application evidence on a restored copy. Measure the change and preserve a rollback definition. Treat missing-index and unused-index reports as inputs with collection limits.
Reference: DTA workloads and design options.
Related reading
A tuning recommendation is not a deployment instruction, it is a proposal to evaluate against representative work.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
6 Comments. Leave new
I wouldn’t consider myself a follower of yours but so many of my questions end up answered on pages of your blog. Thank you for writing these and please continue.
Thanks for insight as always …. Huge Fan of your blog
why DTA suggesting extra statistics.
Generally if we create any index automatically statistics should be created for that particular index. But why again DTA suggesting exta statistics
As I said it the blog post, it is not the best tool.
If not the DTA, then what do you recommend? The suggestion to “adopt DMVs which gives metadata of the data” is quite vague. Do you have a blog post about how one goes about optimizing in this manner?
Yes, there are missing indexes and unused indexes script on the blog. You can use them.