LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon