169
6.1 Principes de l’indexation
Attention aux performances – L’appel de sys.dm_db_index_physical_stats en
mode 'DETAILED' oblige SQL Server à parcourir extensivement l’index. Éviter
d’appeler la fonction avec ce mode sur tous les index d’une base (paramètres à
NULL).
Au plus simple, vous pouvez vous baser sur les colonnes
avg_fragmentation_in_percent et fragment_count. La recommandation de Microsoft est de réorganiser l’index lorsque l’avg_fragmentation_in_percent est en dessous
de 30 %, et de reconstruire en dessus.
L’option SORT_IN_TEMPDB force SQL Server à stocker temporairement les résultats
intermédiaires de tris opérés pour créer l’index. Vous trouvez plus d’informations sur
ces tris dans l’entrée de BOL « tempdb and Index Creation ». Ces tris peuvent générer
des larges volumes sur des tables importantes, des index clustered, des index nonclustered composites ou des index nonclustered comportant beaucoup de colonnes incluses. Quand l’option SORT_IN_TEMPDB est à OFF (par défaut), toute l’opération de
création se fait dans les fichiers de la base de données qui contient l’index, ce qui
provoque beaucoup de lectures et d’écritures sur le même disque, et ralentit les opérations d’entrées/sorties normales d’utilisation de la base. Avec SORT_IN_TEMPDB à ON,
les lectures et écritures sont séparées : lectures sur la base, écritures sur tempdb, et
ensuite l’inverse. Si tempdb est placé sur un disque dédié, cela peut nettement améliorer les performances. De plus, cela offre plus de chances d’une contiguïté des
extensions contenant l’index, car l’écriture de la structure de l’index dans le fichier
de bases de données se fait en une fois. Ne négligez pas cette option sur des tables à
grande volumétrie, elle offre souvent de bons gains de performances à la création des
index, quand tempdb a son disque dédié.
DROP_EXISTING permet de supprimer l’index et de le recréer en une seule commande. Les options de l’index doivent être spécifiées à nouveau – notamment le
FILLFACTOR –, ils ne sont pas conservés de l’index existant.
La création d’un index pose un verrou sur la table, qui empêche la lecture et
l’écriture en cas d’index clustered (verrou de modification de schéma), et l’écriture en
cas d’index nonclustered (verrou partagé). L’option ONLINE permet de créer ou recréer
un index sans verrouiller la table. SQL Server utilise pour ce faire la fonctionnalité
de row versioning. Pendant toute la phase de création d’index, les modifications de
données sont possibles, elles seront ensuite automatiquement synchronisées sur le
nouvel index. Cette option est valide pour un index nonclustered aussi bien que clustered. L’opération ONLINE est plus lourde, spécialement lorsque les données de la table
max_record_size_in_bytes
Taille du plus grand enregistrement
avg_record_size_in_bytes
Taille moyenne des enregistrements
Colonne
Signification
➤
6.1 Principes de l’indexation
Attention aux performances – L’appel de sys.dm_db_index_physical_stats en
mode 'DETAILED' oblige SQL Server à parcourir extensivement l’index. Éviter
d’appeler la fonction avec ce mode sur tous les index d’une base (paramètres à
NULL).
Au plus simple, vous pouvez vous baser sur les colonnes
avg_fragmentation_in_percent et fragment_count. La recommandation de Microsoft est de réorganiser l’index lorsque l’avg_fragmentation_in_percent est en dessous
de 30 %, et de reconstruire en dessus.
L’option SORT_IN_TEMPDB force SQL Server à stocker temporairement les résultats
intermédiaires de tris opérés pour créer l’index. Vous trouvez plus d’informations sur
ces tris dans l’entrée de BOL « tempdb and Index Creation ». Ces tris peuvent générer
des larges volumes sur des tables importantes, des index clustered, des index nonclustered composites ou des index nonclustered comportant beaucoup de colonnes incluses. Quand l’option SORT_IN_TEMPDB est à OFF (par défaut), toute l’opération de
création se fait dans les fichiers de la base de données qui contient l’index, ce qui
provoque beaucoup de lectures et d’écritures sur le même disque, et ralentit les opérations d’entrées/sorties normales d’utilisation de la base. Avec SORT_IN_TEMPDB à ON,
les lectures et écritures sont séparées : lectures sur la base, écritures sur tempdb, et
ensuite l’inverse. Si tempdb est placé sur un disque dédié, cela peut nettement améliorer les performances. De plus, cela offre plus de chances d’une contiguïté des
extensions contenant l’index, car l’écriture de la structure de l’index dans le fichier
de bases de données se fait en une fois. Ne négligez pas cette option sur des tables à
grande volumétrie, elle offre souvent de bons gains de performances à la création des
index, quand tempdb a son disque dédié.
DROP_EXISTING permet de supprimer l’index et de le recréer en une seule commande. Les options de l’index doivent être spécifiées à nouveau – notamment le
FILLFACTOR –, ils ne sont pas conservés de l’index existant.
La création d’un index pose un verrou sur la table, qui empêche la lecture et
l’écriture en cas d’index clustered (verrou de modification de schéma), et l’écriture en
cas d’index nonclustered (verrou partagé). L’option ONLINE permet de créer ou recréer
un index sans verrouiller la table. SQL Server utilise pour ce faire la fonctionnalité
de row versioning. Pendant toute la phase de création d’index, les modifications de
données sont possibles, elles seront ensuite automatiquement synchronisées sur le
nouvel index. Cette option est valide pour un index nonclustered aussi bien que clustered. L’opération ONLINE est plus lourde, spécialement lorsque les données de la table
max_record_size_in_bytes
Taille du plus grand enregistrement
avg_record_size_in_bytes
Taille moyenne des enregistrements
Colonne
Signification
➤