Savoir utiliser les index

Chapitre 06

  55 minutes à lire  

Objectif

Vous n’ignorez pas que l’indexation est un sujet important. Sans index, les SGBDR n’auraient aucune utilité, car ils ne pourraient permettre aucune recherche rapide dans les données. Toute manipulation dans les tables obligerait le moteur à la parcourir de bout en bout. Même une simple contrainte d’unicité serait irréalisable. Comprendre et utiliser les index au maximum de leurs possibilités est ce qui va vous donner le levier le plus important pour augmenter les performances de vos bases de données. Nous allons donc les voir en détail.

Principes de l’indexation

Vous êtes Administrateur de Bases de Données, votre vie professionnelle est donc bien remplie. Mais cela ne soulage pas des grandes questions métaphysiques : quel est le sens de la vie ? Pour y répondre, vous décidez de rencontrer un philosophe. Qu’allez-vous faire pour en trouver un dans votre ville ? Bien entendu, vous allez consulter l’annuaire téléphonique, et plus précisément les pages jaunes. Si vous ne disposez pas d’annuaire, vous n’avez d’autre solution que de errer dans les rues, pour y chercher au hasard, sur une plaque ou une boîte aux lettres, la mention du métier de philosophe.

SQL Server n’est pas différent. Lorsque vous filtrez le contenu d’une table dans une requête, à l’aide en général d’un prédicat dans une clause WHERE, SQL Server doit parcourir la table pour évaluer ce prédicat sur chaque ligne. Cette opération est évidemment très coûteuse, proportionnellement à la taille de la table. Toute la table doit être mise en mémoire et parcourue, c’est-à-dire que toutes les pages de données qui contiennent la table sont chargées, ce qui peut signifier des giga-octets, pour un simple SELECT… Ce parcours est appelé un scan.

Le seul moyen d’éviter cela, est de créer un index. Un index, comme l’index à la fin d’un livre, est une structure d’arbre de recherche permettant une recherche dichotomique. Cet arbre est appelé arbre équilibré ou arbre-B (balanced tree, ou B-Tree).

Le principe est de permettre aux nœuds parents de posséder plus de deux nœuds enfants : c’est une généralisation de l’arbre binaire de recherche. Ce principe minimise la taille de l’arbre et réduit le nombre d’opérations d’équilibrage. De plus un B-arbre grandit à partir de la racine, contrairement à un arbre binaire de recherche qui croît à partir des feuilles. (Article Wikipedia)

Arbre
En théorie des graphes, un arbre est un graphe acyclique et connexe.
Connexité : il est toujours possible d’aller d’un sommet à l’autre par un chemin d’arêtes. Dans le cas de la carte routière, cela revient à dire qu’il est toujours possible d’aller d’une ville à l’autre par la route.
Acyclique : il est impossible de partir d’un sommet et d’y revenir sans rebrousser chemin à un moment. (Article Wikipedia Arbre (Théorie des graphes))

Fig. 6.1 – arbre équilibré

La figure 6.1 nous montre une recherche à travers l’arbre de l’index. Cette représentation en arbre est schématique, l’index est physiquement stocké dans des pages de données identiques à celles qui abritent le contenu des tables. Nous verrons ce stockage un peu plus loin. L’arbre B-Tree est constitué de nœuds, ou niveaux, qui sont parcourus pour trouver l’information. La figure 6.2 montre un index à 4 niveaux. Le premier nœud, appelé nœud racine (root node), est le point d’entrée de la recherche. Dans notre cas, nous cherchons le mot « philosophe », nous sommes donc dirigés d’abord vers la plage alphabétique comportant la lettre P. Au fur et à mesure que nous descendons les nœuds intermédiaires (intermediate node), le choix s’affine, jusqu’au dernier niveau, le nœud feuille (leaf node), où réside chaque occurrence de la clé, avec la référence des lignes de la table qui la contiennent. Ces références se nomment des RID (Row ID), ce sont des combinaisons de numéros de fichier, page et ligne, qui permettent au moteur de stockage de pointer sur la ligne.

Fig. 6.2 – nœuds de l’index

Cette recherche à travers l’arborescence de l’index est représentée dans le plan d’exécution comme un seek. La récupération du RID est appelée un bookmark lookup, ou RID lookup. Vous en trouvez un exemple dans le plan d’exécution graphique reproduit en figure 6.3.

fig. 6.3 – plan d’exécution, seek et RID lookup

Vous voyez dans ce plan d’exécution que RID lookup coûte 57 % relativement au coût total de la requête. Il s’agit donc d’une opération lourde, nous reviendrons sur ce point. La recherche de chaque RID est exprimée par un opérateur de boucle imbriquée (nested loop). Cela veut simplement dire que pour RID trouvé par le seek de l’index, il devra se produire une recherche de RID dans la table heap. Un peu comme les épreuves de l’émission télévisée « Intervilles ». Une équipe doit transporter des seaux d’eau à travers un parcours difficile, mais chaque participant ne peut bien sûr transporter que deux seaux à la fois. Ils doivent donc constamment revenir au point de départ pour prendre les seaux restant. C’est le nested loop : à chaque apparition de nouveau match dans l’index, le moteur de requête « déréférence » le pointeur RID et va chercher physiquement la ou les lignes dans la page de données.

Chaque niveau de l’index est une liste doublement liée entre les pages de ce niveau. En d’autres termes, chaque page d’index contient les identifiants de la page précédente, et de la page suivante, du niveau. Cela permet à SQL Server de parcourir (scan) le niveau dans l’ordre de la clé.

Scan avancé – Édition Entreprise

L’édition Entreprise offre une optimisation avancée des scans (Advanced Scanning) : lorsque plusieurs requêtes doivent parcourir simultanément la même table, l’édition Entreprise peut partager ce parcours entre les processus. La fonctionnalité est appelée « parcours en manège » (Merry-go-round scanning). Un processus se joint au parcours déjà initié, et lorsque celui-ci est terminé, il le poursuit sur les pages qui n’ont pas été parcourue depuis son entrée dans le circuit. Cela peut fortement diminuer le temps de réponse de requêtes qui doivent effectuer des scan importants, en partageant le travail et en diminuant l’attente sur les verrous.

Contrairement à d’autres SGBD, qui implémentent des index de type bitmap, ou en hachage, il n’y a pour l’instant qu’une seule structure physique d’index en SQL Server : le B-Tree (à part l’index de texte intégral, un peu à part). Même les index XML, et les index spatiaux sur les données géographiques sont en interne des index de structure B-Tree, sur une table interne pour le XML, et sur une décomposition hiérarchique de l’espace pour les index spatiaux. L’index B-Tree peut être de deux types : nonclustered et clustered (parfois appelés non-ordonné, et ordonné).

L’index clustered

Nous avons vu que le niveau feuille de l’index pointe sur un RID, et que l’opération de RID lookup permet de retrouver les lignes de la table même. Cette structure, où l’index est physiquement séparé de la table et où les clés sont liées aux lignes par un pointeur, est un index nonclustered. Un autre type d’index B-Tree, nommé clustered, réorganise physiquement les pages de données selon la clé de l’index. Commençons par une démonstration.

USE tempdb
GO

CREATE TABLE dbo.indexdemo (
  id int NOT NULL IDENTITY(1,1),
  texte char(100) NOT NULL
  DEFAULT (REPLICATE(CHAR(CEILING(RAND()*100)), 100))
);

GO

INSERT INTO dbo.indexdemo (id) DEFAULT VALUES;
GO 10

SELECT * FROM dbo.indexdemo
GO

À l’issue de l’exécution de ce code, nous obtenons un résultat semblable à ce que nous voyons sur la figure 6.4.

Fig. 6.4 – résultat du SELECT

Les lignes apparaissent « naturellement » dans l’ordre d’insertion, qui correspond ici à un ordre de nombres dans la colonne Id, simplement parce que nous avons inséré les lignes avec un incrément de la valeur de Id. Si nous avions inséré des Id dans le désordre, ils seraient apparus dans le désordre. Voyons maintenant ce qui se passe si nous supprimons, puis ajoutons une ligne :

