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’)
AND 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.
No comments:
Post a Comment