162
Chapitre 6. Utilisation des index
clustered est beaucoup plus rapide que de scanner la table. De plus, seules les pages
d’index seront verrouillées, permettant une plus grande concurrence d’accès à la
table.
Que se passe-t-il dès que nous rajoutons une colonne supplémentaire dans le
SELECT ?
SELECT LastName, FirstName
FROM Person.Contact
WHERE LastName LIKE 'A%'
ORDER BY LastName, FirstName;
Voici le plan d’exécution :
|--Sort(ORDER BY:([LastName] ASC, [FirstName] ASC))
|--Clustered Index Scan(OBJECT:([PK_Contact_ContactID]),
WHERE:([LastName] like N'A%'))
Raté ! Il manque à l’index nix$Person_Contact$LastName l’information de FirstName. Pour éviter le bookmark lookup, l’optimiseur préfère parcourir la table. Différence de performances ? Un SET STATISTICS IO ON nous donne en lectures, 5 pages
pour la première requête, 1135 pour la seconde… Voilà qui est dommage, simplement pour une colonne de plus à retourner. Pour éviter cela, nous allons rendre
l’index couvrant. En SQL Server 2000, il n’y avait d’un moyen de le faire : ajouter
les colonnes à couvrir en fin de clé de l’index. C’était efficace, mais avec un léger
désavantage : les colonnes ajoutées alourdissaient toute la structure de l’index,
puisqu’elles faisaient partie de la clé. Depuis SQL Server 2005, nous pouvons utiliser
une commande d’inclusion, qui ajoute nos colonnes dans le nœud feuille de l’index
seulement. Ainsi, tous les nœuds intermédiaires conservent leur compacité. Les
colonnes incluses n’ont pas besoin d’y être, puisqu’elles ne servent pas à résoudre la
clause de filtre, mais l’affichage dans la clause SELECT. Bien entendu, si vous vouliez
permettre la recherche par LastName et FirstName, vous ajouteriez la colonne FirstName dans la clé de l’index, au lieu de la mettre en inclusion. La syntaxe de l’inclusion est la suivante :
CREATE INDEX nix$Person_Contact$LastName
ON Person.Contact (LastName) INCLUDE (FirstName)
WITH DROP_EXISTING;
Nous avons ajouté l’option DROP_EXISTING pour recréer l’index sans avoir à le
supprimer préalablement. Qu’en est-il de notre requête ? Voici son nouveau plan
d’exécution :
|--Sort(ORDER BY:([LastName] ASC, [FirstName] ASC))
|--Index Seek(OBJECT:([nix$Person_Contact$LastName]),
SEEK:([LastName] >= N'A' AND [LastName] < N'B'),
WHERE:([LastName] like N'A%') ORDERED FORWARD)
Le seek est revenu. Compte tenu des gains de performance importants de la couverture de requête, n’hésitez pas à pratiquer l’inclusion, même si vous avez plusieurs
colonnes. N’exagérez pas non plus, n’oubliez pas que l’index à un coût de mainte-
Chapitre 6. Utilisation des index
clustered est beaucoup plus rapide que de scanner la table. De plus, seules les pages
d’index seront verrouillées, permettant une plus grande concurrence d’accès à la
table.
Que se passe-t-il dès que nous rajoutons une colonne supplémentaire dans le
SELECT ?
SELECT LastName, FirstName
FROM Person.Contact
WHERE LastName LIKE 'A%'
ORDER BY LastName, FirstName;
Voici le plan d’exécution :
|--Sort(ORDER BY:([LastName] ASC, [FirstName] ASC))
|--Clustered Index Scan(OBJECT:([PK_Contact_ContactID]),
WHERE:([LastName] like N'A%'))
Raté ! Il manque à l’index nix$Person_Contact$LastName l’information de FirstName. Pour éviter le bookmark lookup, l’optimiseur préfère parcourir la table. Différence de performances ? Un SET STATISTICS IO ON nous donne en lectures, 5 pages
pour la première requête, 1135 pour la seconde… Voilà qui est dommage, simplement pour une colonne de plus à retourner. Pour éviter cela, nous allons rendre
l’index couvrant. En SQL Server 2000, il n’y avait d’un moyen de le faire : ajouter
les colonnes à couvrir en fin de clé de l’index. C’était efficace, mais avec un léger
désavantage : les colonnes ajoutées alourdissaient toute la structure de l’index,
puisqu’elles faisaient partie de la clé. Depuis SQL Server 2005, nous pouvons utiliser
une commande d’inclusion, qui ajoute nos colonnes dans le nœud feuille de l’index
seulement. Ainsi, tous les nœuds intermédiaires conservent leur compacité. Les
colonnes incluses n’ont pas besoin d’y être, puisqu’elles ne servent pas à résoudre la
clause de filtre, mais l’affichage dans la clause SELECT. Bien entendu, si vous vouliez
permettre la recherche par LastName et FirstName, vous ajouteriez la colonne FirstName dans la clé de l’index, au lieu de la mettre en inclusion. La syntaxe de l’inclusion est la suivante :
CREATE INDEX nix$Person_Contact$LastName
ON Person.Contact (LastName) INCLUDE (FirstName)
WITH DROP_EXISTING;
Nous avons ajouté l’option DROP_EXISTING pour recréer l’index sans avoir à le
supprimer préalablement. Qu’en est-il de notre requête ? Voici son nouveau plan
d’exécution :
|--Sort(ORDER BY:([LastName] ASC, [FirstName] ASC))
|--Index Seek(OBJECT:([nix$Person_Contact$LastName]),
SEEK:([LastName] >= N'A' AND [LastName] < N'B'),
WHERE:([LastName] like N'A%') ORDERED FORWARD)
Le seek est revenu. Compte tenu des gains de performance importants de la couverture de requête, n’hésitez pas à pratiquer l’inclusion, même si vous avez plusieurs
colonnes. N’exagérez pas non plus, n’oubliez pas que l’index à un coût de mainte-