Edit

Share via

Tune nonclustered indexes with missing index suggestions

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

The missing indexes feature is a lightweight tool for finding missing indexes that might significantly improve query performance. This article describes how to use missing index suggestions to effectively tune indexes and improve query performance.

Limitations of the missing index feature

When the query optimizer generates a query plan, it analyzes what the best indexes are for a particular filter condition. If the best indexes don't exist, the query optimizer still generates a query plan using the least-costly access methods available, but also stores information about these indexes. The missing indexes feature enables you to access that information about best possible indexes so you can decide whether they should be implemented.

Query optimization is a time sensitive process, so there are limitations to the missing index feature. Limitations include:

  • Missing index suggestions are based on estimates made during the optimization of a single query, prior to query execution. Missing index suggestions aren't tested or updated after query execution.
  • The missing index feature suggests only nonclustered disk-based rowstore indexes. Unique and filtered indexes aren't suggested.
  • Key columns are suggested, but the suggestion doesn't specify an order for those columns. For information on ordering columns, see the Apply missing index suggestions section of this article.
  • Included columns are suggested, but SQL Server performs no cost-benefit analysis regarding the size of the resulting index when a large number of included columns are suggested.
  • Missing index requests might offer similar variations of indexes on the same table and column(s) across queries. It's important to review index suggestions and combine where possible.
  • Suggestions aren't made for trivial query plans.
  • Cost information is less accurate for queries involving only inequality predicates.
  • Suggestions are gathered for a maximum of 600 missing index groups. After this threshold is reached, no more missing index group data is gathered.

Due to these limitations, missing index suggestions are best treated as one of several sources of information when performing index analysis, design, tuning, and testing. Missing index suggestions are not prescriptions to create indexes exactly as suggested.

Note

Azure SQL Database offers automatic index tuning. Automatic index tuning uses machine learning to learn horizontally from all databases in Azure SQL Database through AI and dynamically improve its tuning actions. Automatic index tuning includes a verification process to ensure there is a positive improvement to the workload performance from indexes created.

View missing index recommendations

The missing indexes feature consists of two components:

  • The MissingIndexes element in the XML of execution plans. This allows you to correlate indexes that the query optimizer considers missing with the queries for which they are missing.
  • A set of dynamic management views (DMVs) that can be queried to return information about missing indexes. This allows you to view all of the missing index recommendations for a database.

View missing index suggestions in execution plans

Query execution plans can be generated or obtained in multiple ways:

For example, you can use the following query to generate missing index requests against the AdventureWorks sample database.

SELECT City, StateProvinceID, PostalCode  
FROM Person.Address as a
JOIN Person.BusinessEntityAddress as ba on
    a.AddressID = ba.AddressID
JOIN Person.Person as  p on
    ba.BusinessEntityID = p.BusinessEntityID
WHERE p.FirstName like 'K%' and
    StateProvinceID = 9;  
GO

To generate and view the missing index requests:

  1. Open SSMS and connect a session to your copy of the AdventureWorks sample database.

  2. Paste the query into the session and generate an estimated execution plan in SSMS for the query by selecting the Display Estimated Execution Plan toolbar button. The execution plan will display in a pane in the current session. A green Missing Index statement will appear near the top of the graphic plan.

    A graphic execution plan in SQL Server Management Studio. A missing index request appears at the top of the missing index request in green font, directly below the Transact-SQL statement.

    A single execution plan might contain multiple missing index requests, but only one missing index request can be displayed in the graphic execution plan. One option to view a full list of missing indexes for an execution plan is to view the execution plan XML.

  3. Right-click on the execution plan and select Show Execution Plan XML... from the menu.

    Screenshot showing the menu that appears after right-clicking on an execution plan.

    The execution plan XML will open as a new tab inside SSMS.

    Note

    Only a single missing index suggestion will be shown in the Missing Index Details... menu option, even if multiple suggestions are present in the execution plan XML. The missing index suggestion displayed might not be the one with the highest estimated improvement for the query.

  4. Display the Find dialog by using the CTRL+f shortcut.

  5. Search for MissingIndex.

    Screenshot of the XML for an execution plan. The Find dialog has been opened, and the term MissingIndex has been searched for in the document.

    In this example, there are two MissingIndex elements.

    • The first missing index suggests the query might use an index on the Person.Address table that supports an equality search on the StateProvinceID column, which includes two more columns, City and PostalCode'. At the time of optimization, the query optimizer believed that this index might reduce the estimated cost of the query by 34.2737%.
    • The second missing index suggests the query might use an index on the Person.Person table that supports an inequality search on the FirstName column. At the time of optimization, the query optimizer believed that this index might reduce the estimated cost of the query by 18.1102%.

