173
6.1 Principes de l’indexation
Structure interne de l’index
Nous avons vu dans le chapitre traitant des structures de stockage comment SQL
Server répartit les données et les index dans des extensions et des pages. Profitons de
cette connaissance, et de l’existence de l’instruction non documentée DBCC PAGE,
pour suivre à la trace l’utilisation d’un index. Reprenons pour cela la table exemple
précédente, et voyons comment le moteur de stockage cherche à l’intérieur de
l’index.
Pour cela, nous devons utiliser une autre commande DBCC non documentée :
DBCC IND, qui donne la structure interne d’un index : chaque page de l’index est
retourné, avec son ID, son niveau, et la référence de la liste doublement liée : page
précédente et page suivante du même niveau. Sa syntaxe est :
DBCC IND ('base de données', 'table', PartitionId);
Donc, imaginons que nous soyons ce moteur de stockage. L’optimiseur SQL a
décidé d’une stratégie pour répondre à cette requête :
SELECT * FROM dbo.testIndex WHERE CodeLong = '49'
L’index dont la clé est la colonne CodeLong, et dont l’ID est 2, va naturellement
être utilisé. Nous pouvons le vérifier avec l’option de session SET SHOWPLAN_TEXT ON,
qui affiche le plan d’exécution en texte, au lieu d’exécuter l’instruction. Le résultat
appliqué à notre requête donne ceci :
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Index Seek(
OBJECT:([testdb].[dbo].[testIndex].[PK__testIndex__0425A276]),
SEEK:([testdb].[dbo].[testIndex].[codeLong]=[@1])
ORDERED FORWARD)
|--RID Lookup(OBJECT:([testdb].[dbo].[testIndex]),
SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
L’index PK__testIndex... est utilisé pour la recherche, et ensuite un RID lookup
est effectué, pour rechercher la ligne référencée dans l’index nonclustered par un RID
(Row ID). Nous avons vu dans les statistiques IO, que cela nécessitait la lecture de
sept pages. On se souvient que l’index sur CodeLong a six niveaux. Tout ici est donc
logique : descente sur six niveaux d’index, plus lecture de la page de données référencée par le RID. Essayons de faire ce parcours nous-mêmes. Le code suivant
alterne les requêtes de DBCC PAGE et leur résultat :
CREATE TABLE #ind (
PageFID bigint,
PagePID bigint,
IAMFID bigint,
IAMPID bigint,
ObjectID bigint,
IndexID bigint,
6.1 Principes de l’indexation
Structure interne de l’index
Nous avons vu dans le chapitre traitant des structures de stockage comment SQL
Server répartit les données et les index dans des extensions et des pages. Profitons de
cette connaissance, et de l’existence de l’instruction non documentée DBCC PAGE,
pour suivre à la trace l’utilisation d’un index. Reprenons pour cela la table exemple
précédente, et voyons comment le moteur de stockage cherche à l’intérieur de
l’index.
Pour cela, nous devons utiliser une autre commande DBCC non documentée :
DBCC IND, qui donne la structure interne d’un index : chaque page de l’index est
retourné, avec son ID, son niveau, et la référence de la liste doublement liée : page
précédente et page suivante du même niveau. Sa syntaxe est :
DBCC IND ('base de données', 'table', PartitionId);
Donc, imaginons que nous soyons ce moteur de stockage. L’optimiseur SQL a
décidé d’une stratégie pour répondre à cette requête :
SELECT * FROM dbo.testIndex WHERE CodeLong = '49'
L’index dont la clé est la colonne CodeLong, et dont l’ID est 2, va naturellement
être utilisé. Nous pouvons le vérifier avec l’option de session SET SHOWPLAN_TEXT ON,
qui affiche le plan d’exécution en texte, au lieu d’exécuter l’instruction. Le résultat
appliqué à notre requête donne ceci :
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Index Seek(
OBJECT:([testdb].[dbo].[testIndex].[PK__testIndex__0425A276]),
SEEK:([testdb].[dbo].[testIndex].[codeLong]=[@1])
ORDERED FORWARD)
|--RID Lookup(OBJECT:([testdb].[dbo].[testIndex]),
SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
L’index PK__testIndex... est utilisé pour la recherche, et ensuite un RID lookup
est effectué, pour rechercher la ligne référencée dans l’index nonclustered par un RID
(Row ID). Nous avons vu dans les statistiques IO, que cela nécessitait la lecture de
sept pages. On se souvient que l’index sur CodeLong a six niveaux. Tout ici est donc
logique : descente sur six niveaux d’index, plus lecture de la page de données référencée par le RID. Essayons de faire ce parcours nous-mêmes. Le code suivant
alterne les requêtes de DBCC PAGE et leur résultat :
CREATE TABLE #ind (
PageFID bigint,
PagePID bigint,
IAMFID bigint,
IAMPID bigint,
ObjectID bigint,
IndexID bigint,