SQL Server has a concept of “missing indexes”. And no, it’s not referring to an index that used to be there but has now disappeared. Rather, the missing index concept is designed to help us improve the performance of our database.
A missing index is an index that doesn’t yet exist on your table but probably should. SQL Server actually tracks queries that would benefit from indexes and stores suggestions in a set of dynamic management views (DMVs) called the missing index DMVs.
When you run a query and the optimizer thinks that this would be way faster with an index on those columns, it logs that suggestion. Over time, these suggestions accumulate, giving you a prioritized list of indexes that could improve your database’s performance.
How SQL Server Identifies Missing Indexes
The query optimizer evaluates every query it processes. If it determines that an index could significantly reduce the cost of a query, it records details about that potential index. These are things like which table it’s for, which columns should be included, and an estimated improvement percentage.
This happens automatically in the background. You don’t need to enable anything or configure special settings. SQL Server is constantly analyzing your workload and building up a list of recommendations.
The main point here is that these are suggestions based on actual queries your system has executed. So they’re not theoretical. They’re grounded in real workload patterns.
Accessing Missing Index Information
SQL Server exposes missing index data through several DMVs:
sys.dm_db_missing_index_detailscontains the specifics about what index is needed. This includes the table, equality columns, inequality columns, and included columns.sys.dm_db_missing_index_groupslinks index details to groups (mostly for internal use).sys.dm_db_missing_index_group_statsreturns summary information about groups of missing indexes, excluding spatial indexes. It provides usage statistics which shows how many times queries would have benefited from the index and the estimated impact.sys.dm_db_missing_index_group_stats_query(SQL Server 2019+) provides query-level details, showing which specific queries would benefit from each missing index suggestion.
You can query these views together to get actionable information. For example:
SELECT
OBJECT_NAME(d.object_id) AS TableName,
d.equality_columns,
d.inequality_columns,
d.included_columns,
s.user_seeks,
s.user_scans,
s.avg_user_impact,
s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) AS ImpactScore
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
ORDER BY ImpactScore DESC;This query ranks missing indexes by their potential impact, considering how often they’d be used and how much they’d improve query performance.
Example
Let’s create a table and populate it with 100,000 rows of data:
CREATE TABLE sales_transactions (
transaction_id BIGINT PRIMARY KEY,
sale_date DATE,
store_id INT,
product_id INT,
quantity INT,
unit_price DECIMAL(10,2),
total_amount DECIMAL(10,2),
region VARCHAR(50),
payment_method VARCHAR(20)
);
GO
WITH numbers AS (
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
)
INSERT INTO sales_transactions
SELECT
n,
DATEADD(DAY, -(n % 1095), '2026-12-31'), -- Last 3 years
(n % 50) + 1, -- 50 stores
(n % 500) + 1, -- 500 products
(n % 10) + 1, -- Quantity 1-10
CAST((n % 100) + 10.00 AS DECIMAL(10,2)), -- Unit price $10-$110
CAST(((n % 10) + 1) * ((n % 100) + 10.00) AS DECIMAL(10,2)), -- Total
CASE (n % 4)
WHEN 0 THEN 'Northeast'
WHEN 1 THEN 'Southeast'
WHEN 2 THEN 'Midwest'
ELSE 'West'
END,
CASE (n % 3)
WHEN 0 THEN 'Credit Card'
WHEN 1 THEN 'Debit Card'
ELSE 'Cash'
END
FROM numbers;
GONow let’s query that data:
SELECT
region,
YEAR(sale_date) AS sale_year,
MONTH(sale_date) AS sale_month,
COUNT(*) AS transaction_count,
SUM(total_amount) AS total_revenue,
AVG(quantity) AS avg_quantity
FROM sales_transactions
WHERE sale_date >= '2025-01-01'
GROUP BY region, YEAR(sale_date), MONTH(sale_date)
ORDER BY sale_year, sale_month, region;This wouldn’t use any indexes (after all, we didn’t create any indexes). Without an appropriate index, SQL Server would need to scan the entire table.
Now let’s check for missing indexes:
SELECT
OBJECT_NAME(d.object_id) AS TableName,
d.equality_columns,
d.inequality_columns,
d.included_columns,
s.user_seeks,
s.user_scans,
s.avg_user_impact,
s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) AS ImpactScore
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
ORDER BY ImpactScore DESC;Output:
TableName equality_columns inequality_columns included_columns user_seeks user_scans avg_user_impact ImpactScore
------------------ ---------------- ------------------ ------------------------------------ ---------- ---------- --------------- -----------------
sales_transactions null [sale_date] [quantity], [total_amount], [region] 1 0 53.81 70.78559914224451
SQL Server has immediately added a missing index for us to review. To be clear, this index doesn’t exist. SQL Server didn’t go ahead and create an index for us. Rather, this is just a suggestion. It’s SQL Server’s way of recommending to us, an index that might help our query.
So looking at the result, that 53.81% average impact means SQL Server estimates this index would reduce query cost by nearly 54% for the queries that need it. Could be worth considering.
If we check the view at a later date, we might see some different numbers. To demonstrate this, I re-ran the query many times and then checked the DMV again:
TableName equality_columns inequality_columns included_columns user_seeks user_scans avg_user_impact ImpactScore
------------------ ---------------- ------------------ ------------------------------------ ---------- ---------- --------------- ----------------
sales_transactions null [sale_date] [quantity], [total_amount], [region] 109 0 53.81 7715.63030650464
This time we can see 109 user_seeks instead of just 1. Also the ImpactScore has increased significantly. However, the avg_user_impact remains the same.
Creating Indexes from Missing Index Suggestions
SQL Server doesn’t automatically create missing indexes. You’ll need to create them manually. However, there are tools that make this easier.
One approach is through SQL Server Management Studio. When you view the actual execution plan for a query, you’ll often see a green “Missing Index” hint at the top if the optimizer thinks an index would help. Right-click on this hint and select “Missing Index Details,” and SSMS opens a new query window with a pre-generated CREATE INDEX statement. You can review it, modify it if needed, and execute it.
You can also build CREATE INDEX statements programmatically by querying the missing index DMVs. For example:
SELECT
'CREATE INDEX IX_' + OBJECT_NAME(d.object_id) + '_'
+ REPLACE(REPLACE(REPLACE(COALESCE(d.equality_columns + '_', '') + COALESCE(d.inequality_columns, ''), '[', ''), ']', ''), ', ', '_')
+ ' ON ' + d.statement
+ ' (' + COALESCE(d.equality_columns + ', ', '') + COALESCE(d.inequality_columns, '') + ')'
+ CASE WHEN d.included_columns IS NOT NULL THEN ' INCLUDE (' + d.included_columns + ')' ELSE '' END
AS CreateIndexStatement,
s.avg_user_impact,
s.user_seeks + s.user_scans AS total_uses
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
ORDER BY s.avg_user_impact * (s.user_seeks + s.user_scans) DESC;This generates CREATE INDEX statements for all missing index suggestions, sorted by potential impact.
So for our missing index it shows this:
CreateIndexStatement avg_user_impact total_uses
---------------------------------------------------------------------------------------------------------------------------------------------- --------------- ----------
CREATE INDEX IX_sales_transactions_sale_date ON [demo].[dbo].[sales_transactions] ([sale_date]) INCLUDE ([quantity], [total_amount], [region]) 53.81 109
You can review the output and selectively execute the ones that make sense for your workload.
One of the nice things about the missing index feature is that once you create an index that satisfies the suggestion, SQL Server recognizes it and removes that entry from the missing index DMVs.
Should You Create Every Missing Index?
No, not at all. Just because SQL Server provides index suggestions doesn’t mean that you should always create one. Missing index suggestions can be helpful, but they’re not always appropriate. Here’s why you need to be selective:
- Indexes aren’t free. Every index consumes disk space and adds overhead to
INSERT,UPDATE, andDELETEoperations. If you create 50 indexes on a table, your writes will slow to a crawl. - The suggestions are narrow. SQL Server suggests indexes optimized for specific queries. You might get separate suggestions for indexes on (
Status,AssignedTeam) and (AssignedTeam,Status). In many cases creating both would be redundant when one could handle most scenarios. But there are a few edge cases where having both might make sense, so you would need to make that determination on a case-by-case basis. - They don’t consider existing indexes. The DMVs don’t know if you already have an index that’s “close enough” to the suggestion. You might have an index on
Statusthat’s working reasonably well, and adding the suggestedStatus/AssignedTeamindex provides minimal benefit. Or you could modify the existing index to includeAssignedTeam, although you’d need to verify that the change doesn’t negatively impact other queries using that index. - Low-frequency queries skew results. A query that runs once a month might show up with a high impact percentage, but creating an index for it doesn’t make sense when it barely affects your overall workload.
A good approach is to look at the impact score (factoring in both improvement percentage and frequency), evaluate whether existing indexes can be modified to cover the need, and consider the write penalty before creating new indexes.
When Missing Index Data Gets Reset
The missing index DMVs are volatile and get cleared in several situations, including:
- SQL Server restarts
- Database failovers (in availability groups or failover clustering)
- Taking a database offline and bringing it back online
- Running
ALTER INDEXoperations - Executing
ALTER TABLEstatements that modify table structure - Manually clearing the plan cache
- Memory pressure that forces SQL Server to reclaim memory from the DMVs
This means the data reflects your workload since whichever of these events occurred most recently, not the lifetime of your database. For systems that experience frequent failovers, maintenance operations, or memory pressure, you might not accumulate enough data to get meaningful recommendations. For stable production servers without frequent reset events, you might have months of accumulated suggestions, including many that are no longer relevant because your application has changed.
Some teams periodically capture missing index data to a permanent table so they can track trends over time and make more informed indexing decisions.
The Bottom Line
Missing indexes are SQL Server’s way of making suggestions that could improve the performance of your queries. They’re valuable guidance, but they require human judgment. But don’t just blindly implement every recommendation. Look at the suggestions, understand your workload, consider the tradeoffs, and create indexes that actually make sense for your specific situation.