166
Chapitre 6. Utilisation des index
(dans les pages). Pour combattre cette situation, il n’y a qu’un moyen, c’est la réorganisation complète, de temps en temps. Réorganiser à chaque petit changement est
impraticable : les employés du service passeraient leur temps à tout changer.
Un autre problème apparaît quand l’armoire elle-même n’a pas assez de place
pour contenir de nouvelles boîtes sur toutes les étagères. À ce moment, l’ajout d’une
nouvelle boîte ne peut se faire qu’au fond de l’armoire. Il faut alors inscrire sur un
post-it fixé sur la boîte à fiche pleine le numéro de la boîte à fiche qui contient les
dossiers suivants, pour qu’on puisse aller la chercher au fond de l’armoire. Dès lors,
parcourir les dossiers veut dire passer de boîte en boîte non plus dans l’ordre des étagères, mais avec de nombreux renvois à l’emplacement du fond, et avec à chaque fois
un retour à l’endroit quitté sur l’étagère. C’est ce qu’on appelle de la fragmentation
externe : la liste doublement liée d’un niveau d’index pointe sur des pages qui ne
sont pas contiguës, un scan doit donc faire de nombreux allers et retours entre les
extensions. Un split de page pleine entraîne en général à la fois de la fragmentation
interne, puisque de nouvelles pages sont créées qui contiennent de l’espace vide, et
de la fragmentation externe, puisque ces nouvelles pages sont souvent créées dans
d’autres extensions. Une suppression de ligne ne crée que de la fragmentation
interne. Notons également que les splits ne se produisent pas qu’aux insertions : une
mise à jour de ligne, qui augmente la taille d’une colonne de type variable, peut provoquer aussi le déplacement des lignes, soit dans un index nonclustered dont il est
une partie de la clé, soit dans un index clustered, c’est-à-dire la table elle-même 1 . La
fragmentation interne est ennuyeuse parce qu’elle fait grossir la table inutilement.
La fragmentation externe l’est encore plus, parce qu’elle diminue les performances
en lecture physique en provoquant des lectures aléatoires, et qu’elle diminue aussi les
performances d’écriture. Les clauses FILLFACTOR et PAD_INDEX servent à réduire la
fragmentation externe.
FILLFACTOR permet d’indiquer un pourcentage d’espace vide dans les pages du
nœud feuille de l’index, à sa création. Cela permet de conserver suffisamment
d’espace libre pour accommoder de nouvelles lignes sans provoquer de splits, bien
entendu au détriment de la compacité des pages. Sa valeur est 0, qui correspond à
100 %. Pour vos tables très fortement modifiées, vous pouvez attribuer une valeur
manuelle. Par exemple, un FILLFACTOR de 80 laissera 20 % d’espace libre dans la
page. Pour des tables utilisées plutôt en lecture (comme dans des applications
OLAP), laissez-le en valeur par défaut, pour obtenir les meilleures performances de
vos index en les conservant le plus compacts possible.
La colonne fill_factor de la vue système sys.indexes vous donne le FILLFACTOR
défini de vos index :
SELECT SCHEMA_NAME(o.schema_id) + '.' + o.name as table_name,
i.name as index_name,
i.fill_factor
FROM sys.indexes i
1. Les tables heap se réorganisent différemment : des renvois d’enregistrements (forwareded
records) sont créés dans ces cas).
Chapitre 6. Utilisation des index
(dans les pages). Pour combattre cette situation, il n’y a qu’un moyen, c’est la réorganisation complète, de temps en temps. Réorganiser à chaque petit changement est
impraticable : les employés du service passeraient leur temps à tout changer.
Un autre problème apparaît quand l’armoire elle-même n’a pas assez de place
pour contenir de nouvelles boîtes sur toutes les étagères. À ce moment, l’ajout d’une
nouvelle boîte ne peut se faire qu’au fond de l’armoire. Il faut alors inscrire sur un
post-it fixé sur la boîte à fiche pleine le numéro de la boîte à fiche qui contient les
dossiers suivants, pour qu’on puisse aller la chercher au fond de l’armoire. Dès lors,
parcourir les dossiers veut dire passer de boîte en boîte non plus dans l’ordre des étagères, mais avec de nombreux renvois à l’emplacement du fond, et avec à chaque fois
un retour à l’endroit quitté sur l’étagère. C’est ce qu’on appelle de la fragmentation
externe : la liste doublement liée d’un niveau d’index pointe sur des pages qui ne
sont pas contiguës, un scan doit donc faire de nombreux allers et retours entre les
extensions. Un split de page pleine entraîne en général à la fois de la fragmentation
interne, puisque de nouvelles pages sont créées qui contiennent de l’espace vide, et
de la fragmentation externe, puisque ces nouvelles pages sont souvent créées dans
d’autres extensions. Une suppression de ligne ne crée que de la fragmentation
interne. Notons également que les splits ne se produisent pas qu’aux insertions : une
mise à jour de ligne, qui augmente la taille d’une colonne de type variable, peut provoquer aussi le déplacement des lignes, soit dans un index nonclustered dont il est
une partie de la clé, soit dans un index clustered, c’est-à-dire la table elle-même 1 . La
fragmentation interne est ennuyeuse parce qu’elle fait grossir la table inutilement.
La fragmentation externe l’est encore plus, parce qu’elle diminue les performances
en lecture physique en provoquant des lectures aléatoires, et qu’elle diminue aussi les
performances d’écriture. Les clauses FILLFACTOR et PAD_INDEX servent à réduire la
fragmentation externe.
FILLFACTOR permet d’indiquer un pourcentage d’espace vide dans les pages du
nœud feuille de l’index, à sa création. Cela permet de conserver suffisamment
d’espace libre pour accommoder de nouvelles lignes sans provoquer de splits, bien
entendu au détriment de la compacité des pages. Sa valeur est 0, qui correspond à
100 %. Pour vos tables très fortement modifiées, vous pouvez attribuer une valeur
manuelle. Par exemple, un FILLFACTOR de 80 laissera 20 % d’espace libre dans la
page. Pour des tables utilisées plutôt en lecture (comme dans des applications
OLAP), laissez-le en valeur par défaut, pour obtenir les meilleures performances de
vos index en les conservant le plus compacts possible.
La colonne fill_factor de la vue système sys.indexes vous donne le FILLFACTOR
défini de vos index :
SELECT SCHEMA_NAME(o.schema_id) + '.' + o.name as table_name,
i.name as index_name,
i.fill_factor
FROM sys.indexes i
1. Les tables heap se réorganisent différemment : des renvois d’enregistrements (forwareded
records) sont créés dans ces cas).