DELETE FROM dbo.indexdemo WHERE id = 4

INSERT INTO dbo.indexdemo (id) VALUES (9)
GO

SELECT * FROM dbo.indexdemo
GO

Résultat sur la figure 6.5.

Fig. 6.5 – résultat du SELECT

Vous voyez que la ligne portant l’Id 9 est insérée à la place de la ligne supprimée. La suppression a libéré 104 octets dans la page, une insertion de la même taille s’est faite dans l’espace laissé libre. Cela permet de remplir la page. Une table heap n’est vraiment organisée selon aucun ordre logique ou physique. Si nous voulons obtenir un résultat ordonné, nous devons le spécifier dans la requête :

SELECT * FROM dbo.indexdemo ORDER BY id;

Ce qui nécessitera un tri dans la plan de la requête, comme nous le voyons dans le plan graphique, sur le figure 6.6.

Fig. 6.6 – plan d’exécution

Nous pouvons créer un index sur la colonne id, pour permettre ce tri. Essayons :

CREATE NONCLUSTERED INDEX nix$dbo_indexdemo$id
ON dbo.indexdemo (id ASC);
GO

SELECT * FROM dbo.indexdemo ORDER BY id;

Nous voyons la différence de plan d’exécution sur la figure 6.7.

Fig. 6.7 – plan d’exécution

SQL Server parcourt le nœud feuille de l’index, qui lui donne rapidement l’ordre par id. Ensuite, il doit faire un RID lookup sur chaque ligne.

Précision : ici, l’exemple donne un plan d’exécution peu optimal. En effet, la boucle imbriquée implique de faire une lecture par occurrence dans le parcours de l’index, donc de lire chaque fois une page. Mais, notre petite table tient toute entière dans une page de données. Donc, au lieu de lire une page, puis de trier, SQL Server fait dix lectures. À ce volume de données, ce n’est pas important, et SQL Server choisit un plan d’exécution « acceptable ». Si la cardinalité de la table est plus importante, l’optimiseur fera le choix d’un scan, même en présence de l’index.

Supprimons cet index, et créons à la place, un index clustered :

DROP INDEX nix$dbo_indexdemo$id
ON dbo.indexdemo;
GO

CREATE CLUSTERED INDEX cix$dbo_indexdemo$id
ON dbo.indexdemo (id ASC);
GO

que donne un simple

SELECT * FROM dbo.indexdemo;

Nous voyons le résultat sur la figure 6.8.

Fig. 6.8 – résultat du SELECT

L’ordre des lignes a changé, même sans que nous n’ayons spécifié une clause ORDER BY. Que s’est-il passé ? Simplement, la création de l’index clustered a réorganisé les lignes de la table dans la page de données, selon l’ordre de la clé de l’index clustered. En d’autre termes, la table est maintenant physiquement ordonnée selon l’index clustered. Désormais, toute ligne insérée ou supprimée sera placée à un endroit précis dans l’ordre de la table. Si nous supprimons une ligne, en ajoutons une autre, nous verrons que, contrairement à l’exemple précédent sur notre table heap, les lignes retournée par ce simple SELECT seront toujours dans l’ordre le l’Id, car c’est l’ordre physique des lignes dans la table.

Attention : ce comportement ne vous dédouane pas de spécifier la clause ORDER BY si vous souhaitez récupérer un jeu de résultat dans un tri précis. Vous n’avez aucune garantie que l’ordre des lignes sera consistant, même s’il existe un index clustered. Dans certains opérateurs de requête (les UNION, les jointures par exemple), SQL Server peut choisir de pré-trier un résultat intermédiaire pour rendre les opérations suivantes plus rapides. De même, le moteur de stockage peut retourner les pages dans un ordre favorable aux performances plutôt que dans leur ordre « logique ».

Qu’en est-il de la requête avec ORDER BY id, et de son plan d’exécution ? Réponse sur la figure 6.9.

Fig. 6.9 – plan d’exécution

Plus besoin d’opérateur de tri : la table est déjà dans l’ordre demandé. Il suffit de faire un scan de l’index clustered. Mais, pourquoi n’avons-nous plus de RID lookup ? Comment SQL Server fait-il pour aller chercher la colonne [texte] que nous voulons afficher ? Utilisons DBCC IND pour observer la table, et l’index. Le dernier paramètre de DBCC IND indique l’id de l’index. Un index clustered a toujours l’id 1. Regardons donc ce que donne DBCC IND sur l’index 0 (la table elle-même), et l’index 1 (l’index clustered) :

DBCC IND ('tempdb', 'dbo.indexdemo', 0);

DBCC IND ('tempdb', 'dbo.indexdemo', 1);

Résultat sur la figure 6.10.

Fig. 6.10 – résultat de DBCC IND

Ici nous constatons plusieurs choses : le résultat pour la table et pour l’index clustered est le même. La page d’index et la page de table ont le même id, c’est donc la même. Nous voyons aussi que la page de données se trouve au niveau 0 (le nœud feuille) de l’index clustered (1). Cela signifie donc que la page de données, et la page du niveau feuille de l’index clustered, est la même.

En effet, lorsque vous créez un index clustered, la table devient partie de l’index : le noeud feuille de l’index clustered est… la table elle-même. Il est important de comprendre ce fait. Une table de type heap, et une table clustered, sont deux objets bien différents. Lorsque SQL Server effectue un seek dans un index clustered, il ne quitte jamais l’index, et la notion de RID n’existe plus. Quand la recherche arrive au dernier niveau de l’index, elle est terminé, car on se trouve déjà au bon endroit : dans la page de données. Ainsi, contrairement à un index nonclustered, ce qui se trouve dans le nœud feuille n’est pas uniquement la clé de l’index, mais aussi toutes les autres colonnes de la table.

Cela implique également que la table clustered comporte un ordre de pages. Comme la table est le dernier niveau de l’index, elle maintient comme les index une liste doublement liée de ses pages. Cela permet un scan de la table, pour retrouver des lignes dans l’ordre de la clé de l’index clustered. Une table heap, par contre, n’a aucune notion de page précédente ou page suivante, puisque ses lignes ne sont organisées dans aucun ordre explicite. Pour un heap, SQL Server sait qu’une page fait partie de la table, en inspectant les pages IAM de la table.

Note : bien qu’elle n’ait aucune particularité d’un index, la table heap est elle aussi, en interne, considérée comme un index : elle apparaît dans la vue sys.indexes, comme un index de type heap, et ses pages sont allouées également par des pages d’IAM (Index Allocation Map).

Qu’en est-il des index nonclustered sur une table clustered ? Ils sont aussi d’une autre espèce. Observons leur différence fondamentale. Pour cela, nous allons ajouter un certain nombre de lignes dans notre table de démo, et une troisième colonne que nous allons indexer :

DECLARE @i int

SELECT @i = MAX(id)+1 FROM dbo.indexdemo;

WHILE @i <= 2000 BEGIN
  INSERT INTO dbo.indexdemo (id) SELECT @i;
  SET @i = @i + 1
END

ALTER TABLE dbo.indexdemo
ADD petittexte CHAR(1) NULL;
GO

UPDATE dbo.indexdemo
SET petittexte = CHAR(ASCII('a')+(id%26))
GO

CREATE NONCLUSTERED INDEX nix$dbo_indexdemo$petittexte[^19]
ON dbo.indexdemo (petittexte ASC);
GO

SELECT * FROM dbo.indexdemo WHERE petittexte = 'c';

Nous pouvons voir le plan d’exécution de la dernière requête SELECT dans la figure 6.11.

Fig. 6.11 – plan d’exécution

Pourquoi avons-nous augmenté le nombre de ligne à 2000 ? Sans cela, le plan d’exécution choisi aurait été un scan de table. Le coût du lookup étant important, l’optimiseur choisit plus volontiers un scan de la table si le nombre de pages est petit.

