Missing Index Query Text: Linking Suggestions to Their Queries

Missing index query text tells you which statement asked for an index, and that changes how you read the suggestion. A recommendation without its query is a guess. With the query next to it, you can review a real problem.

A suggestion with no author

Somebody runs a missing index report and finds forty green suggestions. The manager says, “Create them all.” You should pause. Each suggestion came from some statement, and nobody has said which one, how often it runs, or whether it matters.

SQL Server keeps the answer. Let me build a small workload that asks for an index, then walk from the suggestion back to the query. Use a test database. The demo creates two tables and a procedure, and the last block removes them.

Build a workload that asks for an index

Orders has 50,000 rows and only a primary key. The procedure runs two statements. The first counts all orders. The second looks up one customer’s recent orders, which needs an index on CustomerId and OrderDate.

DROP PROCEDURE IF EXISTS dbo.GetCustomerOrders;
DROP TABLE IF EXISTS dbo.Orders, dbo.Customers;

CREATE TABLE dbo.Customers (CustomerId int NOT NULL CONSTRAINT PK_Customers PRIMARY KEY, CustomerName varchar(50) NOT NULL);

CREATE TABLE dbo.Orders (
    OrderId int NOT NULL CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED,
    CustomerId int NOT NULL,
    OrderDate date NOT NULL,
    TotalDue decimal(12,2) NOT NULL);

