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
- Les suggestions viennent du cache de plans : elles ne couvrent que les plans encore en cache et disparaissent au redémarrage ou lors d'un vidage du cache.
- L'optimiseur ne vérifie pas les index existants proches ni le coût d'écriture ajouté. Vérifiez chaque suggestion avant de créer un index.
impactest une estimation de l'optimiseur, pas un gain mesuré.