How to identify missing SQL Server indexes
Microsoft SQL Server supports many useful stored procedures, functions and operators.
Some actions you can perform without using these features, but usually using them
provides more easy and effective way to accomplish the same goal. For example, in
SQL Server 2005, Microsoft introduced Dynamic Management Views (DMVs). Of course
the DMVs are supported in all later SQL Server versions also. The role of DMVs is
to return SQL Server state information that can be used by database administrators
to identify potential SQL Server performance issues. For example, the Dynamic
Management Views can be used to identify missing indexes on a table.
In this article, you will learn how to use the Dynamic Management Views to identify
missing indexes on a table. You can also find the script to identify missing SQL
Server indexes and the average percentage benefit that user queries could experience
if this missing index group will be implemented.
You can use the following Dynamic Management Views to identify missing indexes:
The sys.dm_db_missing_index_details dynamic management view returns detailed
information about missing indexes, excluding spatial indexes. This view
returns the equality_columns column which contains the comma-separated list of
recommended columns for the index.
The sys.dm_db_missing_index_groups dynamic management view returns information
about what missing indexes are contained in a specific missing index group,
excluding spatial indexes. To determine which missing index groups a particular
missing index is part of, you can query the sys.dm_db_missing_index_groups
dynamic management view by equijoining it with sys.dm_db_missing_index_details
based on the index_handle column.
The sys.dm_db_missing_index_group_stats dynamic management view returns summary
information about groups of missing indexes, excluding spatial indexes. This view
returns the avg_user_impact column which contains average percentage benefit that
user queries could experience if this missing index group will be implemented.
Information returned by these dynamic management views is not persisted. Missing index
information is kept only until SQL Server is restarted. Database administrators should
periodically make backup copies of the missing index information if they want to keep
it after server recycling.
To query these dynamic management views the users must be granted the VIEW SERVER STATE
permission or any permission that implies the VIEW SERVER STATE permission.
This is the example to get the missing indexes for the current database and the average
percentage benefit that user queries could experience if this missing index group will
SELECT DISTINCT @@ServerName as “Server_Name”
,DB_NAME(mid.[database_id]) as “DB_Name”
,OBJECT_NAME(mid.[object_id]) as “Tb_Name”
,equality_columns as “Ind_column_recommended”
,avg_user_impact as “average % benefit”
FROM sys.dm_db_missing_index_group_stats AS migs JOIN sys.dm_db_missing_index_groups AS mig
ON migs.group_handle = mig.index_group_handle JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY avg_user_impact DESC
After executing the query above, check the “average % benefit” column. We recommend
to create indexes only if the “average % benefit” column is 90% or higher and
continue monitoring once you have made your changes to ensure that your disk I/O
is optimum over the long term.
Because insert, update and delete operations may become very slow if there are many
indexes created on a table, you should create each new index very carefully. You
should remember that each index take up disk space and a greater number of indexes
result in more disk space being required to store them.
Keep in mind that once the SQL Server is restarted all the information related to
missing indexes is lost. So, you should periodically make backup copies of the missing
index information if you want to keep it after server recycling.