Using the SQL Server Performance Dashboard Missing Indexes Report



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