Ici, plus de RID lookup, mais un « Key Lookup ». La description de cet opérateur nous le dit clairement : c’est l’utilisation de la clé de l’index clustered pour effectuer la recherche dans la table. La conclusion est simple : l’index nonclustered, dans son nœud feuille, ne fournit plus de RID pour la recherche, mais la clé de l’index clustered, de sorte que, dans ce cas, il y a en réalité un double seek d’index. Lorsqu’un seek est effectué dans un index nonclustered créé sur une table clustered, arrivé au nœud feuille de l’index nonclustered, la clé de l’index clustered est trouvé, et ce dernier est traversé à son tour. Ceci est schématisé par la figure 6.12.

Fig. 6.12 – parcours du Key Lookup

Mais c’est encore un peu plus que cela. La clé de l’index clustered est ajoutée à la fin de la clé de l’index nonclustered. Ce qui fait que, implicitement, l’index nonclustered est un index composite, contenant en plus la ou les clés de l’index clustered. Ce n’est pas montré dans la définition de l’index, ni dans la vue système sys.index_columns, mais si vous observez l’index, soit avec DBCC SHOW_STATISTICS (que nous verrons plus loin), soit avec DBCC PAGE, vous pouvez voir cela. Par exemple :

DBCC IND ('tempdb', 'dbo.indexdemo', 4)
GO

DBCC TRACEON (3604);

DBCC PAGE (tempdb, 1, 218, 3);
GO

Chez nous, l’index nonclustered nix$dbo_indexdemo$petittexte a l’index_id 4. En exécutant DBCC IND, nous voyons que la page contenant le nœud racine porte le numéro 218, nous utilisons DBCC PAGE pour voir le contenu de ce nœud (plus de détail sur l’utilisation de ces commandes DBCC dans la section « Structure interne de l’index »). Le résultat est montré dans la figure 6.13.

Fig. 6.13 – résultat de DBCC PAGE

Nous y voyons trois colonnes de clé : petittexte, id, et un unifiant (UNIQUIFIER), ici toujours NULL. La colonne id fait donc bien partie de la clé de l’index, déjà au nœud racine.

L’UNIQUIFIER est utilisé dans le cas d’un index clustered non unique. Un index clustered comporte, en interne, nécessairement des clés uniques (sinon, comment un Key Lookup pourrait trouver une seule ligne ?). Si un tel index est créé sans contrainte d’unicité (index unique, ou sur une clé primaire ou unique), SQL Server ajoute une valeur unifiante à la fin de la clé lorsque des valeurs doublonnées sont présentes dans la clé.

cela veut-il dire que SQL Server va beaucoup déplacer les lignes de pages ?

SELECT
i.index_id, i.name, i.type_desc,
i.is_primary_key, fill_factor,
INDEX_COL(OBJECT_NAME(i.object_id), i.index_id, ic.index_column_id)
as column_name
FROM sys.indexes i
JOIN sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('dbo.indexdemo')
ORDER BY i.index_id, ic.key_ordinal;

Donc, un seek est un parcours de l’arbre équilibré de l’index, pour obtenir avec la ligne correspondante avec un maximum d’efficacité. Plus le nombre de seeks est élevé, plus l’index est réellement utilisé à sa pleine capacité. Alors qu’un scan (« analyse » dans la traduction française de SQL Server) est le parcours complet du noeud feuille (leaf node) de l’index. Le noeud feuille est le dernier niveau, autrement dit la base de l’index, où chaque recherche se termine. Un scan sur un index ordonné (clustered) correspond à un scan sur la table, puisque le nœud feuille d’un index ordonné est la table elle-même.

Le lookup (bookmark lookup), ou « recherche de clés » dans la traduction française, correspond à la nécessité, lorsqu’une recherche sur l’index non ordonné a atteint le noeud feuille, de retrouver les lignes correspondantes dans la table. Les colonnes lookup de la vue sys.dm_db_index_usage_stats indiquent que l’index a participé à une opération de recherche de clés. Elle n’a de sens que sur un index ordonné. En effet, la recherche de clé ne se produit qu’à partir d’un index non ordonné. Cette recherche peut se faire soit sur un RID (Row ID, ou identifiant de ligne) dans le cas d’une table « heap » (sans index ordonné), soir sur la clé de l’index ordonné si la table en comporte un. Dans ce dernier cas, la recherche de clé se fait donc par un parcours de l’index ordonné. C’est ce parcours qui est indiqué dans les colonnes lookup de la vue dynamique.

Le bookmark lookup est une opération lourde, parce qu’elle provoque des lectures aléatoires (random IO), c’est-à-dire des lectures non pas en séquence de lignes dans les pages, mais de lignes qui peuvent se situer n’importe où. Comme lorsque vous cherchez dans un dictionnaire : il est beaucoup plus coûteux de prendre chaque nom dans l’index de fin d’ouvrage, et de vous rendre à la bonne page, que d’avoir un dictionnaire dont les entrées sont déjà ordonnée, et de feuilleter les pages sur la lettre qui vous intéresse. L’optimiseur essaie donc d’éviter autant que possible le bookmark lookup, en choisissant un scan dès que le nombre de lignes à retourner atteint un certain seuil.

Un index a une profondeur (le nombre de niveaux de l’arbre) et une étendue (le nombre de clés, et physiquement de pages à chaque niveau). Bien entendu, l’étendue augmente au fur et à mesure que l’on descend dans les niveaux.

6.1.2 Choisir son index

Dans quel cas choisir un index clustered, et dans quel cas choisir un index nonclustered ?

Comme le dernier niveau de l’index est la ligne elle-même, toute recherche à travers l’index clustered sera très rapide, puisque l’étape de lookup sera inutile. L’index clustered est à choisir soigneusement, car, bien entendu, on ne peut créer qu’un seul index clustered par table (comme on ne peut avoir qu’un seul ordre alphabétique dans un dictionnaire… ce n’est que dans le monde de la physique quantique qu’un objet peut se dupliquer et prendre deux natures différentes. Une table ne peut, elle, être physiquement triée par deux clés différentes à la fois).

Comme l’index clustered ordonne physiquement les lignes, il est aussi idéal sur des recherches de plages de valeurs. Les clauses de recherche utilisant un BETWEEN, ou une syntaxe comme :

SELECT *
FROM dbo.indexdemo
WHERE id > 10 AND id < 50;

profitera aussi grandement d’un index clustered.

D’un autre côté, tout ajout d’une ligne entraîne inévitablement une réorganisation physique de la table. L’ordre des lignes doit se maintenir, et cela a un coût. L’ajout d’une ligne dont la clé clustered doit s’insérer à l’intérieur de l’index, peut provoquer une séparation (split) de page, et donc entraîner des écritures coûteuses du moteur de stockage. Pour éviter ce coût de maintenance, il est fortement recommandé de créer un index clustered sur une colonne qui augmente de façon séquentielle (on dit aussi monotone), comme un autoincrémental (IDENTITY) ou une colonne d’horodatage. Pour la même raison, les mises à jour de la valeur de la clé sont déconseillées. Pour ces raisons, on crée souvent l’index clustered sur la clé primaire de la table – d’ailleurs la clé primaire est par défaut créée clustered. Dans un modèle de données bien conçu, à quelques rares exceptions près, la clé primaire est immuable (on ne change pas un identifiant référencé dans des tables filles…). L’index clustered est aussi préférable sur une colonne, ou un ensemble de colonnes, unique. Nous avons vu qu’en interne, SQL Server doit unifier les clés de l’index clustered si elle ne le sont pas déjà, simplement parce que pour ordonner les lignes, il ne peut tolérer de doublon. Créer un index clustered sur une clé non unique augmente la taille de l’index. C’est un défaut avec lequel on va parfois composer, car les avantages de l’index clustered sont réels. Ceci dit, il ne faut pas sous-estimer l’importance de conserver un index clustered aussi petit que possible. Comme sa clé est copiée dans la clé de tous les autres index de la table, il influence la taille de tous les index, et donc leur efficacité, et leur utilité. Prenons un peu d’avance sur la section traitant des statistiques, et faisons une expérience :

DROP INDEX cix$dbo_indexdemo$id
ON dbo.indexdemo;

CREATE CLUSTERED INDEX cix$dbo_indexdemo$texte
ON dbo.indexdemo (texte ASC);
GO

