Indexes are supposed to make SQL Server faster – so why do so many databases end up slower, bloated, and harder to maintain when they have more of them? It usually comes down to misapplied indexes rather than missing ones. There may be too many that are too wide, tuned with settings that don’t fit the workload, or built on assumptions that stopped being true years ago.
This guide walks through the most common SQL Server index tuning mistakes seen in production environments, such as over-indexing, oversized
INCLUDElists, unnecessary fill factor settings, misuse ofSORT_IN_TEMPDB, over-aggressive index maintenance – and the myth that heaps are a shortcut to speed. Features real examples.
I think this serves as a reasonable overview of the topic. You can certainly get into more nuance on a number of the topics, but this is a good starting point.