I have been using SQL Server for over 20 years and have been working on the focused area of SQL Server Performance Tuning for over 12 years. In recent time I have been very busy doing Comprehensive Database Performance Health Check, and the client, I enjoy working the most is the one who has used in the past Database Engine Tuning Advisor.
Do not get me wrong that Database Engine Tuning Advisor is not a bad tool if you are in the year 2000. However, as the time has progressed, SQL Server has also advanced quite a bit and now the SQL Server Performance Tuning World is way above and beyond just creating indexing and statistics.
If you have used Database Engine Tuning Advisor (DETA or as commonly known as DTA) in the past, you must be aware that it most of the time only gives following two suggestions: 1) Create Index 2) Create Statistics.
This particular nature of DETA or DTA in fact actually have created a mess for quite a many different SQL Server users. I have seen quite a few organizations where overuse of DTA has ended up a table with 100s of indexes and eventually it has become impossible to use those tables.
In one of the recent project, where the customer complained about dead slow performance, after careful observation and analysis, I removed over 100 indexes and statistics which started with the prefix _DTA and we were able to see an instant performance improvement for SQL Server.
I personally prefer that there are no more than 5 indexes on a single table. If you have to create a new index, I strongly suggest you drop one index and keep the total number of indexes per table to 5. Here is the script which you can use to identify missing index or remove unused indexes.
Though, many may disagree. I suggest that you say no to Database Engine Tuning Advisor and adopt DMVs which gives metadata of the data.
Reference: Pinal Dave (https://blog.sqlauthority.com)