Can we totally depend on Database Engine Tuning Advisor?

Question

I read about Database Engine Tuning Advisor and how it can suggest the index for a given query. My question is can I completely depend on these suggestions when creating index ?

Answer 1

Absolutely not. The Database Engine Tuning Advisor has a very limited scope, only recommending indexes for a particular workload. But the actual workload may vary.

For instance, DTA would recommend index1 for a particular query. But say that table is the target of heavy data modification, so the benefits of index1 are so extremely small compared to the overhead and performance impact of maintaining index1 from the data modifications from a more significant workload. (that's just an example, but should serve in illustrating how a shotgun approach at indexing can get you into trouble)

Take DTA's recommendations as just that... recommendations. There is no substitute for the due diligence of testing out the recommended indexing in an actual production-equivalent workload.

Answer 2

The database tuning advisor can be useful for tuning specific queries or a multitude of queries.

But

Just be aware that throwing a bunch of indexes on a table can help the performance of one query, while harming the performance of another. Additionally, the more indexes a table has, the longer writes take due to having to write to multiple indexes.

If you have especially long running queries, you can reference the tuning advisor's suggestions (but try to understand why it's suggesting them before throwing them onto a table willy nilly).

One other (potentially) nice feature is providing a trace file for the advisor to parse. If you are able to get a "like production" trace, that could potentially give you beneficial indexes to throw on several tables. By "like production" i mean if you can get a trace that represents production like behavior over a long period of time. One option is to trace production (See why this can be a bad idea https://dba.stackexchange.com/questions/818/using-sql-profiler-on-a-database-thats-in-production)... but be careful this can be pretty heavy handed. Here's a tutorial on how to do a smaller footprint trace without utilizing the UI: http://tranpeter.blogspot.com/2013/10/sql-server-proffer-offline.html