Voici en complément des 20 requêtes pour auditer la qualité de votre modèle de données (voir 20 requêtes pour auditer la qualité de votre modèle de données) une requête destinée à déceler les index obèses (c’est à dire, ceux ayant trop de colonnes dans leur définition.
Voici la requête :
WITH
T0 AS
(
SELECT o.object_id, i.index_id,
s.name AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
is_included_column,
COUNT(*) AS NOMBRE
FROM sys.objects AS o
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.indexes AS i
ON o.object_id = i.object_id
JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
WHERE i.index_id > 0
AND is_ms_shipped = 0
GROUP BY o.object_id, i.index_id, s.name, o.name, i.name, is_included_column
)
SELECT T1.TABLE_SCHEMA,
T1.TABLE_NAME,
T1.INDEX_NAME,
T1.NOMBRE + T0.NOMBRE AS NOMBRE_COLONNES,
T0.NOMBRE AS NOMBRE_COLONNES_CLEFS,
T1.NOMBRE AS NOMBRE_COLONNES_INCLUSES
FROM T0
JOIN T0 AS T1
ON T0.object_id = T1.object_id
AND T0.index_id = T1.index_id
WHERE T0.is_included_column = 1
AND T1.is_included_column = 0
AND T1.NOMBRE + T0.NOMBRE > 10 --> seuil limite du nombre de colonnes dans l'index à ne pas dépasser
T0 AS
(
SELECT o.object_id, i.index_id,
s.name AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
is_included_column,
COUNT(*) AS NOMBRE
FROM sys.objects AS o
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.indexes AS i
ON o.object_id = i.object_id
JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
WHERE i.index_id > 0
AND is_ms_shipped = 0
GROUP BY o.object_id, i.index_id, s.name, o.name, i.name, is_included_column
)
SELECT T1.TABLE_SCHEMA,
T1.TABLE_NAME,
T1.INDEX_NAME,
T1.NOMBRE + T0.NOMBRE AS NOMBRE_COLONNES,
T0.NOMBRE AS NOMBRE_COLONNES_CLEFS,
T1.NOMBRE AS NOMBRE_COLONNES_INCLUSES
FROM T0
JOIN T0 AS T1
ON T0.object_id = T1.object_id
AND T0.index_id = T1.index_id
WHERE T0.is_included_column = 1
AND T1.is_included_column = 0
AND T1.NOMBRE + T0.NOMBRE > 10 --> seuil limite du nombre de colonnes dans l'index à ne pas dépasser
Le seuil étant fixé à 10 colonnes, vous pouvez être plus sévère encore ou plus laxiste à votre choix !!!
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
Développez et administrez pour la performance avec SQL Server 2014
L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR
