Share via

Finding Missing Indexes and Unused Indexes using DMVs

  • Article

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.

Comments

  • Anonymous
    September 24, 2014
    Vipul,

    Nice. Thanks for sharing. There is a lot of similar tracks on the Net. Yet, there is a conciseness and timelessness to yours.

    BTW, I ordered the output of the missing indexes script by migs.avg_user_impact desc and migs.avg_total_user_cost desc to see which ones I should dig a bit deeper into.

    Sincerely,

    Daniel Adeniji
  • Anonymous
    February 22, 2016


    Nice Article !

    Really this will help to people of SQL Server Community.
    I have also prepared small note on this, Script to find missing indexes in SQL Server.

    http://www.dbrnd.com/2015/11/sql-server-script-to-find-missing-indexes/