Share via

Using the SQL Server Performance Dashboard Missing Indexes Report

  • Article

The Microsoft SQL Server Performance Dashboard contains a drill-down report detailing information about missing indexes. Although you might think that this report makes it easy to implement new indexes and improve the performance of your database queries, it’s not that simple. Over-indexing a database table can lead to bigger performance problems such as having to update more indexes during write operations. The Missing Indexes report is useful for identifying candidates for new indexes. Index tuning is still a practice that requires as much art as automation. The process of generating the data stored in the DMVs has its own limitations (from Books Online):

· It is not intended to fine tune an indexing configuration.

· It cannot gather statistics for more than 500 missing index groups.

· It does not specify an order for columns to be used in an index.

· For queries involving only inequality predicates, it returns less accurate cost information.

· It reports only include columns for some queries, so index key columns must be manually selected.

· It returns only raw information about columns on which indexes might be missing.

· It can return different costs for the same missing index group that appears multiple times in XML Showplans.

It’s important that you read the documentation in SQL Server Books Online regarding the process of finding missing indexes before using the data from this report.

The Missing Indexes report can be useful to sift through the data in the missing indexes DMVs to find the top 10 or 20 necessary missing indexes. Be aware that the missing index process can potentially generate a lot of data. Many production databases will rack up several hundred missing indexes within a day or so of operation. Many of these may not be necessary.

Once you’ve identified a group of candidate indexes, the SQL Server Database Tuning Advisor is a useful tool for determining if the index will improve the query plan. Please read the link to Books Online for more information.

Before you use this report to implement new indexes in a database, it is import to understand the source of the report data. This report uses the data in the SQL query below from the DMVs data management views dm_db_missing_index_groups, dm_db_missing_index_group_stats and dm_db_missing_index_details:

select d.database_id, d.object_id, d.index_handle, d.equality_columns,
d.inequality_columns, d.included_columns, d.statement as fully_qualified_object,
gs.*
from sys.dm_db_missing_index_groups g
join sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle
join sys.dm_db_missing_index_details d on g.index_handle = d.index_handle

Here’s a list of the columns and descriptive information displayed in the report from SQL Server Books Online (edited for brevity):

Column name

Description

database_id

Identifies the database where the table with the missing index resides.

object_id

Identifies the table where the index is missing.

index_handle

Identifies a particular missing index. The identifier is unique across the server. index_handle is the key of this table.

equality_columns

Comma-separated list of columns that contribute to equality predicates of the form:

table.column = constant_value

inequality_columns

Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form:

table.column > constant_value

included_columns

Comma-separated list of columns needed as covering columns for the query.

statement

Name of the table where the index is missing.

group_handle

Identifies a group of missing indexes. This identifier is unique across the server. In SQL Server 2005, an index group contains only one index.

unique_compiles

Number of compilations and recompilations that would benefit from this missing index group. Compilations and recompilations of many different queries can contribute to this column value.

user_seeks

Number of seeks caused by user queries that the recommended index in the group could have been used for.

user_scans

Number of scans caused by user queries that the recommended index in the group could have been used for.

last_user_seek

Date and time of last seek caused by user queries that the recommended index in the group could have been used for.

last_user_scan

Date and time of last scan caused by user queries that the recommended index in the group could have been used for.

avg_total_user_cost

Average cost of the user queries that could be reduced by the index in the group.

avg_user_impact

Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

system_seeks

Number of seeks caused by system queries, such as auto stats queries, that the recommended index in the group could have been used for.

system_scans

Number of scans caused by system queries that the recommended index in the group could have been used for.

last_system_seek

Date and time of last system seek caused by system queries that the recommended index in the group could have been used for.

last_system_scan

Date and time of last system scan caused by system queries that the recommended index in the group could have been used for.

avg_total_system_cost

Average cost of the system queries that could be reduced by the index in the group.

avg_system_impact

Average percentage benefit that system queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

Here are links to the detailed information on these DMVs in Books Online:

sys.dm_db_missing_index_groups

dm_db_missing_index_group_stats

sys.dm_db_missing_index_details

MissingIndexReportSample.jpg

Comments