In the latest versions of SQL Server, there are some amazing new features. Many of them allow us to expand the capabilities of SQL Server, but some are added to allow us to dive more deeply into how the system works. A couple of the newer DMVs are fantastic tools to allow us to find indexes that are unused, duplicate, or unneeded. If you're not using sys.dm_db_index_usage_stats or sys.dm_db_missing_index_details, you should dig into a little and learn how these work. However running a diagnostic query to find unused indexes and then dropping those indexes is a bad idea. You need to ensure that those indexes aren't rarely, or lightly used.

Read the rest of "Review Your Indexing" at SQLServerCentral