DBCC SHOW_STATISTICS ('dbo.indexdemo',
'nix$dbo_indexdemo$petittexte');

Nous supprimons l’index clustered sur notre table de test, et nous le recréons, mais cette fois-ci sur la colonne texte, qui est de type CHAR(100). Voyons comment l’index nix$dbo_indexdemo$petittexte, sur la colonne petittexte (CHAR(1)), a réagi. La commande DBCC SHOW_STATISTICS nous permet d’obtenir des détails sur la structure de l’index, qui sont ceux qu’utilise l’optimiseur SQL pour juger de sa pertinence pour répondre à une requête. Voyons un extrait du résultat (nous n’avons gardé que les lignes et colonnes utiles pour notre propos) :

Name Rows Density Average key length
-----------------------------------------------------------------------
nix$dbo_indexdemo$petittexte 2007 0,1304348 100,0992
All density Average Length Columns
----------------------------------------------
0,03703704 0,09915297 petittexte
0,003496503 100,0992 petittexte, texte

Notons au passage ce qui s’est produit : nix$dbo_indexdemo$petittexte a été recréé, puisqu’il doit contenir la clé de l’index clustered, et que celui-ci a changé. Méfiez-vous de cet effet de bord : la modification d’un index clustered entraîne la recréation de tous les autres index, et donc un travail important, augmenté d’un blocage (verrouillage) de la table durant une période souvent longue (voyez plus loin l’option ONLINE pour limiter ce temps de blocage).

L’index nix$dbo_indexdemo$petittexte, dont la clé était auparavant de 5 octets (1 octet pour la colonne petittexte, et 4 octets pour la colonne id), en fait maintenant (environ) 101. L’optimiseur va orienter son choix en regard de cette information. Plus la clé est longue, plus l’index sera lourd à parcourir.

La taille maximale d’un clé d’index est de 900 octets. Nous en sommes loin ici, mais déjà, l’utilité de l’index est compromise. Un index dont la clé fait 900 octets, est très peu utile, et même dangereux si l’index est clustered.

Couverture de requête

L’index comporte une ou plusieurs colonnes de la table dans sa clé. Cela veut donc dire que la valeur de ces colonnes est connue de l’index, et que toutes ces valeurs sont présentes dans son nœud feuille. Par exemple, un index sur la colonne LastName de la table Person.Contact contient tous les LastName. Dans ce cas, nous souhaitons dans notre requête ne récupérer que les noms, l’index tout seul suffit à y répondre :

SELECT DISTINCT LastName
FROM Person.Contact
WHERE LastName LIKE 'A%'
ORDER BY LastName;

Donnera ce plan d’exécution[^20] :

|--Stream Aggregate(GROUP BY:([LastName]))
|--Index Seek(OBJECT:([nix$Person_Contact$LastName]),
SEEK:([LastName] >= N'A' AND [LastName] < N'B'),
WHERE:([LastName] like N'A%') ORDERED FORWARD)

Vous voyez que, non seulement l’index a été utilisé, mais en plus en seek (avec une réécriture du LIKE en clause acceptable pour un seek), et non pas en scan, même si l’estimation de la cardinalité retournée est de 911 lignes. Ceci parce qu’il n’y a pas de bookmark lookup, donc une stratégie de seek est beaucoup plus intéressante.

Cette utilisation de l’index pour retourner les colonnes demandées, est appelée couverture. Ici, l’index nix$Person_Contact$LastName couvre notre requête. Un index couvrant est extrêmement intéressant pour les performances, car l’index est une structure non seulement optimisée pour la recherche, mais plus compacte que la table, puisqu’il contient moins de colonnes : scanner le nœud feuille d’un index nonclustered 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 ION 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 maintenance. La mise à jour de colonnes incluses provoque des écritures dans l’index, et potentiellement des splits de pages d’index lorsque la colonne est de taille variable.

Index filtré et contrainte d’unicité

Nous le savons, NULL n’est pas une valeur, c’est même le contraire d’une valeur, c’est l’absence de valeur, l’inconnu. Comment gérer cet inconnu dans une contrainte d’unicité ? La norme SQL indique en toute logique qu’une contrainte d’unicité doit accepter de multiples marqueurs NULL. Dans la prise en charge par SQL de la logique à trois états, le NULL est discriminant. Une requête comme :

SELECT *
FROM Person.Contact
WHERE Title = 'Mr.'

les lignes dont le Title est NULL ne sont pas retournées. Ces lignes pourraient correspondre à un Title = ‘Mr.’, mais nous n’en savons rien, nous ne pouvons donc les considérer. Le même phénomène s’applique à l’unicité. Une colonne contenant le marqueur NULL pourrait correspondre à une valeur déjà entrée, mais nous n’en savons rien, par conséquent, une contrainte d’unicité devrait accepter les NULL, et remettre sa vérification au moment où la colonne est réellement alimentée d’une valeur.

SQL Server ne réagit pas ainsi. Un seul marqueur NULL est accepté dans une contrainte d’unicité ou dans un index unique, sans doute en partie parce que, pour SQL Server, NULL est une clé d’index comme une autre (une recherche WHERE …

En SQL Server 2008, une clause de filtre est introduite à la création d’index. Elle est intéressante pour notre cas, mais aussi pour optimiser des index sur des colonnes dont seules quelques valeurs sont sélectives. Vous pouvez simplement ajouter une clause WHERE à la commande de création d’index nonclustered :

CREATE UNIQUE INDEX uqf$Person_Contact$EmailAddress
ON Person.Contact (EmailAddress)
WHERE EmailAddress IS NOT NULL;

Les lignes qui ne correspondent pas à la clause WHERE ne sont simplement pas pris en compte dans l’index. De fait, ce filtre nous permet d’implémenter un index unique répondant à la norme SQL. La clause WHERE accepte les comparaisons simple, et l’utilisation du IN.

Mais la création d’index filtrés n’est pas limitée aux index uniques, vous pouvez l’utiliser sur des colonnes très peu sélectives pour certaines valeurs et très sélectives pour d’autres (en incluant dans le filtre seulement les valeurs très sélectives), pour diminuer la taille de l’index et le rendre plus efficace. En voici un exemple, qui crée un index filtré sur une colonne de type bit, censée représenter un flag actif/inactif :

CREATE TABLE dbo.testfiltered (
  id int NOT NULL PRIMARY KEY,
  Active bit NOT NULL,
  fluff char(896) NOT NULL DEFAULT ('b')
);

INSERT INTO dbo.testfiltered (id, Active)
SELECT ContactId, 0 FROM AdventureWorks.Person.Contact

UPDATE t1 SET Active = 1
FROM dbo.testfiltered t1
JOIN (SELECT TOP (10) id FROM dbo.testfiltered ORDER BY NEWID()) t2
ON t1.id = t2.id

CREATE INDEX nix$dbo_testfiltered$active
ON dbo.testfiltered (Active)
WHERE Active = 1;

CREATE INDEX nix$dbo_testfiltered$activeNotFiltered
ON dbo.testfiltered (Active);

DBCC SHOW_STATISTICS ('dbo.testfiltered',
'nix$dbo_testfiltered$active')

DBCC SHOW_STATISTICS ('dbo.testfiltered',
'nix$dbo_testfiltered$activeNotFiltered')

SELECT *
FROM dbo.testfiltered
WHERE Active = 1;

Dans cet exemple, l’index nix$dbo_testfiltered$active est choisi par l’optimiseur : il est plus compact que l’index non filtré, et donc plus rapide à parcourir. Nous avons délibérément ajouté une colonne nommée fluff pour alourdir la table, donc le nombre de pages qui la contiennent. Nous avons aussi utilisé une astuce pour mettre à jour dix lignes aléatoire, à l’aide d’un ORDER BY NEWID() : NEWID() retournant un GUID différent à chaque ligne. Cette astuce est coûteuse en performances, elle n’est à utiliser que dans ce type de tests.

6.1.3 Création d’index

Après avoir présenté les différents types d’index, il nous reste à parler simplement de la création de ceux-ci. Nous avons vu dans nos codes d’exemple, la syntaxe de création. Nous pouvons la résumer ainsi :

CREATE [ UNIQUE ] [ CLUSTERED | [NONCLUSTERED]{.ul} ] INDEX
index_name
ON <objet> ( colonne [ [ASC]{.ul} | DESC ] [ ,...n ] )
[ INCLUDE ( colonne [ ,...n ] ) ]
*[ WHERE <filtre> ]*
[ WITH ( <option> [ ,...n ] ) ]
[ ON { partition_schema ( colonne )
| filegroup
| default
}
]
*[ FILESTREAM_ON { filestream_filegroup | partition_schema | \"NULL\"
}* ]
<option> ::=
{

PAD_INDEX = { ON | [OFF]{.ul} }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | [OFF]{.ul} }
| DROP_EXISTING = { ON | [OFF]{.ul} }
| ONLINE = { ON | [OFF]{.ul} }
| ALLOW_ROW_LOCKS = { [ON]{.ul} | OFF }
| ALLOW_PAGE_LOCKS = { [ON]{.ul} | OFF }
| MAXDOP = max_degree_of_parallelism
| *DATA_COMPRESSION = { [NONE]{.ul} | ROW | PAGE}*
*[ ON PARTITIONS ( { <partition_number_expression> | <range> }*
*[ , ...n ] ) ]*
}

