Gestion des index manquants et inutiles

Voici deux requêtes permettant de créer les index manquants ou de supprimer les index inutiles dans les bases SQL Server

— mise en place de tous les index manquants :


SELECT 'CREATE INDEX X_' + REPLACE(CAST(NEWID() AS VARCHAR(50)), '-', '') + ' ON ' + statement + ' (' 

       + CASE  

            WHEN equality_columns IS NULL THEN '' 

            WHEN inequality_columns IS NULL THEN equality_columns 

            ELSE equality_columns + ', ' 

         END 

       + CASE  

            WHEN inequality_columns IS NULL THEN '' 

            ELSE inequality_columns  

         END    

       + ')' 

       + CASE  

            WHEN included_columns IS NULL THEN '' 

            ELSE ' INCLUDE (' + included_columns +')' 

         END 

       + ' WITH (FILLFACTOR = 90);'    

FROM   sys.dm_db_missing_index_details

— suppression des index inutiles dans une base


USE <MaBase>; 

 

SELECT 'DROP INDEX [' + SCHEMA_NAME(schema_id)  

               +'].['+o.name +'].[' + i.name +'];' AS SQL_STATEMENT, 

       user_seeks, user_scans, user_lookups, user_updates 

FROM   sys.dm_db_index_usage_stats AS ius  

       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 ius.object_id = o.object_id 

WHERE  database_id = DB_ID()   

  AND  is_unique = 0 

  AND  is_primary_key = 0 

  AND  is_unique_constraint = 0 

  AND  user_seeks = 0 

  AND  user_updates > 0 

ORDER BY COALESCE(CAST(user_updates AS FLOAT) / NULLIF(user_scans + user_lookups, 0), 2000000000) DESC;

— NOTA : Remplacer <MaBase> par le nom de votre base


-------- 

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  * * * * *

MVP Microsoft SQL Server

5 réflexions au sujet de « Gestion des index manquants et inutiles »

Bourgui 24 mai 2011 à 12 h 16 min

Merci pour tout.

Les champs ntext son ici pour des raison plus historique, j’essaie de les chasser au fur et a mesure (d’ailleur la requête m’a permis d’en trouver un autre). Mais c’est pas évident évident sur une base qui a déjà quelques année et un bon paquet de tables.

Encore merci

↓

sqlpro Auteur de l’article23 mai 2011 à 18 h 08 min

1) les types TEXT, NTEXT et IMAGE sont obsolète depuis la version 2005. Il est donc urgent de les transformer en VARCHAR(max), NVARCHAR(max) et VARBINARY(max). Une prochaine version ne les supportera plus.

2) si vous avez une seule colonne de ce type dans la clause INCLUDE, alors vous pouvez utiliser cette requête :

WITH   
T01 AS  
(SELECT '[' + TABLE_CATALOG +'].[' + TABLE_SCHEMA +'].[' + TABLE_NAME + ']' AS TABLE_FULL_NAME,   
  COLUMN_NAME  
 FROM INFORMATION_SCHEMA.COLUMNS  
 WHERE DATA_TYPE IN ('text', 'ntext', 'image')),  
T02 AS   
(SELECT statement, equality_columns, inequality_columns, included_columns, COLUMN_NAME,  
  PATINDEX( '%' + COLUMN_NAME + '%', included_columns) AS COL_NON_INCLUDE  
 FROM sys.dm_db_missing_index_details AS mid  
  LEFT OUTER JOIN T01  
  ON mid.statement = T01.TABLE_FULL_NAME),  