Each disk-based nonclustered index in your database takes up space, adds overhead for inserts, updates, and deletes, and might require maintenance. For these reasons, it's a best practice to review all the missing index requests for a table and the existing indexes on a table before adding an index based on a query execution plan.

View missing index suggestions in DMVs

You can retrieve information about missing indexes by querying the dynamic management objects listed in the following table.

Dynamic management view Information returned
sys.dm_db_missing_index_group_stats (Transact-SQL) Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.
sys.dm_db_missing_index_groups (Transact-SQL) Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group.
sys.dm_db_missing_index_details (Transact-SQL) Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
sys.dm_db_missing_index_columns (Transact-SQL) Returns information about the database table columns that are missing an index.

The following query uses the missing index DMVs to generate CREATE INDEX statements. The index creation statements here are intended to assist you in crafting your own DDL after examining all of the requests for the table along with existing indexes on the table.

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1), 
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) 
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' + 
        CONVERT (varchar, mig.index_group_handle) + '_' + 
        CONVERT (varchar, mid.index_handle) + ' ON ' + 
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') + 
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' + 
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON 
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON 
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

This query orders the suggestions by a column named estimated_improvement. The estimated improvement is based on a combination of:

  • The estimated query cost of queries associated with the missing index request.
  • The estimated impact of adding the index. This is an estimate of how much the nonclustered index would reduce the query cost.
  • The sum of executions of query operators (seeks and scans) that have been run for queries associated with the missing index request. As we discuss in persist missing indexes with Query Store, this information is periodically cleared.

Note

The Index-Creation script in Microsoft's Tiger Toolbox examines missing index DMVs and automatically removes any redundant suggested indexes, parses out low impact indexes, and generates index creation scripts for your review. As in the query above, it does NOT execute index creation commands. The Index-Creation script is suitable for SQL Server and Azure SQL Managed Instance. For Azure SQL Database, consider implementing automatic index tuning.

Review Limitations of the missing index feature and how to apply missing index suggestions before creating indexes, and modify the index name to match the naming convention for your database.

Persist missing indexes with Query Store

Missing index suggestions in DMVs are cleared by events such as instance restarts, failovers, and setting a database offline. Additionally, when the metadata for a table changes, all missing index information about that table is deleted from these dynamic management objects. Table metadata changes can occur when columns are added or dropped from a table, for example, or when an index is created on a column of a table. Performing an ALTER INDEX REBUILD operation on an index on a table also clears missing index requests for that table.

Similarly, execution plans stored in the plan cache are cleared by events such as instance restarts, failovers, and setting a database offline. Execution plans might be removed from cache due to memory pressure and recompilations.

Missing index suggestions in execution plans can be persisted across these events by enabling Query Store.

The following query retrieves the top 20 query plans containing missing index requests from Query Store based on a rough estimate of total logical reads for the query. The data is limited to query executions within the past 48 hours.