Les valeurs soulignées représentent les valeurs par défaut, et les instructions en italiques ne sont valables que pour SQL Server 2008.

Voyons rapidement quelques unes de ces options.

UNIQUE vous permet de créer une contrainte sur la ou les colonnes qui composent la clé. Au niveau du moteur, elle est strictement équivalente à la contrainte d’unicité, qui crée elle-même un index unique de ce type.

Pour chaque colonne de la clé, vous pouvez indiquer l’ordre de tri : ASC ou DESC. Si vous ordrez principalement dans l’ordre descendant (par exemple les lignes les plus récentes d’abord, à l’aide d’un index sur une date), créez l’index directement en DESC, cela rendra le scan plus rapide.

Les options FILLFACTOR et PAD_INDEX détermine l’espace laissé libre dans les pages d’index. Lorsque vous créez une index sur une table qui est très souvent modifiée, chaque modification entraîne une opération de maintenance de l’index. Nous nous souvenons que l’index maintient chacun de ses niveaux dans l’ordre de la clé, à l’intérieur des pages d’index comme de page en page, à l’aide d’une liste doublement liée. Cette maintenance entraîne des réorganisations dans la page, et entre les pages, lorsque des valeurs de clé sont insérées. Imaginons une armoire de boîtes à fiches, contenant par exemple des dossiers individuels. Lorsque de nouveaux dossier sont ajoutés, ils peuvent remplir complètement une boîte à fiche. Il faut alors prendre une boîte vide, l’insérer au bon endroit dans l’armoire, et y placer les nouveaux dossiers (ce qu’on appelle un split de page). Lorsque des dossiers sont retirés pour suppression, ils libèrent de la place dans une boîte. Ceci rend l’ensemble moins compact : un certains nombres de boîtes ne sont qu’en partie pleines, voire presque vides et le tout prend plus de place dans l’armoire. Si on devait chercher un dossier parmi l’ensemble, cela coûterait plus de travail, car il faudrait ouvrir plus de boîtes. C’est le problème que rencontre SQL Server lors d’un scan. On appelle ce phénomène la fragmentation interne (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, par un post-it, 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[^21]. 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, au moment où les pages. 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 application 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
JOIN sys.objects o ON i.object_id = o.object_id
WHERE fill_factor <> 0
ORDER BY table_name;

Il est évident que le FILLFACTOR n’est appliqué qu’à la création et reconstruction de l’index. Il n’est pas automatiquement maintenu. Comme si vous réorganisez votre armoire en décidant de ne remplir vos boîtes à fiche qu’à moitié pour laisser de la place pour les futurs dossiers, vous n’allez pas toujours conserver ce remplissage à 50 % à l’ajout de chaque dossier : cela équivaudrait à ne rien changer au problème, en prenant plus de place pour le tout. Si vous voulez maintenir une valeur de FILLFACTOR, il faut donc appliquer un plan de maintenance de vos index, pour les réorganiser ou les reconstruire régulièrement.

Pour cela, vous avez deux commandes :

ALTER INDEX … REBUILD reconstruit totalement l’index. Vous pouvez, en édition Entreprise, indiquer une reconstruction ONLINE. Les options de l’index spécifiés à la création (dont le FILLFACTOR) sont conservées. En cas de reconstruction d’un index clustered, les index nonclustered ne sont pas reconstruits. Pour tout reconstruire en une fois, utilisez cette syntaxe :

ALTER INDEX ALL ON Person.Contact REBUILD;

Vous pouvez changer au passage les options, comme le FILLFACTOR :

ALTER INDEX nix$Person_Contact$LastName
ON Person.Contact
REBUILD WITH (FILLFACTOR = 50);

En mode de récupération simple (ou en mode journalisé en bloc), cette opération est loguée de façon minimale, et est donc plus légère.

ALTER INDEX … REORGANIZE réorganise seulement le nœud feuille de l’index. C’est une commande plus rapide, qui est toujours exécutée ONLINE, même en édition Standard. Les pages sont compactées selon la valeur du FILLFACTOR indiquée à la création de l’index. C’est une commande idéale pour traiter rapidement un index peu fragmenté.

Mais, bien sûr, la question est : comment savoir si un index est fragmenté ? La fonction de gestion dynamique sys.dm_db_index_physical_stats est là pour ça. Démonstration :

CREATE INDEX nix$Person_Contact$FirstName
ON Person.Contact (FirstName)

SELECT index_id, *
FROM sys.indexes
WHERE name = 'nix$Person_Contact$FirstName'
AND object_id = OBJECT_ID('Person.Contact')

SELECT * 
FROM sys.dm_db_index_physical_stats
  (DB_ID(N'AdventureWorks'),
  OBJECT_ID(N'Person.Contact'), 22, NULL , 'DETAILED')
ORDER BY index_level DESC;

ALTER INDEX nix$Person_Contact$FirstName

ON Person.Contact

REBUILD WITH (FILLFACTOR = 20)

-- ou :

CREATE INDEX nix$Person_Contact$FirstName
ON Person.Contact (FirstName)
WITH FILLFACTOR = 20,
DROP_EXISTING;

SELECT *
FROM sys.dm_db_index_physical_stats
  (DB_ID(N'AdventureWorks'),
  OBJECT_ID(N'Person.Contact'), 22, NULL , 'DETAILED')
ORDER BY index_level DESC;

Les colonnes intéressantes retournées par cette fonction sont :

Tableau 1.1 – Résultat de sys.dm_db_index_physical_stats

Colonne Signification
Colonne Signification

index_depth Profondeur de l’index (nombre de niveaux). 1 au minimum

index_level Niveau de l’index. En mode ‘DETAILED’, la fonction retourne une ligne par niveau.

avg_fragmentation_in_percent Fragmentation externe moyenne

fragment_count Nombre de groupes de pages consécutives au nœud feuille (ou à chaque niveau en vision détaillée). Un index totalement défragmenté devrait avoir seulement un fragment, puisque toutes les pages se suivent. Plus ce nombre est important, plus il y a de fragmentation externe.

avg_fragment_size_in_pages Indique combien de pages en moyenne comportent les fragments. Plus ce nombre est important, moins il y a de fragmentation externe, car plus il y a de pages consécutives.

page_count Nombre total de pages sur le niveau

avg_page_space_used_in_percent Pourcentage moyen de remplissage de la page. Donne la fragmentation interne.

record_count Nombre d’enregistrements sur le niveau, donc de clés présentes

min_record_size_in_bytes Taille du plus petit enregistrement

max_record_size_in_bytes Taille du plus grand enregistrement

avg_record_size_in_bytes Taille moyenne des enregistrements

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écificités à 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 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 supporté qu’en é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éral 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.

6.1.4 Optimiser la taille de l’index

Les index sont stockés dans des pages, tout comme les données. L’arbre B-Tree a une profondeur et une étendue. Le principe de l’index est de limiter l’étendue par la profondeur : un niveau d’index trop large devient inefficace, il faut alors placer un niveau supplémentaire pour en diminuer la surface. Corollairement, plus la clé de l’index est petite, plus le nombre de clé par page est important, moins l’index doit être profond, et plus son parcours est optimisé.

Prenons un exemple pratique. Voici le code, nous le détaillerons ensuite :

CREATE DATABASE testdb
GO

ALTER DATABASE testdb SET RECOVERY SIMPLE
GO

USE testdb

CREATE TABLE dbo.testIndex (
  codeLong char(900) NOT NULL PRIMARY KEY NONCLUSTERED,
  codeCourt smallint NOT NULL,
  texte char(7150) NOT NULL DEFAULT ('O')
)
GO

DECLARE @v int

SET @v = 1

WHILE (@v <= 8000) BEGIN
  INSERT INTO dbo.testIndex (codeLong, codeCourt)
  SELECT CAST(@v as char(10)), @v
  SET @v = @v + 1
END
GO

CREATE UNIQUE INDEX uq$testIndex$codeCourt ON dbo.testIndex
(codeCourt)

SELECT name, index_id, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.testIndex')

/* -- résultat

name index_id type_desc

--------------------------
----------- ------------

NULL 0 HEAP

PK__testIndex__0425A276 2 NONCLUSTERED

uq$testIndex$codeCourt 3 NONCLUSTERED

*/

SELECT index_depth, index_level, page_count, record_count
FROM sys.dm_db_index_physical_stats(
DB_ID(), OBJECT_ID('dbo.testIndex'), 2, NULL, 'DETAILED')

/* -- résultat

index_depth index_level page_count record_count

----------- -----------
--------------------
--------------------

6 0 1441 8000

6 1 321 1441

6 2 72 321

6 3 16 72

6 4 3 16

6 5 1 3

*/

SELECT index_depth, index_level, page_count, record_count
FROM sys.dm_db_index_physical_stats(
DB_ID(), OBJECT_ID('dbo.testIndex'), 3, NULL, 'DETAILED')

/* -- résultat

index_depth index_level page_count record_count

----------- -----------
--------------------
--------------------

2 0 14 8000

2 1 1 14

*/

Nous créons d’abord une base de données pour y jouer, que nous mettons en mode de récupération simple afin d’éviter une augmentation inutile de la taille du journal. Nous créons ensuite une table de test, en faisant en sorte que chaque ligne remplisse une page. Nous y ajoutons deux colonnes pour y appliquer des index : l’une fait 900 octets (la taille maximale d’une clé d’index), l’autre un simple smallint (2 octets). Nous indexons ces deux colonnes. L’une est clé primaire, l’autre clé candidate. Le fait que ces index soient uniques n’a pas d’importance dans cet exemple, la structure de l’index n’est en rien différente.

Nous créons une insertion en boucle, de façon à y insérer 8000 lignes. Nous utilisons sys.indexes pour retrouver l’identifiant de chaque index. L’index lourd porte l’id 2, le léger, l’id 3. Nous en observons ensuite la taille à l’aide de la fonction système sys.dm_db_index_physical_stats. Ce que nous constatons, c’est que l’index lourd est profond, et nécessairement étendu (peu de clés peuvent résider dans la même page) : 6 niveaux pour un total de 1854 pages. L’index léger, lui, comporte 2 niveaux pour un total de 15 pages. Observons la différence sur les requêtes :

SET STATISTICS IO ON

SELECT * FROM dbo.testIndex WHERE CodeLong = '49'

-- Table 'testIndex'.
Scan count 0, logical reads 7

SELECT * FROM dbo.testIndex WHERE CodeCourt = 49

-- Table 'testIndex'.
Scan count 0, logical reads 3

La recherche avec l’index léger doit lire 3 pages, alors que celle qui s’applique à l’index lourd, 7 pages.

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 ci-dessus, 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 7 pages. On se souvient que l’index sur CodeLong a 6 niveaux. Tout ici est donc logique : descente sur 6 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 ci-dessous 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,
  PartitionNumber bigint,
  PartitionID bigint,
  iam_chain_type varchar(20),
  PageType int,
  IndexLevel int,
  NextPageFID bigint,
  NextPagePID bigint,
  PrevPageFID bigint,
  PrevPagePID bigint
)
GO

