Voici une petite requête qui liste les index inutiles et présente la date de scrutation en sus des métadonnées des clauses de clef, INCLUDE et WHERE de composition de des index remontés comme inemployés.
WITH
TD AS (SELECT create_date AS DEPUIS
FROM sys.databases
WHERE database_id = DB_ID('tempdb')),
idc AS (SELECT ic.object_id, index_id,
ROW_NUMBER() OVER(PARTITION BY ic.object_id, index_id, is_included_column
ORDER BY index_column_id) AS index_column_id,
is_included_column,
c.name, CASE WHEN is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END AS ord
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id),
idk AS (SELECT object_id, index_id, index_column_id, 1 as cols,
CAST('[' + name + '] ' + ord AS NVARCHAR(max)) AS KEY_DEF
FROM idc
WHERE is_included_column = 0
AND index_column_id = 1
UNION ALL
SELECT idc.object_id, idc.index_id, idc.index_column_id, cols + 1,
KEY_DEF + ', ' + '[' + idc.name + '] ' + ord
FROM idc
INNER JOIN idk
ON idc.object_id = idk.object_id AND
idc.index_id = idk.index_id AND
idc.index_column_id = idk.index_column_id + 1
WHERE idc.is_included_column = 0),
idi AS (SELECT object_id, index_id, index_column_id, 1 as coli,
CAST('[' + name + '] ' + ord AS NVARCHAR(max)) AS COL_DEF
FROM idc
WHERE is_included_column = 1
AND index_column_id = 1
UNION ALL
SELECT idc.object_id, idc.index_id, idc.index_column_id, coli + 1,
COL_DEF + ', ' + '[' + idc.name + '] ' + ord
FROM idc
INNER JOIN idi
ON idc.object_id = idi.object_id AND
idc.index_id = idi.index_id AND
idc.index_column_id = idi.index_column_id + 1
WHERE idc.is_included_column = 1),
dfi AS (SELECT idk.*, COL_DEF,
ROW_NUMBER() OVER(PARTITION BY idk.object_id, idk.index_id
ORDER BY cols DESC) AS N
FROM idk
LEFT OUTER JOIN idi
ON idk.object_id = idi.object_id
AND idk.index_id = idi.index_id),
dfj AS (SELECT dfi.object_id, dfi.index_id,
KEY_DEF AS KEY_COLUMNS,
COL_DEF AS INCLUDED_COLUMNS, i.filter_definition AS FILTER,
'CREATE INDEX [' + i.name + '] + ON [' + o.name +'].[' + s.name
+'] (' + KEY_DEF +')'
+ COALESCE(' INCLUDE (' + COL_DEF + ')', '')
+ COALESCE(' WHERE (' + i.filter_definition + ')', '') + ';' AS LOGICAL_DEFINITION
FROM dfi
INNER JOIN sys.indexes AS i
ON dfi.object_id = i.object_id
AND dfi.index_id = i.index_id
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE N = 1)
SELECT DEPUIS, s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME, i.name AS INDEX_NAME,
user_seeks, user_scans, user_lookups, user_updates,
KEY_COLUMNS, INCLUDED_COLUMNS, FILTER, LOGICAL_DEFINITION
FROM sys.dm_db_index_usage_stats AS ius
CROSS JOIN TD
INNER JOIN sys.indexes AS i
ON ius.object_id = i.object_id
AND ius.index_id = i.index_id
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
LEFT OUTER JOIN dfj
ON dfj.object_id = i.object_id
AND dfj.index_id = i.index_id
WHERE database_id = DB_ID()
AND i.index_id > 1
AND user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND is_unique = 0
AND is_primary_key = 0
AND is_unique_constraint = 0
ORDER BY user_updates, i.index_id DESC;
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