T03 AS  
(SELECT statement, equality_columns, inequality_columns, COL_NON_INCLUDE,  
  CASE WHEN COL_NON_INCLUDE IS NULL -- pas de colonnes BLOB obsolète  
  THEN included_columns  
  WHEN COL_NON_INCLUDE = 1 AND LEN(included_columns) = LEN(COLUMN_NAME) -- colonne BLOB obsolète seule dans l'include  
  THEN NULL  
  WHEN COL_NON_INCLUDE = 1 -- colonne BLOB obsolète en tête  
  THEN SUBSTRING(included_columns, LEN(COLUMN_NAME) + 3, LEN(included_columns) - LEN(COLUMN_NAME) - 2)  
  WHEN COL_NON_INCLUDE > 1 AND COL_NON_INCLUDE + LEN(COLUMN_NAME) - 1 < LEN(included_columns) -- colonne BLOB obsolète au milieu  
  THEN SUBSTRING(included_columns, 1, COL_NON_INCLUDE - 1)   
  + SUBSTRING(included_columns, COL_NON_INCLUDE + LEN(COLUMN_NAME) + 2, LEN(included_columns) - (COL_NON_INCLUDE + LEN(COLUMN_NAME) ))  
  WHEN COL_NON_INCLUDE > 1 AND COL_NON_INCLUDE + LEN(COLUMN_NAME) - 1 = LEN(included_columns) -- colonne BLOB obsolète en fin   
  THEN SUBSTRING(included_columns, 1, COL_NON_INCLUDE - 3)   
  ELSE included_columns  
  END AS INCLUSIVE_COLS  
 FROM T02),  
T04 AS   
(SELECT statement, equality_columns, inequality_columns, INCLUSIVE_COLS, LEN(INCLUSIVE_COLS) AS LN,  
  MIN(LEN(INCLUSIVE_COLS)) OVER(PARTITION BY statement, equality_columns, inequality_columns) AS MLN  
 FROM T03),  
T05 AS  
 (SELECT statement, equality_columns, inequality_columns, INCLUSIVE_COLS AS included_columns  
  FROM T04   
  WHERE LN = MLN)  
SELECT 'CREATE INDEX X_' + REPLACE(CAST(NEWID() AS VARCHAR(50)), '-', '') + ' ON ' + statement + ' ('   
  + COALESCE(equality_columns +', ' + inequality_columns, equality_columns, inequality_columns) + ') '  
  + COALESCE(' INCLUDE (' + included_columns +') ', '')  
  + ' WITH (FILLFACTOR = 90);'   
FROM T05;

3) Si vous avez plusieurs colonnes de type BLOB obsolète, alors il faut faire une requête récursive qui dépile la chaine included_columns en colonne, supprime de cette colonne les références aux colonnes BLOB obsolètes puis remettre cela sous forme de chaine. Voici un exemple qui utilise la fonction de dépliage F_MULTIPARSE dont j’ai donné le code ici : http://blog.developpez.com/sqlpro/p7509/langage-sql-norme/fonction-de-decoupage-a-cesure-multiple/

WITH   
T01 AS  
(SELECT ROW_NUMBER() OVER(ORDER BY statement, equality_columns, inequality_columns, included_columns) AS NID,  
  statement, equality_columns, inequality_columns, included_columns  
 FROM sys.dm_db_missing_index_details),  
T02 AS  
(SELECT T01.*, LTRIM(MOT) AS COL, COLUMN_NAME AS COL_TO_DELETE  
 FROM T01  
  OUTER APPLY dbo.F_MULTIPARSE(included_columns, ',')  
  LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS  
  ON T01.statement = '[' + TABLE_CATALOG +'].[' + TABLE_SCHEMA +'].[' + TABLE_NAME + ']'   
  AND LTRIM(MOT) = COLUMN_NAME   
  AND DATA_TYPE IN ('text', 'ntext', 'image')),  
T03 AS  
(SELECT DISTINCT NID, statement, equality_columns, inequality_columns,   
  CASE WHEN COL_TO_DELETE IS NULL THEN COL ELSE NULL END AS COL_TO_INCLUDE  
 FROM T02),  