INSERT INTO #ind
EXEC ('DBCC IND(''testdb'', ''dbo.testIndex'', 2)')

SELECT * FROM #ind WHERE IndexLevel = 5

-- comment résoudre cette requête avec un index

SET STATISTICS IO ON

SELECT * FROM dbo.testIndex WHERE code = '49'

/*
Table 'testIndex'.
Scan count 0, logical reads 7
*/

DBCC TRACEON (3604);
GO

DBCC PAGE (testdb, 1, 8238, 3)

/*

FileId PageId Row Level ChildFileId ChildPageId codeLong (key)

------ ----------- ------ ------
----------- ----------- ----------

1 8238 0 5 1 4179 NULL

1 8238 1 5 1 8239 253

1 8238 2 5 1 1122 45

*/

-- row, key 45, ChildPageId : 1122

DBCC PAGE (testdb, 1, 1122, 3)

/*

FileId PageId Row Level ChildFileId ChildPageId codeLong (key)

------ ----------- ------ ------
----------- ----------- ----------

1 1122 0 4 1 4180 45

1 1122 1 4 1 9582 487

1 1122 2 4 1 9121 55

[...]

*/

-- row, key 487, ChildPageId : 9582

DBCC PAGE (testdb, 1, 9582, 3)

/*

FileId PageId Row Level ChildFileId ChildPageId codeLong (key)

------ ----------- ------ ------
----------- ----------- ----------

1 9582 0 3 1 9122 487

1 9582 1 3 1 9207 495

1 9582 2 3 1 9368 504

[...]

*/

-- row, key 487, ChildPageId : 9122

DBCC PAGE (testdb, 1, 9122, 3)

/*

FileId PageId Row Level ChildFileId ChildPageId codeLong (key)

------ ----------- ------ ------
----------- ----------- ----------

1 9122 0 2 1 9039 487

1 9122 1 2 1 9076 489

1 9122 2 2 1 9123 491

1 9122 3 2 1 9160 493

*/

-- row, key 489, ChildPageId : 9076

DBCC PAGE (testdb, 1, 9076, 3)

/*

FileId PageId Row Level ChildFileId ChildPageId codeLong (key)

------ ----------- ------ ------
----------- ----------- ----------

1 9076 0 1 1 9073 489

1 9076 1 1 1 9075 4897

1 9076 2 1 1 3623 49

1 9076 3 1 1 9079 4906

*/

-- row, key 49, ChildPageId : 3623

DBCC PAGE (testdb, 1, 3623, 3)

/*

FileId PageId Row Level codeLong (key) HEAP RID

------ ----------- ------ ------
---------------------------------

1 3623 0 0 49 0x380C000001000000

1 3623 1 0 490 0x590E000001000000

1 3623 2 0 4900 0x8C23000001000000

[...]

*/

Nous créons d’abord une table temporaire pour stocker le résultat de DBCC IND. Nous cherchons ensuite dans cette table l’id de la page d’entrée de l’index : son nœud racine. Il s’agit de la page qui est au niveau le plus élevé. Nous regardons ensuite son contenu avec DBCC PAGE. Cela nous retourne une structure qui comporte notamment les colonnes ChildPageId et codeLong (Key), indiquant respectivement l’Id de la page fille (celle qui se trouve au nœud intermédiaire suivant, c’est-à-dire au niveau inférieur de l’index), et la clé qui représente la ligne. Chaque entrée d’index représente une plage de valeurs de clés située entre la clé indiquée dans la ligne et la clé de la ligne suivante de ce nœud. Par exemple ici, la ligne 2 du nœud 5 de l’index, pointe sur des valeurs de clé situées entre ‘253’ compris, et ‘45’ non compris (n’oublions pas que cette colonne est un char, pas un numérique, et que la clé est donc triée alphabétiquement). La ligne 3 pointe sur toutes les clés égales ou supérieures à ‘45’. C’est là que nous devons descendre. Nous suivons donc cette voie sur 6 niveaux, et nous voyons que chaque page référencée dans ChildPageId est une page située au niveau inférieur. Lorsque nous arrivons au nœud feuille, le niveau 0, le ChildPageId est remplacé par un HEAP RID, qui est un stockage binaire du numéro de fichier, de page, et de ligne dans la page, de la ligne qui correspond à cette valeur. Dans le cas d’un index sur une table clustered, nous aurions trouvé la clé de l’index clustered.

