SQL Server

How to identify missing SQL Server indexes

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:

  • sys.dm_db_missing_index_details
  • sys.dm_db_missing_index_groups
  • sys.dm_db_missing_index_group_stats

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.

Remarks

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.

Permissions

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.

Example

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

be implemented:

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.


Conclusion

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.

Facebooktwittergoogle_plusredditpinterestlinkedinmail