How good are the proposed additions of missing indexes to SSMS?

Question

We used the Include Actual Execution Plan feature in SSMS to optimize our requests. It is sometimes recommended to add an index. I am curious how good / accurate this recommendation is. For example, if I analyze a query and there are no indexes that, in his opinion, should be added, I am sure that no additional index can improve performance?

If this is not the case, are there any better tools that tell us the indexes that should be added (or other optimizations that can be made)?

We are using SQL Server 2012.

Answer 1

In general, never use the index suggested by this function, unchecked. These indexes consider only one query and do not take into account the entire load. In addition, they are often overloaded with columns that are not needed to improve performance.

An invalid index warning is a good indication that an additional index will improve performance. However, use them only as a starting point. Always check to make sure they make sense and make sure there is no other index that makes more sense.

Some people recommend a database configuration advisor. He reviews the overall workload before giving recommendations. But even there, I would prefer a manual review, or at least a good performance test, before applying them to production.