INSERT dbo.Customers (CustomerId, CustomerName)
SELECT TOP (500) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), CONCAT('Customer ', ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
FROM sys.all_objects;

INSERT dbo.Orders (OrderId, CustomerId, OrderDate, TotalDue)
SELECT n, n % 500 + 1, DATEADD(DAY, n % 730, '2024-01-01'), (n % 1000) + 0.99
FROM (SELECT TOP (50000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS numbers;

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders @CustomerId int, @From date
AS
BEGIN
    DECLARE @AllOrders bigint;
    SELECT @AllOrders = COUNT_BIG(*) FROM dbo.Orders;

    SELECT c.CustomerName, COUNT_BIG(*) AS orders_found, SUM(o.TotalDue) AS total_due
    FROM dbo.Orders AS o
    JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
    WHERE o.CustomerId = @CustomerId AND o.OrderDate >= @From
    GROUP BY c.CustomerName;
END;

Now run it three times with different customers, the way an application would.

EXEC dbo.GetCustomerOrders @CustomerId = 42, @From = '2025-06-01';
EXEC dbo.GetCustomerOrders @CustomerId = 7, @From = '2025-01-01';
EXEC dbo.GetCustomerOrders @CustomerId = 99, @From = '2025-09-01';

Join the handles in the right order

Three views hold the story, and the handles chain them together. The query view exposes group_handle. Join that to index_group_handle in sys.dm_db_missing_index_groups. That view gives you index_handle, which finds the row in sys.dm_db_missing_index_details. The last_sql_handle column feeds sys.dm_exec_sql_text for the text.

SELECT q.group_handle, q.query_hash, q.user_seeks, q.user_scans,
       q.avg_total_user_cost, q.avg_user_impact,
       d.statement AS affected_table, d.equality_columns,
       d.inequality_columns, d.included_columns,
       t.text AS batch_text
FROM sys.dm_db_missing_index_group_stats_query AS q
JOIN sys.dm_db_missing_index_groups AS g ON g.index_group_handle = q.group_handle
JOIN sys.dm_db_missing_index_details AS d ON d.index_handle = g.index_handle
OUTER APPLY sys.dm_exec_sql_text(q.last_sql_handle) AS t
WHERE d.database_id = DB_ID()
ORDER BY q.user_seeks + q.user_scans DESC, q.group_handle, q.query_hash;

One row comes back. The equality column is CustomerId, the inequality column is OrderDate, and TotalDue is included. user_seeks shows 3, one for each run. The batch_text column holds the whole procedure, not just the guilty statement.

Read avg_user_impact as an estimate from the optimizer’s costing. In my run it said about 97 percent, while avg_total_user_cost was only about 0.24. A huge percentage of a tiny cost is not a reason to rush. Your numbers will differ.

Trim the text to one statement

The index belongs to one statement, so cut the batch down. The offsets are byte offsets, which is why the code divides by 2. An end offset of -1 means the statement runs to the end of the batch. I also add the server start time, because these counters reset when the engine restarts.

SELECT q.query_hash, d.equality_columns, d.inequality_columns, d.included_columns,
       SUBSTRING(t.text, q.last_statement_start_offset / 2 + 1,
         (CASE WHEN q.last_statement_end_offset = -1
               THEN DATALENGTH(t.text)
               ELSE q.last_statement_end_offset END
          - q.last_statement_start_offset) / 2 + 1) AS statement_text,
       i.sqlserver_start_time
FROM sys.dm_db_missing_index_group_stats_query AS q
JOIN sys.dm_db_missing_index_groups AS g ON g.index_group_handle = q.group_handle
JOIN sys.dm_db_missing_index_details AS d ON d.index_handle = g.index_handle
OUTER APPLY sys.dm_exec_sql_text(q.last_sql_handle) AS t
CROSS JOIN sys.dm_os_sys_info AS i
WHERE d.database_id = DB_ID()
ORDER BY q.group_handle, q.query_hash;

Now statement_text holds only the SELECT with the join. Save it with the query hash, the suggested columns, the time you looked and the start time. Text and plans leave the cache on their own schedule.

Be careful where you save it. Statement text can contain real customer values. Keep the output with the people who investigate the workload.

Check the indexes you already own

A suggestion never looks at your current indexes as a set. Always compare it with what exists. Equality columns also arrive without a final key order.

SELECT i.name AS index_name, i.type_desc, i.is_unique,
       c.name AS column_name, ic.key_ordinal, ic.is_included_column
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY i.index_id, ic.key_ordinal, ic.index_column_id;

Only the clustered primary key on OrderId exists. Nothing covers CustomerId, so the request is fair here. On your server, one existing index might need only an extra included column.

Test the candidate before you keep it

Create the suggested index on a test copy, then run the same statement and count reads. The block also counts the open suggestions before and after.

SET STATISTICS IO ON;
DECLARE @CustomerId int = 42, @From date = '2025-06-01';

SELECT COUNT(*) AS suggestions FROM sys.dm_db_missing_index_details WHERE database_id = DB_ID();

SELECT c.CustomerName, COUNT_BIG(*) AS orders_found, SUM(o.TotalDue) AS total_due
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.CustomerId = @CustomerId AND o.OrderDate >= @From
GROUP BY c.CustomerName;

CREATE INDEX IX_Orders_CustomerId_OrderDate ON dbo.Orders (CustomerId, OrderDate) INCLUDE (TotalDue);

SELECT c.CustomerName, COUNT_BIG(*) AS orders_found, SUM(o.TotalDue) AS total_due
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.CustomerId = @CustomerId AND o.OrderDate >= @From
GROUP BY c.CustomerName;

SELECT COUNT(*) AS suggestions FROM sys.dm_db_missing_index_details WHERE database_id = DB_ID();
SET STATISTICS IO OFF;

Look at the Orders lines in Messages. The first, 182 reads, is the query before the index: it read the whole table. The middle line is CREATE INDEX reading the table to build the index. The last, 2 reads, is the same query with the index. The suggestion count also falls from 1 to 0. The write cost is the other half of the review, so test your inserts and updates before you deploy.

The last block removes the demo objects.

DROP PROCEDURE IF EXISTS dbo.GetCustomerOrders;
DROP TABLE IF EXISTS dbo.Orders, dbo.Customers;

Next time a report shows green suggestions, ask for the statement behind each one first.

An index suggestion is not an instruction, it is a lead to investigate.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.