How to Fix Missing Indexes in SQL Server | Ultimate Guide

Indexes are critical for optimizing the performance of queries in SQL Server. However, over time indexes can become outdated or even go missing entirely due to various issues. Tracking down and fixing missing indexes is an important database administration task to maintain peak SQL Server performance.

In this comprehensive guide, we will dive deep into how to identify and fix missing indexes in SQL Server. We will look at what causes indexes to go missing, techniques for finding missing indexes, and best practices for correcting index issues.

What Causes Missing Indexes in SQL Server?

There are several common reasons that indexes may become missing in a SQL Server database:

• Accidental Index Deletion: Database administrators may accidentally drop or delete an index during maintenance operations. This instantly causes the index to go missing.
• Database Restarts: When an SQL Server database restarts or detaches/attaches, all indexes are rebuilt. If an error occurs during this process, some indexes could become missing.
• Schema Changes: Operations like splitting a table or column changes can cause associated indexes to be dropped. New indexes will need to be created.
• Corruption: Database corruption can damage index structures causing indexes to be marked as missing.
• Column Deletion: If a column used in an index is deleted, the index will be dropped and appear missing.
• Manual Maintenance: Manual processes like index rebuilds can also lead to indexes unintentionally becoming missing if errors occur.
• Performance Problems: Severely fragmented indexes that cause performance issues are sometimes dropped, which can inadvertently remove needed indexes.

These types of issues can cause indexes that were once defined and functioning to no longer be present on a table. This leads to degraded SQL Server performance.

How to Identify Missing Indexes

The first step in fixing missing indexes is to identify indexes that have gone missing. There are several techniques for detecting missing indexes:

• sys.dm_db_index_usage_stats: This DMV lists all indexes on a database’s tables along with usage statistics. Any indexes not appearing can be considered missing.
• sys.dm_db_missing_index_details: This DMV provides details on missing indexes including the table, column, and usage information. Very useful for identifying needed missing indexes.
• Database Tuning Advisor: The tuning advisor tool analyzes database workload and provides recommendations about missing indexes that can enhance performance.
• Query Store: The query store tracks query performance over time. Missing index suggestions are provided for underperforming queries.
• Execution Plans: The actual execution plan output displays missing index warnings when a more optimal plan could be achieved if certain indexes existed.
• Third-Party Tools: Specialized tools like SQL Sentry and ApexSQL also have diagnostic capabilities to detect and report missing index candidates.

By leveraging these techniques, you can comprehensively find and verify any missing index candidates in a SQL Server database.

Manual Method to Find Missing Indexes in SQL Server

Execute the entire operation manually:

SELECT migs.user_seeks as [Estimated Index Uses],
migs.avg_user_impact [Estimated Index Impact %],
migs.avg_total_user_cost [Estimated Avg Query Cost],
db_name(mid.database_id) AS DatabaseID,
OBJECT_SCHEMA_NAME (mid.OBJECT_ID,mid.database_id) AS [SchemaName], OBJECT_NAME(mid.OBJECT_ID,mid.database_id) AS [TableName],
'CREATE INDEX [IX_' +
+
OBJECT_NAME (mid.OBJECT_ID,mid.database_id)
+
REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''),', ','-'), '[',''),']','')
+ CASE
WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL
THEN
ELSE
END
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns,''),', ','-'), 'T', ''), ']','') + ']'
+
+
'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 +
') WITH (MAXDOP =?, FILLFACTOR=?,
ONLINE=?, SORT_IN_TEMPDB=?);', '') AS [Create TSQL],
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.unique_compiles,
migs.last_user_seek
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_issing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
ORDER BY [Estimated Index Uses] DESC OPTION (RECOMPILE):

How to Rebuild Missing Indexes

Once one or more missing indexes have been identified, the next step is to rebuild them. Rebuilding a missing index is similar to creating a new index, except it utilizes the ALTER INDEX syntax rather than CREATE INDEX since the index metadata still exists in the database:

