Can I trust the Database Engine Tuning Advisor recommendations?

Question

Our database has become large, and we are trying to optimize performance.

Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with Profiler?

Is this a good way to optimize? How accurate are these recommendations?

Answer 1

The tuning advisor (DTA) can yield information about missing indexes, but the results are only as good as the workload you've provided and your ability to interpret the results and fill in the gaps.

• Your .trc file may only contain certain events, not all, and may not cover a full business cycle. So if you have a big set of reports that people run at the end of the month, you may be missing the most important part of your workload.
• DTA only considers improving the performance of read queries, and puts no thought whatsoever into what impact new indexes will have on the write portion of your workload. Indexes aren't free.
• DTA has a tendency to recommend multiple redundant indexes, that only differ by one additional key column or an include column. In a lot of cases, one index might be able to satisfy both queries that generated the recommendation for the two individual indexes.

So, the information can be useful, but I would never let any client blindly implement all of the indexes. You need to take a lot more data points into consideration.

For example, how have you come to the conclusion that adding indexes will improve any performance issues you are having? Have you performed any baselines and identified any bottlenecks?

Answer 2

You cannot and should not blindly implement recommendations by any tool without understanding the overall consequence that it might make on the application as well as server performance.

Always evaluate it before implementing it blindly.

DTA will generate its recommendations based on the workload that was presented to it.

From BOL :

Database Engine Tuning Advisor recommendations are based on the estimation of query execution costs that the SQL Server query optimizer makes. The actual improvements in query performance that result after applying Database Engine Tuning Advisor recommendations may be different than the estimated improvements that are displayed after Database Engine Tuning Advisor completes its analysis.

I am not saying that DTA's recommendation are wrong or incorrect, but just make sure your understand the implications as you might end up creating duplicate Indexes or more statistics on your tables/columns.