Les index SQL Server : au-delà du CREATE INDEX

Tout le monde sait créer un index. On ouvre SSMS, on fait un clic droit, "New Index", on coche quelques colonnes, et voilà. Mais entre un index qui transforme une requête de 40 minutes en 200 millisecondes et un index qui ne sert à rien tout en ralentissant vos écritures, il y a un monde. Et ce monde, très peu de gens l'explorent vraiment.

Laissez-moi être direct : la plupart des index que je vois en production ne sont pas manquants. Ils sont faux. Mal conçus, mal ordonnés, dupliqués, ou tout simplement jamais utilisés. Et le pire ? Personne ne le sait, parce que personne ne vérifie.

Clustered vs non-clustered : la question que tout le monde se pose mal

On vous a probablement dit qu'un index clustered trie les données physiquement sur le disque. C'est vrai — mais c'est trompeur. Le clustered index est la table. La table n'existe pas séparément. Les feuilles du clustered index, ce sont vos données. Donc quand vous choisissez votre clé clustered, vous choisissez l'ordre physique de toute votre table. Ce n'est pas un détail.

La règle que je donne systématiquement : choisissez une clé clustered qui est unique, étroite (peu de colonnes, type léger), statique (qui ne change jamais), et croissante (pour éviter les page splits). Un INT IDENTITY fait parfaitement l'affaire. Un GUID… non. Surtout pas un GUID aléatoire. Si vous devez absolument utiliser un GUID, prenez un NEWSEQUENTIALID() ou un GUID séquentiel. Mais honnêtement, un bigint identity est presque toujours meilleur.

L'index non-clustered, lui, est une structure séparée qui pointe vers la ligne — soit via la clé clustered (le "bookmark lookup"), soit via le RID si vous n'avez pas de clustered index (heap). Chaque index non-clustered a un coût : en stockage, en CPU sur les écritures, en maintenance. Ce n'est pas gratuit. Loin de là.

L'ordre des colonnes : le truc que 90% des gens ratent

Voici l'erreur la plus courante que je vois. Quelqu'un crée un index sur (DateCreation, ClientId, Statut) parce que c'est l'ordre logique dans la requête. Mais la requête filtre sur ClientId = 42 et Statut = 'Actif', sans toucher à DateCreation. Résultat ? L'index est inutile. Le moteur fait un scan de l'index parce que la première colonne n'est pas dans le prédicat.

La règle d'or pour l'ordre des colonnes dans un index composite :

1. Égalité d'abord — les colonnes avec = vont en premier.
2. Plage ensuite — les colonnes avec >, <, BETWEEN viennent après.
3. Tri enfin — les colonnes du ORDER BY en dernier.

C'est bête, c'est simple, et presque personne ne le fait. Pourquoi ? Parce que les gens créent des index en regardant la requête, pas en pensant à la structure B-tree en dessous. L'index est un arbre. Si vous ne donnez pas la bonne clé de navigation au départ, l'arbre ne sert à rien.

INCLUDE vs clé : la différence qui change tout

Voici un truc qui me fait sourire à chaque fois. Un client me montre une requête lente. Je regarde l'index. Il a 12 colonnes dans la clé. Douze. Le gars a tout mis dans la clé pour faire un "covering index". Sauf qu'un index avec 12 colonnes dans la clé est énorme, lent à parcourir, et coûteux à maintenir.

La solution ? INCLUDE. Les colonnes incluses ne participent pas au tri de l'index — elles sont juste stockées au niveau feuille pour éviter le bookmark lookup. Donc vous mettez dans la clé uniquement ce qui sert au filtrage et au tri, et tout le reste dans INCLUDE.

-- Mauvais : tout dans la clé
CREATE NONCLUSTERED INDEX IX_Commandes_Mauvais
ON Commandes (ClientId, DateCommande, ProduitId, Quantite, PrixUnitaire);

-- Bon : filtrage/tri dans la clé, reste dans INCLUDE
CREATE NONCLUSTERED INDEX IX_Commandes_Bon
ON Commandes (ClientId, DateCommande)
INCLUDE (ProduitId, Quantite, PrixUnitaire, TotalCommande);

La différence ? Le deuxième index est plus compact, plus rapide à parcourir, et couvre exactement la même requête. Le premier gaspille de l'espace et du CPU à trier des colonnes qui n'ont pas besoin d'être triées.

