Can we completely depend on the DBMS kernel tuning adviser?

Question

I read about the Advisor Engine Tuning Advisor and how it can offer an index for this query. My question is: can I completely depend on these suggestions when creating the index?

Answer 1

A database tuning advisor can be useful for tuning specific queries or multiple queries.

But

Just keep in mind that throwing a bunch of indexes into a table can help the performance of one query while harming the performance of another. In addition, the more indexes the table has, the longer the record will be due to the need to write to multiple indexes.

If you have particularly long requests, you can refer to the suggestions of the configuration consultant (but try to understand why he offers them before throwing them on the willy nilly table).

Another (potentially) nice feature is the provision of a trace file for parsing the EA. If you can get a “as production” trace, this could potentially give you profitable indexes to drop a few tables. By "similar products" I mean if you can get a footprint that represents production as behavior over a long period of time. One option is to track production (see Why this might be a bad idea https://dba.stackexchange.com/questions/818/using-sql-profiler-on-a-database-thats-in-production ) ... but be careful, it can be pretty hard. Here is a tutorial on how to make a smaller footprint without using the user interface: http://tranpeter.blogspot.com/2013/10/sql-server-proffer-offline.html

Answer 2

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

For example, DTA would recommend index1 for a specific request. But say that the table is the goal of significant data modification, so the advantages of index1 so small that index1 from data changes from a more significant workload is supported compared to overhead and performance index1 . (this is just an example, but should serve to illustrate how a shotgun approach when indexing can cause you problems)

Take the DTA recommendations as soon as these ... recommendations . There is no substitute for the due diligence of testing recommended indexing in the actual work equivalent workload.