Finding Missing Indexes:
|
Dynamic management object |
Information returned |
|
sys.dm_db_missing_index_group_stats |
Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes. |
|
sys.dm_db_missing_index_groups |
Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group. |
|
sys.dm_db_missing_index_details |
Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index. |
|
sys.dm_db_missing_index_columns |
Returns information about the database table columns that are missing an index. |
|
Note: |
|
When the metadata for a table changes, all missing index information about that table is deleted from these dynamic management objects. Table metadata changes can occur when columns are added or dropped from a table, for example, or when an index is created on a column of a table. |
When SQL Server is restarted, all of the missing index information is dropped.
Example using DMVs:
Query:
SELECT mig.index_group_handle,mid.index_handle, migs.avg_total_user_cost as AvgTotalUserCostThatCouldbeReduced, migs.avg_user_impact as AvgPercentageBenefit,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
--where statement = '[<DBName>].[dbo].[<TableName>]'
Finding Unused Indexes:
DMVs used: sys.dm_db_index_usage_stats
Example to find Unused Indexes:
select object_name(i.object_id) as ObjectName, i.name as [Unused Index]
from sys.indexes i
left join sys.dm_db_index_usage_stats s on s.object_id = i.object_id
and i.index_id = s.index_id
and s.database_id = db_id()
where objectproperty(i.object_id, 'IsIndexable') = 1
AND objectproperty(i.object_id, 'IsIndexed') = 1
and s.index_id is null -- and dm_db_index_usage_stats has no reference to this index
or (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0) -- index is being updated, but not used by seeks/scans/lookups
order by object_name(i.object_id) asc
In the above query, user_seeks, user_scans, and user_lookups counters indicate the usage of the particular index. If the value of the counters is 0, it means that index has never been used for any Seek, or Scan or Lookup operations.
Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view (DMV).
The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view.
The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.
When an index is used, a row is added to sys.dm_db_index_usage_stats if a row does not already exist for the index. When the row is added, its counters are initially set to zero.