SELECT TOP 20
    qsq.query_id,
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,    
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on 
    qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on 
    qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE    
    qsp.query_plan like N'%<MissingIndexes>%'
    and qsrsi.start_time >= DATEADD(HH, -48, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC;
GO

Apply missing index suggestions

To effectively use missing index suggestions, follow nonclustered index design guidelines. When tuning nonclustered indexes with missing index suggestions, review the base table structure, carefully combine indexes, consider key column order, and review included column suggestions.

Review the base table structure

Before creating nonclustered indexes on a table based on missing index suggestions, review the table's clustered index.

One way to check for a clustered index is by using the sp_helpindex system stored procedure. For example, we can view a summary of the indexes on the Person.Address table by executing the following statement:

exec sp_helpindex 'Person.Address';
GO

Review the index_description column. A table can have only one clustered index. If a clustered index has been implemented for the table, the index_description will contain the word 'clustered'.

Screenshot of the sp_helpindex being run against the `Person.Address` table in the AdventureWorks database. The table returns four indexes. The fourth index has an index_description that shows that it's a clustered, unique primary key.

If no clustered index is present, the table is a heap. In this case, review if the table was intentionally created as a heap to solve a specific performance problem. Most tables benefit from clustered indexes: often, tables are implemented as heaps by accident. Consider implementing a clustered index based on the clustered index design guidelines.

Review missing indexes and existing indexes for overlap

Missing indexes might offer similar variations of nonclustered indexes on the same table and column(s) across queries. Missing indexes might also be similar to existing indexes on a table. For optimal performance, it is best to examine missing indexes and existing indexes for overlap and avoid creating duplicate indexes.

Script out existing indexes on a table

One way to examine the definition of existing indexes on a table is to script out the indexes with Object Explorer Details:

  1. Connect Object Explorer to your instance or database.
  2. Expand the node for the database in question in Object Explorer.
  3. Expand the Tables folder.
  4. Expand the table for which you would like to script out indexes.
  5. Select the Indexes folder.
  6. If the Object Explorer Details pane is not already open, on the View menu, select Object Explorer Details or press F7.
  7. Select all indexes listed on the Object Explorer Details pane with the shortcut CTRL+a.
  8. Right-click anywhere in the selected region and select the menu option Script index as, then CREATE To and New Query Editor Window.

Screenshot of scripting out all indexes on a table using the Object Explorer Details pane in SSMS.

Review indexes and combine where possible

Review the missing index recommendations for a table as a group, along with the definitions of existing indexes on the table. Remember that when defining indexes, generally equality columns should be put before the inequality columns, and together they should form the key of the index. To determine an effective order for the equality columns, order them based on their selectivity: list the most selective columns first (leftmost in the column list). Unique columns are most selective, while columns with many repeating values are less selective.

Included columns should be added to the CREATE INDEX statement using the INCLUDE clause. The order of included columns doesn't affect query performance. Therefore, when combining indexes, included columns might be combined without worrying about order. Learn more in included columns guidelines.

For example, you might have a table, Person.Address, with an existing index on the key column StateProvinceID. You might see missing index recommendations for the Person.Address table for the following columns:

  • EQUALITY filters for StateProvinceID and City
  • EQUALITY filters for StateProvinceID and City, INCLUDE PostalCode

Modifying the existing index to match the second recommendation, an index with keys on StateProvinceID and City including PostalCode, would likely satisfy the queries that generated both index suggestions.

Tradeoffs are common in index tuning. It is likely that for many datasets, the City column is more selective than the StateProvinceID column. However, if our existing index on StateProvinceID is heavily used, and other requests largely search on both StateProvinceID and City, it is lower overhead for the database in general to have a single index with both columns in the key, leading on StateProvinceID, although it is not the most selective column.

Indexes might be modified in multiple ways:

The order of index keys matters when combining the index suggestions: City as a leading column is different from StateProvinceID as a leading column. Learn more in nonclustered index design guidelines.

When creating indexes, consider using online index operations when they are available.

While indexes can dramatically improve query performance in some cases, indexes also have overhead and management costs. Review general index design guidelines to help assess the benefit of indexes before creating them.

Verify if your index change is successful

It's important to confirm if your index changes have been successful: is the query optimizer using your indexes?

One way to validate your index changes is to use Query Store to identify queries with missing index requests. Note the query_id for the queries. Use the Tracked Queries view in Query Store to check if execution plans have changed for a query and if the optimizer is using your new or modified index. Learn more about Tracked Queries in start with query performance troubleshooting.

Related content

Learn more about index and performance tuning in the following articles: