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.