Can I trust the Database Engine Tuning Advisor recommendations?

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?

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?

StackExchange Database Administrators Q#42920, answer score: 10