LLI Wiki — My Digital Knowledge Base

Suggestions d'index manquants depuis le cache de plans

Lister les suggestions d'index manquants enregistrées par l'optimiseur SQL Server dans les plans en cache, avec l'instruction CREATE INDEX correspondante.

Cette requête interroge le cache de plans de SQL Server (ou d'Azure SQL Database) et liste les suggestions d'index manquants (MissingIndex) produites par l'optimiseur, avec l'instruction CREATE NONCLUSTERED INDEX correspondante. Les suggestions sont triées par impact estimé.

Requête

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;

Remarques

Mastodon LLIMastodon SocialFosstodon