170
Chapitre 6. Utilisation des index
sont modifiées, car SQL Server doit maintenir deux structures d’index, et elle utilise
activement tempdb, mais elle offre l’avantage d’un meilleur accès concurrentiel aux
tables. Cette option n’est disponible qu’en édition Entreprise. Pour plus de détail sur
son fonctionnement interne, reportez-vous aux BOL, entrée « How Online Index
Operations Work ».
ALLOW_ROW_LOCKS et ALLOW_PAGE_LOCKS contrôlent le verrouillage sur l’index
(donc sur la table dans le cas d’un index clustered). Par défaut, les verrous sont posés
sur des clés d’index, et escaladés au besoin. Les verrous sur les clés permettent une
meilleure concurrence d’accès, mais peuvent être plus coûteux à gérer si beaucoup de
lignes sont appelées. ALLOW_ROW_LOCKS à OFF désactive la pose de verrous au niveau
clé (ligne), et donc force les verrous sur les pages. Couplé avec ALLOW_PAGE_LOCKS à
OFF, cela force les verrous sur la table. En général il n’y a rien à changer dans ces
options. Elles peuvent éventuellement faire gagner du temps sur des requêtes larges
de type OLAP, mais dans ces requêtes, le passage en niveau d’isolation READ UNCOMMITTED est plus efficace. Pour plus de précisions sur le contrôle de la granularité de
verrouillage, reportez-vous à la section 7.2.2.
L’option MAXDOP spécifie le degré de parallélisme à appliquer à l’opération de création de l’index. Le parallélisme pour un CREATE INDEX n’est pris en charge que par
l’édition Entreprise.
Vous pouvez créer des index composites, c’est-à-dire des index multicolonnes. Ils
sont utiles pour résoudre des clauses de filtres qui utilisent toujours les mêmes colonnes (par exemple une recherche systématique avec date de début et date de fin).
Dans ce cas, l’ordre de déclaration des colonnes dans la clé est important. Placez la
colonne sur laquelle vous cherchez le plus souvent, en premier. Ainsi l’index peut
être utile également pour les recherches sur cette colonne uniquement. Vous pouvez
vous représenter la clé d’un index comme la concaténation de toutes les colonnes
qui la composent (plus la clé de l’index clustered à la fin). SQL Server pourra utiliser
cet index pour une recherche sur la première colonne, sur la première et la
deuxième, etc. Une recherche exclusivement sur la deuxième colonne ne pourra
profiter de l’index, car on ne peut le parcourir qu’en le prenant au début de la clé.
C’est tout à fait comme un annuaire : vous pouvez chercher par nom, ou par nom et
prénom, puisqu’il est organisé par ordre alphabétique de noms de famille. Chercher
les personnes ayant un même prénom dans un annuaire, est impossible. Vous ne pouvez que parcourir toutes les pages.
La clé d’un index est limitée à 16 colonnes, et 900 octets. N’en arrivez pas jusque-là… Cette limite n’est pas applicable aux colonnes incluses.
Si les colonnes de l’index composite sont cherchées toujours ensembles, placez
les colonnes les plus sélectives (contenant le plus de valeurs uniques) en premier,
cela augmentera la sélectivité générale de l’index, et le rendra plus utile, et plus intéressant pour l’optimiseur. Faites ici encore la comparaison avec un annuaire : il est
organisé par nom et prénom, non par prénom et nom, aussi parce qu’il est plus
rapide, pour trouver Pierre Bourdieu, de chercher Bourdieu, et dans ceux-ci, de trouver Pierre, que l’inverse.
Chapitre 6. Utilisation des index
sont modifiées, car SQL Server doit maintenir deux structures d’index, et elle utilise
activement tempdb, mais elle offre l’avantage d’un meilleur accès concurrentiel aux
tables. Cette option n’est disponible qu’en édition Entreprise. Pour plus de détail sur
son fonctionnement interne, reportez-vous aux BOL, entrée « How Online Index
Operations Work ».
ALLOW_ROW_LOCKS et ALLOW_PAGE_LOCKS contrôlent le verrouillage sur l’index
(donc sur la table dans le cas d’un index clustered). Par défaut, les verrous sont posés
sur des clés d’index, et escaladés au besoin. Les verrous sur les clés permettent une
meilleure concurrence d’accès, mais peuvent être plus coûteux à gérer si beaucoup de
lignes sont appelées. ALLOW_ROW_LOCKS à OFF désactive la pose de verrous au niveau
clé (ligne), et donc force les verrous sur les pages. Couplé avec ALLOW_PAGE_LOCKS à
OFF, cela force les verrous sur la table. En général il n’y a rien à changer dans ces
options. Elles peuvent éventuellement faire gagner du temps sur des requêtes larges
de type OLAP, mais dans ces requêtes, le passage en niveau d’isolation READ UNCOMMITTED est plus efficace. Pour plus de précisions sur le contrôle de la granularité de
verrouillage, reportez-vous à la section 7.2.2.
L’option MAXDOP spécifie le degré de parallélisme à appliquer à l’opération de création de l’index. Le parallélisme pour un CREATE INDEX n’est pris en charge que par
l’édition Entreprise.
Vous pouvez créer des index composites, c’est-à-dire des index multicolonnes. Ils
sont utiles pour résoudre des clauses de filtres qui utilisent toujours les mêmes colonnes (par exemple une recherche systématique avec date de début et date de fin).
Dans ce cas, l’ordre de déclaration des colonnes dans la clé est important. Placez la
colonne sur laquelle vous cherchez le plus souvent, en premier. Ainsi l’index peut
être utile également pour les recherches sur cette colonne uniquement. Vous pouvez
vous représenter la clé d’un index comme la concaténation de toutes les colonnes
qui la composent (plus la clé de l’index clustered à la fin). SQL Server pourra utiliser
cet index pour une recherche sur la première colonne, sur la première et la
deuxième, etc. Une recherche exclusivement sur la deuxième colonne ne pourra
profiter de l’index, car on ne peut le parcourir qu’en le prenant au début de la clé.
C’est tout à fait comme un annuaire : vous pouvez chercher par nom, ou par nom et
prénom, puisqu’il est organisé par ordre alphabétique de noms de famille. Chercher
les personnes ayant un même prénom dans un annuaire, est impossible. Vous ne pouvez que parcourir toutes les pages.
La clé d’un index est limitée à 16 colonnes, et 900 octets. N’en arrivez pas jusque-là… Cette limite n’est pas applicable aux colonnes incluses.
Si les colonnes de l’index composite sont cherchées toujours ensembles, placez
les colonnes les plus sélectives (contenant le plus de valeurs uniques) en premier,
cela augmentera la sélectivité générale de l’index, et le rendra plus utile, et plus intéressant pour l’optimiseur. Faites ici encore la comparaison avec un annuaire : il est
organisé par nom et prénom, non par prénom et nom, aussi parce qu’il est plus
rapide, pour trouver Pierre Bourdieu, de chercher Bourdieu, et dans ceux-ci, de trouver Pierre, que l’inverse.