ALTER INDEX [index_name] ON [schema].[table] REBUILD;

This will recreate the physical index structure using the existing index definition.

It is important when rebuilding a missing index to ensure all of the original index columns and options match the index’s column list and parameters when it was created. The best practice is to script out any missing indexes to retain their full definition before dropping.

If the original index DDL creation script is not available, the index columns and options can be determined by querying sys. indexes and sys.index_columns.

After the missing indexes have been rebuilt, queries relying on those indexes will once again have optimal performance.

How to Proactively Prevent Missing Indexes

While rebuilding missing indexes resolves the performance impact, the bigger goal is to prevent indexes from going missing in the first place. Some best practices to avoid missing index issues include:

• Carefully Test Index Maintenance: Use lower environments to thoroughly test major index maintenance to avoid unintended missing indexes.
• Script Index Changes: Always script out indexes before making any changes so they can be easily reverted or rebuilt if issues occur.
• Disable Automatic Indexes: Disable any auto-create index options to reduce uncontrolled index changes.
• Monitor Fragmentation: Keep index fragmentation low to avoid rebuilding-related missing indexes.
• Limit Manual Changes: Minimize frequent manual index alterations which increase the risk of human error or corruption.
• Enable Trace Flags: Trace flags like 2562 log all index operations to capture any related errors.
• Practice Agreegressive Indexing: Define indexes proactively based on usage patterns rather than reactively adding indexes.

Applying these best practices can minimize the chances of production indexes being deleted or going missing unexpectedly.

How Missing Indexes Affect Query Performance

To understand why finding and fixing missing indexes is so critical, let’s analyze how they affect query response times from a mathematical perspective.

The basic formula for query execution time is:

Execution Time = Index Seek/Scan Time + Data Access Time

Total query time equals the time used in seek/scan operations to find rows based on indexes plus the time needed to actually retrieve row data.

If an index is missing that could have been used to eliminate a scan, the formula becomes:

Execution Time = Table Scan Time + Data Access Time

By introducing a full scan, execution time increases significantly, often by multiple orders of magnitude for larger tables. This simple dynamic illustrates the massive impact missing indexes can have on query response times and overall SQL Server performance.

Tools for Automating Missing Index Detection

Given the vital performance impact of missing indexes, many database administrators choose to monitor for and fix index issues using scheduling tools automatically. Some options include:

• SQL Server Maintenance Plans: Native tool that can run T-SQL scripts to detect and fix missing indexes on a schedule.
• PowerShell Scripts: PowerShell provides rich scripting capabilities to build automated index maintenance routines.
• DBCC CHECKDB: Can integrate CHECKDB with REINDEX option to rebuild any corrupt indexes.
• SQL Server Agent Jobs: Agent jobs allow flexible scheduling and automation of complex missing index restore processes.
• Third-Party Tools: Specialized tools offer streamlined mechanisms to detect and fix plan issues like missing indexes.

Automating index integrity checks ensures optimal SQL Server performance by continuously detecting and correcting index-related problems like missing indexes.

FAQs – Frequently Asked Questions and Answers

1. What is the difference between a missing index and a fragmented index?

Answer: A missing index is an index that does not exist. A fragmented index is an index that is damaged or does not contain all of the data that it should.

2. What is a missing index?

Answer: A missing index is an index that does not exist, but should.

3. Common mistakes to avoid when fixing missing indexes?

Answer: Not identifying the cause of the missing index, rebuilding indexes on tables with low data change rates, and not testing the impact of fixing a missing index on other queries.

To Conclude

Fixing missing indexes can significantly improve the performance of SQL Server queries. Consistently monitoring and maintaining healthy database indexes is a key responsibility of all SQL Server database administrators. 

The guidance provided in this article will equip you with the knowledge to diagnose, repair, and prevent missing index occurrences.