Lorsque l’on a un problème de performance sur une requête SQL, le problème vient souvent (mais pas tout le temps) du manque d’index. Un index comme son nom l’indique permet d’indexer les données d’une ou plusieurs colonnes. Cela permet de chercher des données rapidement et donc d’accélerer les filtres (WHERE) les jointures (JOIN), les groupements (GROUP BY). Par exemple la requête suivante :

SELECT User_Email FROM [User] WHERE User_Email LIKE 'meziantou%'

Sans index il faut parcourir entièrement la table pour filtrer les lignes :

Après la création d’un index sur la colonne Email

CREATE INDEX UserEmail ON [user] (User_Email)

SQL Server peut utiliser celui-ci pour filtrer les lignes :

Le problème auquel on est confronté est de savoir quels index créer. Créer tous les index possibles n’est pas une solution viable car un index prend de la place sur le disque… D’un autre coté ne créer aucun index n’est pas non plus satisfaisant. Comme souvent on se retrouve avec le dilème stockage vs performance.

Le but est donc d’identifier les requêtes pour lesquels le gain est réel. Pour cela on peut utiliser quelques métriques telles que le temps d’exécution de la requête, le nombre d’exécutions ou encore le gain potentiel.

Pour les plus fainéants SQL Server fait une partie du travail pour nous. A chaque requête, SQL Server calcul un plan d’exécution (liste d’étape permettant de répondre à la requête). Durant ce calcul SQL Server stocke la liste de index qui auraient pu être utilisés s’ils existaient. Pour connaitre la liste des index à créer on peut donc simplement demander à SQL Server cette liste.

SELECT     
  'CREATE INDEX [missing_index] ON ' + mid.statement     
  + '('     
   + ISNULL(mid.equality_columns, '')     
   + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END     
   + ISNULL(mid.inequality_columns, '')     
  + ')'     
  + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,     
  migs.*, mid.database_id, mid.[object_id]     
FROM sys.dm_db_missing_index_groups mig     
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle     
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

Quelques informations sur la requête :

• equality_columns : liste des colonnes utilisées avec l’opérateur d’égalité (“=”) dans la requête, par exemple “SELECT * FROM Demo WHERE Id = 42”
• inequality_columns : liste des colonnes non utilisées avec l’opérateur d’égalité (“<>”, “>”, “<”, “>=”, “<=”) dans la requête, par exemple “SELECT * FROM Demo WHERE Id > 42”
• sys.dm_db_missing_index_groups : liste des index manquants
• sys.dm_db_missing_index_details : détails sur l’index tels que la liste des colonnes de l’index
• sys.dm_db_missing_index_group_stats : permet d’avoir des indications sur la pertinence de l’index

Cette vue, bien que pratique, a quelques limitations :

• l’index doit-il être clustered ou non ?
• Dans le cas d’un index multi-colonnes, quel est l’ordre des colonnes de l’index optimal ? (l’index (a;b) n’est pas identique à l’index (b;a))
• Quel index apportera le plus de gains ? La vue fournie tout de même quelques métriques pour nous aider : avg_total_user_cost, avg_user_impact, user_seks, user_scans

N’oubliez pas Database Engine Tuning Advisor pour obtenir des conseils précis pour optimiser une requête. Et n’oubliez pas non plus de vérifier l’utilisation de vos index (sys.dm_db_index_operational_stats, sys.dm_db_index_usage_stats).