5

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?

Improve this question
1

2 Answers 2

Reset to default
10

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?

Improve this answer
3
  • 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
    Commented May 23, 2013 at 17:08
  • 2
    @Zane sure, learn from them, but don't just press the "create all indexes" button or whatever it offers.
    – Aaron Bertrand
    Commented May 23, 2013 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.
    – Greenstone Walker
    Commented May 24, 2013 at 2:50
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.