Too Many Indexes in SQL Server?

The saying “Too much of a good thing is not always good” holds true when discussing indexes. Too many indexes create additional overhead associated with the extra amount of data pages that the Query Optimizer needs to go through. Also, too many indexes require too much space and add to the time it takes to accomplish maintenance tasks.

Still, the Data Tuning Wizard typically recommends a large number of indexes, especially when analyzing a workload with many queries. The reason behind this is because queries are analyzed on an individual basis. It is a good practice to incrementally apply indexes as needed, always keeping a baseline to compare if the new index improves query performance.

SQL Server 2012 provides several Dynamic Management Views (DMV) to obtain index usage information. Some of these DMVs include:

• sys.dm_db_missing_index_details: Returns detailed information about a missing index.
• sys.dm_db_missing_index_columns: Returns information about the table columns that are missing an index.
• sys.dm_db_missing_index_groups: Returns information about a specific group of missing indexes.
• sys.dm_db_missing_index_group_stats: Returns summary information about missing index groups.
• sys.dm_db_index_usage_stats: Returns counts of different types of index operations and the time each type of operation was last performed.
• sys.dm_db_index_operational_stats: Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
• sys.dm_db_index_physical_stats: Returns size and fragmentation information for the data and indexes of the specified table or view.

For example, to obtain a list of indexes that have been used and those that have not been used by user queries, query the sys.dm_db_index_usage_stats DMV. From the list of indexes that have been used you can obtain important statistics that help you fine tune your indexes. Some of this information includes index access patterns such index scans, index seeks, and index bookmark lookups.

To obtain a list of indexes that have been used by user queries, execute the following script:

SELECT

 SO.name Object_Name,

 SCHEMA_NAME(SO.schema_id) Schema_name,

 SI.name Index_name,

 SI.Type_Desc,

 US.user_seeks,

 US.user_scans,

 US.user_lookups,

 US.user_updates 

FROM sys.objects AS SO

 JOIN sys.indexes AS SI

  ON SO.object_id = SI.object_id

 INNER JOIN sys.dm_db_index_usage_stats AS US

  ON SI.object_id = SI.object_id  

  AND SI.index_id = SI.index_id

WHERE

 database_id=DB_ID(‘AdventureWorks’)

 SO.type = ‘u’

 AND SI.type IN (1, 2)

 AND (US.user_seeks > 0 OR US.user_scans > 0 OR US.user_lookups > 0 );

To obtain a list of indexes that have not been used by user queries, execute the following script:

SELECT

 SO.Name TableName,

 SI.name IndexName,

 SI.Type_Desc IndexType

 US.user_updates

FROM sys.objects AS SO

 INNER JOIN sys.indexes AS SI

  ON SO.object_id = SI.object_id

 LEFT OUTER JOIN sys.dm_db_index_usage_stats AS US   

  ON SI.object_id = US.object_id  

  AND SI.index_id = US.index_id

WHERE 

 database_id=DB_ID(‘AdventureWorks’)

 SO.type = ‘u’

 AND SI.type IN (1, 2)

 AND (US.index_id IS NULL)

 OR  (US.user_seeks = 0 AND US.user_scans = 0 AND US.user_lookups = 0 );

Indexes that are not used by user queries should be dropped, unless they have been added to support mission critical work that occurs at specific points in time, such as monthly or quarterly data extracts and reports. Unused indexes add overhead to insert, delete, and update operations as well as index maintenance operations. Index usage statistics are initialized to empty when the SQL Server service restarts. The database is detached or shutdown when the AUTO_CLOSE property is turned on.