6.2 Vues de gestion dynamique pour maintenir les index

Nous l’avons vu, un index créé n’est pas forcément utilisé. Notamment, l’optimiseur peut choisir de parcourir la table au lieu d’utiliser l’index, si celui-ci est peu sélectif. Tout index a un coût de maintenance, c’est-à-dire qu’il doit répercuter en temps réel toutes les modifications apportées dans la table, sur les colonnes qui composent sa clé. Un index inutilisé est donc pénalisant pour les performance, ainsi bien sûr que pour l’espace de stockage : il consomme de l’espace disque sans aucune utilité. Il faut donc le supprimer. Mais comment savoir si un index est utilisé ou non ? Vous avez à disposition une vue dynamique qui vous offre toutes les informations nécessaires. cette vue s’appelle sys.dm_db_index_usage_stats. Elle affiche les informations suivantes :

Tableau 1.1 – Résultat de sys.dm_db_index_usage_stats

Colonne Signification

user_seeks nombre de recherches à travers l’index dûs à une requête utilisateur

user_scans nombre de parcours du noeud feuille de l’index dûs à une requête utilisateur

user_lookups nombre de recherche de clés (bookmark lookups) dûs à une requête utilisateur

user_updates nombre de mises à jour de l’index, dûs à une requête DML (INSERT, UPDATE, DELETE) utilisateur

last_user_seek dernière recherche à travers l’index dûe à une requête utilisateur

last_user_scan dernier parcours du nœud feuille de l’index dûe à une requête utilisateur

last_user_lookup dernière recherche de clés (bookmark lookups) dûe à une requête utilisateur

last_user_update dernière mise à jour de l’index, dûe à une requête DML (INSERT, UPDATE, DELETE) utilisateur

system_seeks nombre de recherches à travers l’index dûs à une requête système

system_scans nombre de parcours du noeud feuille de l’index dûs à une requête interne

system_lookups nombre de recherche de clés (bookmark lookups) dûs à une requête interne

system_updates nombre de mises à jour de l’index, dûs à une requête DML (INSERT, UPDATE, DELETE) interne

last_system_seek dernière recherche à travers l’index dûe à une requête système

last_system_scan dernier parcours du nœud feuille de l’index dûe à une requête système

last_system_lookup dernière recherche de clés (bookmark lookups) dûe à une requête système

last_system_update dernière mise à jour de l’index, dûe à une requête DML (INSERT, UPDATE, DELETE) système

Si un scan s’exécute sur une table, c’est soit qu’une recherche dans une clause WHERE ne peut être effectuée à travers un index (que l’index soit trop peu sélectif, soit qu’il n’y a pas de bon index pour la clause de filtre). On peut donc utiliser le nombre de scan pour détecter un manque d’index ou un index trop peu sélectif.

6.2.1 Obtenir les informations opérationnelles de l’index

La fonction dynamique sys.dm_db_index_operational_stats vous permet d’obtenir des informations sur la vie et les opérations de maintenance d’un index. Il s’agit d’une fonction, qu’il faut donc appeler en passant des paramètres : nom de la base, nom de la table, nom de l’index, nom de la partition. Des paramètres passés à NULL permettent de retourner des lignes pour chaque index de la base/table. Vous pouvez ainsi mesurer si l’index est plus ou moins coûteux à maintenir, s’il doit opérer de nombreux splits de pages pour accommoder de nouvelles lignes, quelles sont les temps d’attentes sur l’index.

Tableau 1.1 – Résultat de sys.dm_db_index_operational_stats

Colonne Signification
partition_number numéro de partition si la table est partitionnée.
Base 1

leaf_insert_count Nombre total d’insertions au niveau feuille de l’index

leaf_delete_count Nombre total de suppressions au niveau feuille de l’index

leaf_update_count Nombre total de mises à jour au niveau feuille de l’index

leaf_ghost_count Nombre total de lignes au niveau feuille marquées pour suppression, mais pas encore supprimées. Elles seront supprimées par un thread de nettoyage qui s’exécute à intervalles réguliers.

nonleaf_insert_count Nombre total d’insertions aux niveaux intermédiaires de l’index. Valeur 0 sur une ligne correspondant à une table heap

nonleaf_delete_count Nombre total de suppressions aux niveaux intermédiaires de l’index. Valeur 0 sur une ligne correspondant à une table heap

nonleaf_update_count Nombre total de mises à jour aux niveaux intermédiaires de l’index. Valeur 0 sur une ligne correspondant à une table heap

leaf_allocation_count Nombre total d’allocations de page sur le niveau feuille d’un index ou un heap. Sur un index, une allocation de page correspond à un page split

nonleaf_allocation_count Nombre total d’allocations de page causés par un split, sur les niveaux intermédiaires de l’index. Valeur 0 sur une ligne correspondant à une table heap

leaf_page_merge_count Nombre total de fusions de page sur le niveau feuille

nonleaf_page_merge_count Nombre total de fusions de page sur les niveaux intermédiaires. Valeur 0 sur une ligne correspondant à une table heap

range_scan_count Nombre total de scans (de table ou d’une plage plus petite)

singleton_lookup_count Nombre total de récupérations de lignes distinctes depuis l’index ou la table heap

forwarded_fetch_count Nombre de lignes récupérées à travers un enregistrement de renvoi (forwarding record). Valeurs existant seulement sur une table heap. Donc toujours 0 sur des index

lob_fetch_in_pages Nombre total de pages d’objets larges (large object (LOB)) récupérées, depuis des unités d’allocation LOB_DATA. Signifie la présence de colonnes TEXT, IMAGE, ou VARCHAR(MAX) ou VARBINARY(MAX), ou XML

lob_fetch_in_bytes Nombre total d’octets d’objets larges récupérés

lob_orphan_create_count Nombre total de valeurs d’objets larges orphelines crées pour des opérations en lot. Valeur toujours à 0 sur les index non ordonnés

lob_orphan_insert_count Nombre total de valeurs d’objets larges orphelines insérées durant les opérations en lot. Valeur toujours à 0 sur les index non ordonnés

row_overflow_fetch_in_pages Nombre total de pages de dépassement de page (row-overflow) retournées depuis une unité d’allocation ROW_OVERFLOW_DATA

row_overflow_fetch_in_bytes Nombre total d’octets de dépassement de page (row-overflow) retournés depuis une unité d’allocation ROW_OVERFLOW_DATA

column_value_push_off_row_count Nombre total de valeurs de colonne poussées dans une autre page, pour accommoder soit des valeurs LOB, soit des valeurs en dépassement de page (ROW OVERFLOW), durant une insertion ou une mise à jour

column_value_pull_in_row_count Nombre total de lignes réintégrées dans une page de données (unité d’allocation IN_ROW_DATA), depuis des pages de LOB et de dépassement (unité d’allocation LOB_DATA ou ROW_OVERFLOW_DATA), lorqu’une mise à jour de données a diminué la taille de la ligne

row_lock_count Nombre total de verrouillages de ganularité ligne demandés

row_lock_wait_count Nombre total d’attentes de libération d’un verrou de ligne

row_lock_wait_in_ms Temps total d’attente de libération de verrous de ligne, en millisecondes

page_lock_count Nombre total de verrouillages de ganularité page demandés

page_lock_wait_count Nombre total d’attentes de libération d’un verrou de page

page_lock_wait_in_ms Temps total d’attente de libération de verrous de page, en millisecondes

index_lock_promotion_attempt_count Nombre total de tentative d’escalades de verrous

index_lock_promotion_count Nombre total d’escalades de verrous réellement effectués

page_latch_wait_count Nombre total d’attentes de libération d’un latch (latch contention)

