Index couvrants SQL Server: clés, INCLUDE et coût des lookups

Un plan peut afficher Index Seek et rester lent. Après cette recherche, SQL Server effectue parfois des milliers de lookups dans l'index cluster pour récupérer les colonnes manquantes. Le sujet n'est donc pas la présence d'un seek, mais le nombre d'opérations supplémentaires nécessaires à la requête complète.

Un index couvrant contient les colonnes nécessaires à une requête précise. La couverture appartient à cette relation, pas à la table en général. Ajouter une colonne au SELECT ou modifier un filtre peut supprimer la couverture existante. Commencez par l'instruction réelle et par la distribution habituelle des paramètres.

Organiser la clé selon la navigation

L'exemple fixe le client par égalité, applique une plage de dates et restitue les commandes les plus récentes. CustomerId ouvre donc la clé, suivi de OrderedAt et de SaleId pour départager les dates identiques. Total et Status servent seulement à la sortie et figurent dans INCLUDE.

CREATE TABLE #Sales
(
    SaleId bigint NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    OrderedAt datetime2(0) NOT NULL,
    Total decimal(12,2) NOT NULL,
    Status tinyint NOT NULL
);
INSERT #Sales VALUES
(1,7,'2021-10-01',90,1),(2,7,'2021-10-02',40,0),
(3,8,'2021-10-02',20,1),(4,7,'2021-10-03',70,1);

CREATE INDEX IX_Sales_Customer_Date
ON #Sales(CustomerId, OrderedAt DESC, SaleId DESC)
INCLUDE(Total, Status);

DECLARE @CustomerId int=7;
DECLARE @From datetime2(0)='2021-10-01';
SELECT TOP (2) SaleId, OrderedAt, Total, Status
FROM #Sales
WHERE CustomerId=@CustomerId AND OrderedAt>=@From
ORDER BY OrderedAt DESC, SaleId DESC;

SET STATISTICS IO, TIME ON;
SELECT SaleId, OrderedAt, Total, Status
FROM #Sales
WHERE CustomerId=@CustomerId AND OrderedAt>=@From;
SET STATISTICS IO, TIME OFF;
DROP TABLE #Sales;

Pour le client 7, la première requête renvoie 4 et 2. L'index peut fournir restriction et ordre demandé. Si la date venait en premier, SQL Server pourrait parcourir la plage mais lire beaucoup d'autres clients avant de les éliminer. Placer systématiquement la colonne globalement la plus sélective en tête n'est donc pas une règle suffisante.

Une condition de plage limite souvent l'efficacité des colonnes suivantes pour resserrer la recherche. Elles peuvent encore servir au tri ou à la couverture. Examinez les prédicats de recherche et les filtres résiduels dans le plan réel. Le nombre de lignes lues, comparé au nombre rendu, est plus informatif que le seul nom des colonnes indexées.

Utiliser INCLUDE pour un coût identifié

Les colonnes incluses sont stockées au niveau feuille de l'index non cluster, sans participer à son ordre de navigation. Elles peuvent supprimer des lookups tout en élargissant les pages, la consommation du cache et le volume sauvegardé. Leurs modifications doivent aussi être maintenues. Inclure un état très souvent actualisé peut ajouter une charge d'écriture notable.

N'incluez pas automatiquement toutes les colonnes d'une recommandation d'index manquant. Ces suggestions reflètent un fragment du coût estimé et ne consolident pas les besoins des autres requêtes. Comparez avec les index existants. Une extension raisonnable peut servir plusieurs instructions, mais deux ordres de clés différents peuvent également répondre à deux usages légitimes.

Un lookup n'est pas intrinsèquement mauvais. Dix lookups pour dix lignes peuvent coûter moins cher que l'entretien permanent d'un index large et peu utilisé. Le point de bascule dépend du nombre de lignes, du cache, de leur largeur et des alternatives. Si un client possède dix commandes et un autre un million, testez les deux.

Évaluer toute l'activité

La petite table temporaire démontre les résultats attendus, sans prouver un gain mesurable. Utilisez une copie représentative des données et relevez STATISTICS IO, durée, CPU et plans réels. Essayez des plages étroites et larges. Ne videz pas le cache de production pour fabriquer un test à froid; comparez des conditions équivalentes et notez leur contexte.

Mesurez aussi insertions, corrections de montants, changements d'état et suppression des anciennes données. Si l'index évite un tri, vérifiez allocations mémoire et déversements avant et après. Si une requête volumineuse choisit toujours un scan, ce choix peut être rationnel. Forcer une recherche suivie de centaines de milliers de lookups peut empirer la situation.

La décision doit nommer les requêtes gagnantes, les index éventuellement redondants et le supplément de stockage et d'écriture. Conservez l'ancienne définition et observez un cycle métier complet. Un index couvrant utile réduit le coût global du travail; il ne se contente pas d'améliorer l'apparence du plan.

Références techniques: Microsoft Learn: Included columns · Microsoft Learn: Index design.