Limitations of the Missing Indexes Feature
The missing index feature has the following limitations:
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 does not suggest filtered indexes.
It can return different costs for the same missing index group that appears multiple times in XML Showplans.
It does not consider trivial query plans.
The following sections provide more information about these limitations.
Fine Tuning an Indexing Configuration
The missing indexes feature is a lightweight tool for finding missing indexes that might significantly improve query performance. It does not provide adequate information to fine tune your indexing configuration. Use Database Engine Tuning Advisor for that purpose. For more information about Database Engine Tuning Advisor, see Tuning the Physical Database Design and Related Query Tuning Features.
Maximum Number of Missing Index Groups
Statistics are gathered for a maximum of 500 missing index groups. After this threshold is reached, no more missing index group data is gathered. This threshold is not a tunable parameter and cannot be changed.
Index Column Order
The missing indexes feature does not consider the optimum sort order when suggesting an index. Consider the following sample query:
SELECT column_a, column_b
FROM table_1
WHERE column_a < 10000
AND column_b < 10000
ORDER BY column_b, column_a
The missing indexes feature may suggest an index and list column_a before column_b, but the advantageous sort order would be column_b, column_a, which matches the sort order of the query. When creating the Data Definition Language (DDL) statement to implement a missing index, choose the optimum sort order by examining the individual workload queries. For more information about ordering columns in CREATE INDEX statements based on the output received from the missing indexes feature, see Using Missing Index Information to Write CREATE INDEX Statements.
Queries Involving Only Inequality Predicates
The missing index feature uses a simplistic model to generate cost information for queries involving only inequality predicates. Consequently the cost information returned for these queries may be less accurate than the information returned for queries that involve equalities. Consider the following example query, which contains no equality predicate:
SELECT *
FROM table_1
WHERE column_a > -1
AND column_b > -1
This example query contains only an inequality predicate (WHERE column_a > -1 AND column_b > -1). Assume that all of the rows in table_1 qualify for the predicates column_a > -1 and column_b > -1. In this case, the missing indexes feature might incorrectly recommend an index on both column_a and column_b. In this case, the associated impact numbers reported by the dynamic management view sys.dm_db_missing_indexes_group_stats may also be incorrect.
Returns Only Include Columns for Some Queries
Sometimes the feature only reports column usage information for include columns. For example, the feature might report equality columns = { }, inequality columns = { }, and include columns = {b, c, …}. In this case, one of the include columns must be selected for the index key column. Use the missing index information returned for other queries that benefit from some of the same columns to determine which of the include columns should be selected for the index key.
Returns Only Raw Information
The missing indexes feature returns raw information about columns on which indexes might be missing. This means that the information returned might require additional processing before you can use the information to create an index.
For example, if the column involved is a computed column, and the computation is nondeterministic or imprecise, the CREATE INDEX statement on that column will fail. In the case of an imprecise computation, the column should be persisted first, and then you can create the index.
Returns Different Costs for the Same Missing Index Group in XML Showplans
Missing index groups can appear multiple times in XML Showplans with a different cost reported each time the missing index group appears. This can occur when different parts of a single query benefit differently from the same missing index group.
Consider the following example query:
SELECT i.CustomerID, c.ModifiedDate, City
FROM NewIndividual i JOIN NewCustomer c ON i.CustomerID = c.CustomerID
AND c.CustomerType = 'I'
JOIN NewCustomerAddress ca ON ca.CustomerID = c.CustomerID
JOIN NewAddress a ON a.AddressID = ca.AddressID
WHERE i.CustomerID = 16701
In this query, the two different join conditions on the CustomerID column might produce two identical missing index groups that contain the same index on the NewIndividual.CustomerID column. In this case, the sys.dm_db_missing_index_groups dynamic management view would have only one row for the missing index group. However, the missing index group would appear multiple times in the XML Showplan output, with different costs for the different join conditions.