SQL Server Indexes – Is It Safe to Add Missing Indexes Blindly?

indexsql serversql-server-2008ssms

I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster query every time as far as I can tell.

Any reason why I shouldn't continue to do this?

Best Answer

Many reasons.

One of the biggest that I can think of is that the missing index DMVs don't take into account existing indexes.

Example:

You have a table with ColA, ColB, ColC.

Currently you have an index on ColA. The missing index DMV will suggest you add an index on (ColA, ColB). This may be correct, but the smart thing to do is to add ColB as a second key on the existing index. Otherwise you have duplicate coverage and wasted space and overhead.

Similarly, if you have an index on ColB INCLUDE (ColA), it may suggest an index on ColB INCLUDE (ColC). Again the smart thing to do is to add ColC to the include list in the existing index.

The suggested indexes have an extremely narrow view - they only look at a single query, or a single operation within a single query. They don't take into account what already exists or your other query patterns.

You still need a thinking human being to analyze the overall indexing strategy and make sure that you index structure is efficient and cohesive.

If there were no issues with just adding all the suggested indexes then there would be no need to even have them be suggested - they would be implemented automatically.

Related Question