Missing index suggestions from the plan cache
List the missing index suggestions the SQL Server optimizer stored in cached plans, with the matching CREATE INDEX statement.
This query reads the SQL Server plan cache (or Azure SQL Database) and lists the missing index suggestions (MissingIndex) produced by the optimizer, together with the matching CREATE NONCLUSTERED INDEX statement. Results are sorted by estimated impact.
Query
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT query_plan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'), '[', ''), ']', '')) AS database_id,
OBJECT_ID(
REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'), '[', ''), ']', '') + '.' +
REPLACE(REPLACE(n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)'), '[', ''), ']', '') + '.' +
REPLACE(REPLACE(n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)'), '[', ''), ']', '')
) AS object_id,
REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'), '[', ''), ']', '') + '.' +
REPLACE(REPLACE(n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)'), '[', ''), ']', '') + '.' +
REPLACE(REPLACE(n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)'), '[', ''), ']', '')
AS statement,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY'
FOR XML PATH('')
) AS equality_columns,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY'
FOR XML PATH('')
) AS inequality_columns,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
FOR XML PATH('')
) AS include_columns,
-- Construction de l'instruction de création de l'index avec [ ] pour la db, le schéma et la table
'CREATE NONCLUSTERED INDEX [IX_' +
REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'), '[', ''), ']', '') + '_' +
REPLACE(REPLACE(n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)'), '[', ''), ']', '') + '_' +
REPLACE(REPLACE(n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)'), '[', ''), ']', '') +
'] ON [' +
REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'), '[', ''), ']', '') + '].[' +
REPLACE(REPLACE(n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)'), '[', ''), ']', '') + '].[' +
REPLACE(REPLACE(n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)'), '[', ''), ']', '') +
'] (' +
LEFT(
(SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') IN ('EQUALITY','INEQUALITY')
FOR XML PATH('')),
LEN((SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') IN ('EQUALITY','INEQUALITY')
FOR XML PATH('')))-1) +
')' +
CASE
WHEN LEN(
(SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
FOR XML PATH(''))
) > 0
THEN ' INCLUDE (' +
LEFT(
(SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
FOR XML PATH('')),
LEN((SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
FOR XML PATH('')))-1)
+ ')'
ELSE ''
END AS create_index
INTO #MissingIndexInfo
FROM
(
SELECT query_plan
FROM (
SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats WITH(NOLOCK)
) AS qs
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
WHERE tp.query_plan.exist('//MissingIndex') = 1
) AS tab (query_plan)
CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE n.exist('QueryPlan/MissingIndexes') = 1;
-- Retirer la virgule finale des listes
UPDATE #MissingIndexInfo
SET equality_columns = LEFT(equality_columns, LEN(equality_columns) - 1),
inequality_columns = LEFT(inequality_columns, LEN(inequality_columns) - 1),
include_columns = LEFT(include_columns, LEN(include_columns) - 1);
SELECT *
FROM #MissingIndexInfo
ORDER BY impact DESC;
DROP TABLE #MissingIndexInfo;
Notes
- The suggestions come from the plan cache: they only cover plans still cached and are lost on restart or when the cache is cleared.
- The optimizer does not check for similar existing indexes or the extra write cost of a new index. Review each suggestion before creating an index.
impactis the optimizer's estimate, not a measured gain.