DB-유

Finding Missing Indexes with SQL Server DMVs

· mssql, performance, indexing

Every time the query optimiser compiles a plan and notices that an index would have made a seek possible where it had to scan, it records that wish in a set of dynamic management views. These "missing index" DMVs are a quick way to find the biggest indexing gaps on an instance you did not design, and they are surprisingly accurate at pointing to the tables that hurt. They are also frequently abused: people create every suggestion verbatim and end up with 40 near-duplicate indexes on one table.

I inherit instances I didn't design a lot, and this is where I start. Below: how I query the DMVs, how to read the numbers, and how to turn a suggestion into a sensible index on SQL Server 2014 through 2017.

You need VIEW SERVER STATE, and an instance that's been up long enough to see a representative workload. The DMVs are cleared on restart and when a table's metadata changes.

Ranking the suggestions

There are three views joined by index_group_handle and index_handle. The standard "improvement measure" multiplies how much the optimiser thinks a query would improve by how often it ran:

SELECT TOP 25
    ROUND(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 0) AS improvement_measure,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.avg_user_impact,
    migs.last_user_seek
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;

The columns to read:

  • equality_columns: columns used with =. These go first in the key.
  • inequality_columns: columns used with >, <, BETWEEN, LIKE 'x%'. These go after the equality columns.
  • included_columns: columns only selected, not filtered. These become INCLUDE.
  • avg_user_impact: the estimated percentage cost reduction for the queries involved.
  • user_seeks: how many times a query would have used the index.

Anything with an improvement measure in the tens of thousands or higher and hundreds of seeks is worth a look. Anything with user_seeks of 3 is noise.

Which queries want it?

The DMV does not tell you which query produced the wish. Since SQL Server 2005 the missing index information is embedded in cached execution plans, so search the plan cache:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP 20
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan.exist('//MissingIndex[@Table="[Orders]"]') = 1
ORDER BY qs.total_worker_time DESC;

Replace [Orders] with the table from the first query. Open the plan XML in SSMS and the green "Missing Index" hint at the top links to the same recommendation.

Look at what's already there

Before creating anything, list the existing indexes on the table:

SELECT i.name, i.type_desc,
       STUFF((SELECT ', ' + c.name FROM sys.index_columns ic
              JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
              WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
              ORDER BY ic.key_ordinal FOR XML PATH('')), 1, 2, '') AS key_columns,
       STUFF((SELECT ', ' + c.name FROM sys.index_columns ic
              JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
              WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
              FOR XML PATH('')), 1, 2, '') AS included_columns
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.Orders');

Very often the suggestion is an existing index plus one included column. I'd widen the existing index nearly every time rather than add a second one, since every extra index slows writes. Also merge several suggestions on the same table that share leading columns into one index.

Creating the index

The DMV lists equality columns in column-id order, not selectivity order. Put the most selective equality column first if the queries always filter on all of them, otherwise order by how often each column appears alone.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Status
ON dbo.Orders (CustomerId, Status)
INCLUDE (OrderDate, TotalAmount)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);

Drop ONLINE = ON on Standard Edition before SQL Server 2016 SP1; DATA_COMPRESSION also requires Enterprise or 2016 SP1 and later.

A week later

Check that the new index is actually being used and that the missing-index row has disappeared:

SELECT OBJECT_NAME(s.object_id) AS table_name, i.name, s.user_seeks, s.user_scans, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() AND i.name = 'IX_Orders_CustomerId_Status';

If user_updates climbs quickly while user_seeks stays low, the index costs more than it saves.

Notes

The DMVs vanished. They are reset on service restart, on ALTER TABLE, and when the database goes offline. Snapshot them into a table daily if you need history.

Only 500 suggestions are kept per instance. Older ones are dropped as new ones arrive, so a noisy database can hide suggestions for a quiet one.

Suggested included columns are absurdly wide. The optimiser lists every selected column. If that is 30 columns, the query needs rewriting, not a covering index that duplicates the table.

No suggestions for a slow query. The DMVs only record cases where the optimiser considered an index seek. Queries with non-sargable predicates (WHERE YEAR(OrderDate) = 2017, functions on columns, implicit conversions) never produce suggestions. Fix the predicate first.

Treat the DMVs as a ranked list of tables to investigate, not a script to execute.