page_latch_wait_in_ms Temps total d’attente de libération de latch, en millisecondes

page_io_latch_wait_count Nombre total d’attentes de libération d’un latch d’entrée/sortie de page

page_io_latch_wait_in_ms Temps total d’attente de libération de latch d’entrée/sortie de page, en millisecondes

Pour comprendre ce tableau, nous devons expliquer quelques principes supplémentaires :

Lorsqu’une ligne dans une table heap est mise à jour, que la taille de la ligne grandit et qu’il ne reste plus de place dans la page, la ligne est déplacée dans une autre page. Pour éviter une mise à jour de tous les index non ordonnés pour accommoder le nouveau RID de la ligne, SQL Server pose, à la place de l’ancienne ligne, une référence vers la nouvelle position de ligne, ce qu’on appelle un enregistrement de renvoi (forwarding record).

Lors d’une opération de traitement par lot (bulk operation), SQL Server crée des LOB pour stocker temporairement les données. Ces LOB sont dit orphelins (orphan LOB).

Vous pouvez donc utiliser cette fonction système pour collecter des statistiques physiques, d’utilisation, de charge ou de contention sur vos tables et vos index. Exemple :

SELECT object_name(s.object_id) as tbl,
  i.name as idx,
  range_scan_count + singleton_lookup_count as [pages lues],
  leaf_insert_count+leaf_update_count+ leaf_delete_count
  as [écritures sur noeud feuille],
  leaf_allocation_count as [page splits sur noeud feuille],
  nonleaf_insert_count + nonleaf_update_count +
  nonleaf_delete_count as [écritures sur noeuds intermédiaires],
  nonleaf_allocation_count
  as [page splits sur noeuds intermédiaires]
FROM sys.dm_db_index_operational_stats (DB_ID(),NULL,NULL,NULL) s
JOIN sys.indexes i
  ON i.object_id = s.object_id and i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
ORDER BY [pages lues] DESC

6.2.2 Index manquants

Lors de la génération du plan d’exécution de chaque requête (phase d’optimisation), le moteur relationnel teste différents plans d’exécution et sélectionne le moins coûteux. Dans certains cas, ce moteur d’optimisation a la capacité de constater que la requête aurait été bien mieux servie si un index avait été présent. Cette information est retournée lorsqu’on affiche un plan d’exécution détaillé en XML.

Les informations d’index manquants sont également stockés dans un espace de cache, et peuvent être requêtés à travers trois vues système, qui offrent toutes les informations nécessaire pour la création de l’index : colonnes qui devraient composer le clé, colonnes à inclure dans le noeud feuille, avec un compteur qui indique le nombre de fois où l’index aurait été utile.

Cette information n’est pas exhaustive, dans le sens ou l’optimiseur ne va pas détecter tous les index manquants. Il ne le fait que sur certaines requêtes, lorsqu’il a la capacité de comprendre qu’un index aurait été utile. Cela ne vous décharge pas du travail d’optimiser les autres requêtes par la création d’index.

Mais, concerant les vues dynamiques d’index manquants, voici une requête qui vous donne toutes les informations :

SELECT object_name(object_id) as objet, d.*, s.*
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g
  ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s
  ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC, object_id

Comme elle vous dit tout, vous pouvez même faire générer par une requête le code DDL nécessaire à la création des index, ainsi, vous n’avez plus qu’à copier ce code et à l’exécuter.

Toutefois, ne créez pas forcément tous les index conseillés, mais concentrez-vous sur ceux dont le nombre de « user seeks » est le plus importants : cette mesure vous indique combien de fois l’index aurait pu être utile.

Voici un exemple de code pour générer vos index :

SELECT
  'CREATE INDEX nix$' + lower(object_name(object_id)) + '$'
  + REPLACE(REPLACE(REPLACE(COALESCE(equality_columns,
  inequality_columns), ']', ''), '[', ''), ', ', '_')
  + ' ON ' + statement + ' (' + COALESCE(equality_columns,
  inequality_columns) + ') INCLUDE (' + included_columns + ')',
  object_name(object_id) as objet, d.*, s.*
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g
  ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s
  ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC, objet

Nous verrons dans le chapitre 8 comment lire un plan d’exécution, ce qui vous aidera à déterminer quels indexe construire pour vos requêtes.

6.3 Les vues indexées

Une vue est une requête SELECT stockée sur le serveur. On la référence comme une table dans une requête. Elle ne matérialise pas le résultat du SELECT. Donc, chaque appel à la vue, génère des appels aux tables sous-jacentes. Vous pouvez indexer vos vues pour matérialiser les données. Lorsqu’un vue est indexée, elle maintient le résultat de son SELECT dans un index clustered, qui doit lui aussi être maintenu à chaque modification le lignes dans les tables sous-jacentes. Cette option est très intéressante pour les larges volumes, sur des requêtes complexes qui mettent en jeu de nombreuses jointures de tables. Elle permettent une sorte de dénormalisation contrôlé par le système. Dans les cas de volumes plus petits, en général des index bien placés sur les tables sous-jacentes suffisent.

À partir de SQL Server 2005, les vues indexées sont supportées en édition Standard. Une fonctionnalité supplémentaire est présente dans l’édition Entreprise : l’optimiseur de requête peut choisir d’utiliser un index sur une vue, même si la requête ne mentionne pas explicitement la vue, mais simplement une table sous-jacente, et que l’optimiseur trouve un intérêt supérieur à utiliser cet index.

Votre index doit être créé avec certaines options de session qui garantissent la consistance des données de la vue :

  • ANSI_NULLS ON (aussi à la création de la table) ;

  • QUOTED IDENTIFIER ON, et quelques autres options (voir les BOL) ;

  • La vue doit avoir été créée avec l’option WITH SCHEMABINDING (ce

    qui la lie à la structure des tables sous-jacentes, de sorte que les modifications de structure de celles-ci sont protégées) ;

  • Les indicateurs de table sont interdits, on ne peut donc forcer un

    index, ou un niveau d’isolation, dans la requête ;

  • Les fonctions référencées dans la vue doivent être déterministes

    (elle doivent toujours retourner la même valeur, à données égales) ;

Ces options forcent simplement le retour de la vue à être consistant, donc indexable. Les options telles que SET ANSI_NULLS doivent être placées pour toutes les sessions qui requêtent la vue, ou modifient les données sous-jacentes, afin que SQL Server puisse garantir que le résultat du SELECT de la vue soit toujours le même. Cette condition peut être contraignante dans un environnement où vous ne maîtrisez pas pas tous les points d’entrées, et notamment avec des applications qui utilisent des bibliothèques d’accès aux données anciennes, comme ODBC.

PHP : si vous utilisez PHP sous Windows, Microsoft a développé un pilote spécifique, téléchargeable à partir de ce lien : http://www.microsoft.com/sql/technologies/php/.

La première étape est de créer un index clustered unique sur la vue, qui va la matérialiser. L’unicité est nécessaire, vous devez donc trouver un moyen de retourner un colonne ou un jeu de colonnes unique dans votre vue.

Voici un exemple de création de vue, et d’utilisation par l’optimiseur, même en mentionnant seulement la table sous-jacente (nous sommes en édition Developer, qui correspond aux fonctionnalités de l’édition Entreprise :

CREATE VIEW Person.SomeContacts
WITH SCHEMABINDING
AS
  SELECT ContactID, FirstName, LastName
  FROM Person.Contact
  WHERE LastName LIKE '%Ad%'
GO

CREATE UNIQUE CLUSTERED INDEX cix$Person_SomeContacts
ON Person.SomeContacts (ContactID)
CREATE NONCLUSTERED INDEX nix$Person_SomeContacts$LastName_FirstName
ON Person.SomeContacts (LastName, FirstName)
GO

SELECT FirstName, LastName
FROM Person.Contact
WHERE LastName LIKE '%Ad%'

Le plan d’exécution généré est simple, un scan… mais de quel index ? Voici le plan :

|--Index Scan(OBJECT:([AdventureWorks].[Person].[SomeContacts].

[nix$Person_SomeContacts$LastName_FirstName]))