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?

  • Side note. If you are new to developing indexes you can certainly use those recommendations to point you in the right direction. I found these recomendations incredibly usefull when I was first starting.
    – Zane
    May 23 '13 at 17:08
  • 2
    @Zane sure, learn from them, but don't just press the "create all indexes" button or whatever it offers. May 23 '13 at 17:08
  • 1
    The phrase I would use to describe the recommendations are "good enough". Not "good", not "great", certainly not not "awesome" - just "good enough". Ocasionally, "good enough" is all you need or can afford. May 24 '13 at 2:50

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.

Your Answer