T04 AS  
(SELECT *, ROW_NUMBER() OVER(PARTITION BY NID ORDER BY COL_TO_INCLUDE) AS N  
 FROM T03 AS T1  
 WHERE COL_TO_INCLUDE IS NOT NULL  
  OR (SELECT COUNT(*)  
  FROM T03 AS T2  
  WHERE T1.NID = T2.NID) = 1),  
T05 AS   
(SELECT NID, statement, equality_columns, inequality_columns, N,  
  CAST(COL_TO_INCLUDE AS NVARCHAR(max)) AS included_columns  
 FROM T04  
 WHERE N = 1  
 UNION ALL  
 SELECT T05.NID, T05.statement, T05.equality_columns, T05.inequality_columns, T04.N,  
  T05.included_columns + ', ' + T04.COL_TO_INCLUDE  
 FROM T05   
  INNER JOIN T04  
  ON T04.NID = T05.NID AND T05.N + 1 = T04.N),  
T06 AS  
(SELECT *, ROW_NUMBER() OVER(PARTITION BY NID ORDER BY N DESC) AS M   
 FROM T05)  
  
SELECT 'CREATE INDEX X_' + REPLACE(CAST(NEWID() AS VARCHAR(50)), '-', '') + ' ON ' + statement + ' ('   
  + COALESCE(equality_columns +', ' + inequality_columns, equality_columns, inequality_columns) + ') '  
  + COALESCE(' INCLUDE (' + included_columns +') ', '')  
  + ' WITH (FILLFACTOR = 90);'   
FROM T06  
WHERE M = 1;

4) FILLFACTOR est le facteur de remplissage des pages d’index. A défaut la plupart du temps (sauf configuration particulière du serveur via sp_configure, option « fill factor (%) »), les pages des index sont construite avec un remplissage de 100%, provoquant une importante fragmentation des pages d’index à la moindre insertion ou modification (voir : http://sqlpro.developpez.com/optimisation/MaintenanceIndex/ pour une explication). Le fait de mettre un facteur de remplissage moins élevé réserve un peu de place pour les nouvelles insertions ou modifications au prix d’une légère augmentation de la taille de l’index. Ainsi, avec un FILLFACTOR DE 90% l’index grossit de 11,11 % exactement.

↓

Bourgui 23 mai 2011 à 16 h 56 min

coallesce est effectivement une solution.

il y a aussi un bug, mais je sais pas si il a une solution : lorsque une des colonnes incluse dans l’index est un ntext alors l’execution de la commande échoue (on ne peut inclure de ntextd ans un index car les donnée d’un ntext).

Est ce que vous pouvez expliquer le FILLFactor = 90 par défaut ?

Merci

↓

sqlpro Auteur de l’article23 mai 2011 à 16 h 11 min

Bonne remarque, mais plutôt que d’utiliser le ISNULL qui est une fonction spécifique à SQL Server et limitée à deux paramètres, mieux vaut utiliser la fonction normative COALESCE…

SELECT 'CREATE INDEX X_' + REPLACE(CAST(NEWID() AS VARCHAR(50)), '-', '') + ' ON ' + statement + ' ('   
  + COALESCE(equality_columns +', ' + inequality_columns, equality_columns, inequality_columns) + ') '  
  + COALESCE(' INCLUDE (' + included_columns +') ', '')  
  + ' WITH (FILLFACTOR = 90);'   
FROM sys.dm_db_missing_index_details

↓

Bourgui 20 mai 2011 à 14 h 01 min

il existe la fonction isnull qui permet d’éviter les « case when maColonne is null »

↓

Laisser un commentaire Annuler la réponse.

Vous devez être connecté(e) pour rédiger un commentaire.

Identifiez-vous

Créer un compte

Le blog de SQLpro

Le SQL pour SQL Server, PostGreSQL et tous les autres SGBDR

Gestion des index manquants et inutiles

5 réflexions au sujet de « Gestion des index manquants et inutiles »

Laisser un commentaire Annuler la réponse.

Identifiant
Mot de passe

Mot de passe oublié ?