Les index filtrés : le superpouvoir que vous n'utilisez pas

SQL Server 2008 a introduit les index filtrés. C'est probablement la fonctionnalité la plus sous-utilisée du moteur. Un index filtré ne couvre qu'un sous-ensemble des lignes — celles qui matchent un prédicat.

Exemple concret : vous avez une table de commandes avec 50 millions de lignes. 95% sont "Livrées". 5% sont "En cours". Vos requêtes opérationnelles ne s'intéressent qu'aux commandes en cours. Pourquoi indexer les 47 millions de lignes livrées ?

CREATE NONCLUSTERED INDEX IX_Commandes_EnCours
ON Commandes (ClientId, DateCommande)
INCLUDE (Montant, AdresseLivraison)
WHERE Statut = 'En cours';

Cet index est minuscule, couvre 2,5 millions de lignes au lieu de 50, et vos requêtes sur les commandes en cours deviennent instantanées. Et le coût sur les écritures ? Quasi nul, parce que la majorité des insertions concernent des commandes qui ne matchent pas le filtre.

Je vois très peu de bases qui utilisent les index filtrés. C'est dommage. C'est un outil puissant pour les données avec une distribution asymétrique — ce qui est le cas de presque toutes les données réelles.

Columnstore : quand l'analytique change de dimension

Pour les charges analytiques — agrégations, GROUP BY sur des millions de lignes, reporting — l'index columnstore cluster est une révolution. Au lieu de stocker les données ligne par ligne, il les stocke colonne par colonne, avec compression. Les requêtes qui scannaient toute la table ne lisent que les colonnes pertinentes, souvent 5 à 10 fois moins de données.

Mais attention : le columnstore est catastrophique pour les charges transactionnelles avec beaucoup de petites écritures. Il est conçu pour les batch inserts et les reads analytiques. Si vous faites de l'OLTP, restez sur du B-tree. Si vous faites du reporting sur une table de faits, passez au columnstore. C'est aussi simple que ça.

Le mythe de la fragmentation sur SSD

Combien de DBAs passent leurs nuits à réorganiser des index parce que avg_fragmentation_in_percent dépasse 30% ? Trop. Sur disque mécanique, la fragmentation physique avait un sens : la tête devait se déplacer. Sur SSD, le déplacement physique n'existe pas. La fragmentation logique a un impact marginal, et uniquement sur les range scans de grande ampleur.

La vraie question n'est pas "est-ce que mon index est fragmenté ?" mais "est-ce que mon index est utilisé ?" Et ça, vous le découvrez avec sys.dm_db_index_usage_stats.

Trouver les index inutiles

-- Index qui n'ont jamais été lus depuis le dernier redémarrage
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique AS IsUnique
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.dm_db_index_usage_stats us 
    ON i.object_id = us.object_id 
    AND i.index_id = us.index_id
    AND us.database_id = DB_ID()
WHERE i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND i.type_desc <> 'HEAP'
  AND (us.user_seeks = 0 OR us.user_seeks IS NULL)
  AND (us.user_scans = 0 OR us.user_scans IS NULL)
ORDER BY t.name, i.name;

Attention : sys.dm_db_index_usage_stats se vide au redémarrage de SQL Server. Donc si votre serveur a rebooté hier, ces statistiques ne couvrent qu'une journée. Laissez tourner au moins un mois avant de tirer des conclusions. Mais une fois que vous avez assez de données, supprimez les index qui ne servent jamais. Votre throughput d'écriture vous remerciera.

Quand NE PAS créer d'index

Un index n'est pas un remède universel. Voici les cas où je dis non :

• Tables petites — moins de quelques milliers de lignes, un scan est plus rapide qu'un seek.
• Colonnes à faible sélectivité — un index sur une colonne "Statut" avec 3 valeurs ne sert à rien.
• Tables à très forte écriture — chaque index ajoute du coût sur INSERT/UPDATE/DELETE. Parfois le read performance n'en vaut pas la peine.
• Requêtes ponctuelles — si la requête tourne une fois par mois, un scan de 5 secondes est acceptable. Ne créez pas un index permanent pour ça.

L'indexing, c'est un compromis permanent entre read performance et write cost. Chaque index que vous ajoutez accélère certaines lectures et ralentit toutes les écritures. C'est un budget. Dépensez-le intelligemment.