Pourquoi indexer ?
Les index servent à faciliter les lectures en diminuant le nombre de pages de données à lire sur les fichiers disque (une page de SQLServer2005 pèse 8 koctets)
La logique est la même que pour l'index d'un livre :
Si l'on sait ce que l'on doit chercher dans le livre , parcourir l'index du livre sera beaucoup plus rapide que lire l'ensemble du livre (On parle de scan dans le cas de SQLServer)
Mais, comme pour les livres il va de soi qu'un index pertinent doit être plus petit que les données (le livre) !
De même que pour les livres, les index sont triés (en croissant ou en décroissant) ce qui facilitera bien sûr les recherches qui comprennent tout ou partie de la clé cherchée.
Que faut-il indexer ?
Les index se bâtissent sur une ou plusieurs colonnes de tables, voir même de vues qui deviennent alors matérialisées (Ce sont les vues indexées)
Ils peuvent aussi être bâtis sur des fonctions appliquées à des colonnes Ex sur UpperCase(Nom) dans la table du personnel.
Un des premiers critères est donc le choix de la ou des colonnes :
• Pour respecter la règle de taille raisonnable ,on privilégiera donc les colonnes de type int, smallint, datetime dont la taille est fixe et connue d'avance par rapport à celles de type varchar(xx) qui , par définition, peuvent atteindre une grande (voir très grande taille dans le cas du VARCHAR(MAX))
• On privilégiera aussi des colonnes stables : en effet, ces index seront maintenus en temps réel, et le coût de gestion d'un index sur DATE_HEURE_DERNIER_POINTAGE d'un PERSONNEL par exemple, risquerait d'être prohibitif : à chaque fois que le PERSONNEL badgera l'index sera modifié … Il faut toujours avoir une vision globale des aspects performance et se méfier des mesures qui améliorent ponctuellement une partie de l'application tout en dégradant globalement beaucoup l'ensemble…
• De la même façon qu'un dictionnaire ne peut être à la fois trié sur NOM + PRENOM et PRENOM + NOM (Chercher dans un dictionnaire tous les musiciens prénommés Frantz ?), l'index multi-colonnes devra être judicieusement choisi : ai-je une chance de chercher des personnes plutôt par leur prénom (pays Anglo-saxons) ou plutôt par leur nom (France) ? On devra donc choisir avec précaution l'ordre des colonnes
• L'index doit être de taille raisonnable (très raisonnable) par rapport à la taille de la table : on voit donc que les stratégies d'indexation de très petites tables (qui tiennent dans une ou quelques pages de 8 koctets) sont inutiles
Vérifions ce précepte de taille :
La table DveLocation a 14 lignes et tient entièrement dans deux pages
Ce que montre le rapport d'utilisation (Accessible dans le résumé de la base de données)
Examinons maintenant ce que fait l'optimiseur sur cette requête :
select
*
from DveLocation
where LocationID = 10
L'option set statistics io on nous donne le nombre de pages accédées :
Table 'DveLocation'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Ici 2 pages (logical reads)
Voyons le plan d'exécution de la requête (Requête ->Inclure le plan d'exécution réel)
L'optimiseur a choisi de faire un balayage séquentiel de la table (scan) : c'est de toute façon le seul moyen dont il dispose à ce moment.
Essayons de lui 'forcer' la main en créant un index sur la colonne de recherche :
create
index DveLocationID on DveLocation(LocationID)
Et voici les résultats chiffrés de cette stratégie d'indexation :
Table 'DveLocation'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Toujours 2 pages lues donc.
Et le plan d'optimisation :
Strictement identique !!
Que s'est-il passé ? l'optimiseur de SQLServer2005 est dit basé coûts ; c'est-à-dire qu'il cherche à minimiser le côut d'éxécution d'une requête ; il s'est donc rendu compte, intelligent comme il est, que lire les pages de l'index en plus des pages de données serait donc plus coûteux pour lui , solution qu'il a écartée…
On peut voir ce que l'on gagné en utilisant un indicateur (hint) de requête pour forcer l'optimiseur à passer par cet index :
select
*
from DveLocation with
(INDEX(DveLocationID))
where LocationID = 10
Table 'DveLocation'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Bilan : on est passé à 3 pages lues (50% de plus !) mais le plan est bien celui que je voulais obtenir :
Je reviendrai sur les indicateurs plus tard, mais ceci montre un grand principe de l'optimisation des requêtes :
SQLServer2005 dispose de plus d'informations que vous pour prendre ses décisions, ce qui fait que , en général, elles sont plus pertinentes que les vôtres (ne l'ébruitez pas ; on croit que je suis un DBA intelligent…)
Bien sûr, tout l'exemple qui précède est basé sur la très petite taille de la table DveLocation, un contre exemple avec une table plus importante nous rassure sur la pertinence des index sur les tables avec du volume.
Prenons une table DveProduct qui contient 504 lignes et occupe 120 Koctets (soit 15 pages de 8 koctets)
select ProductID,Name
from DveProduct
where ProductID = 320
Table 'DveProduct'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Et avec un index sur ProductID :
create
index DveProductID on DveProduct(ProductID)
go
select ProductID,Name
from DveProduct
where ProductID = 320
Table 'DveProduct'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Eh oui ! les entrées/sorties disque ont été divisées par 5 (de 15 à 3)
Le plan d'exécution, quand à lui, reflète bien cette stratégie d'accès :
La diminution du nombre de lectures est bien sûr proportionnelle à la taille de la table : plus le volume est important et plus le rapport Nbe de lignes cherchées / nombre de lignes de la table est petit, et plus le gain est important.
Pour information, je classe les tables en 4 catégories :
1. Petites tables : moins de 10000 lignes
2. Tables moyennes : de 10000 à 100000 lignes
3. Tables importantes : de 100000 à 1000000 lignes
4. Tables très importantes : plus de 1000000 de lignes
Est-il besoin de préciser que les tables importantes méritent toute votre attention ?
Par ailleurs, cette abaque assez simpliste ne tient pas compte de la taille de la ligne il est clair que 1000 lignes de 50 octets ne pèsent pas la même chose que 1000 lignes de 7000 octets….mais ceci est un autre débat (sur la taille des lignes et la conception de base) que j'aborde dans une autre fiche.
Quels sont les types d'index de SQLServer2005 ?
En première approche, il y a deux types d'index :
• Les index clustered dont les feuilles sont en fait les lignes de la table : on a donc une fusion très intime de la table et de l'index
• Les index non clustered plus indépendants par rapport aux tables
Un index clustered implique que la table est rangée physiquement dans l'ordre de l'index : de ce fait , il ne peut y avoir au maximum qu'un index clustered par table.
Le choix de cet index clustered est très important pour plusieurs raisons :
1. Il n'y en a qu'un et, s'il y a plusieurs candidats possibles, on devra choisir le meilleur
2. La table étant rangée dans cet ordre, on évitera une clé clustered sur des colonnes instables (Mauvais exemple : clustered sur le solde d'un compte bancaire)
3. Cet index sera traversé par les autres index non clustered : il s'agit en quelque sorte d'un adressage indirect : le non cluster donne d'abord une première recherche qui est ensuite appliquée au clustered
4. Du fait de l'empilement des index non clustered sur clustered, la taille des clés clustered devra être le plus raisonnable possible
5. Pour la même raison, on cherchera la plus grande discrimination sur l'index clustered : c'est-à-dire qu'il devra se rapprocher de la clé unique à défaut d'y arriver…
On a donc plusieurs cas de figure pour les tables :
1. Tables sans aucun index (dites en tas) : ceci s'applique bien aux très petites tables
2. Tables avec index non cluster (un ou plusieurs) sans index clustered
3. Tables avec un index clustered uniquement
4. Tables avec un index clustered et un ou plusieurs index non clustered
Que contiennent les index ?
Je vais 'ouvrir un peu le capot' pour voir ce qui se passe à l'intérieur de la base de données ,c'est-à-dire dans les pages du fichier de la base.
Pour cela, j'ai besoin de travailler sur les pages, et, en faisant des requêtes sur les vues/tables systèmes, on constate que les adresses des pages sont dans un format hexadécimal.
Ma première action sera donc de convertir ces numéros de pages en couples (Numéro de fichier, Numéro de page) car une base peut se trouver sur plusieurs fichiers.
Voici le code la fonction que je vais utiliser :
if
object_id('ConvertirNoPage')
is
not
null
drop
function ConvertirNoPage
go
create
function ConvertirNoPage (@CodeHexa binary(6))
returns
varchar(15)
as
begin
return
convert(varchar(2),
(convert(int,
substring(@CodeHexa, 6, 1))
*
power(2, 8))
+
(convert(int,
substring(@CodeHexa, 5, 1))))
+
':'
+
convert(varchar(11),
(convert(int,
substring(@CodeHexa, 4, 1))
*
power(2, 24))
+
(convert(int,
substring(@CodeHexa, 3, 1))
*
power(2, 16))
+
(convert(int,
substring(@CodeHexa, 2, 1))
*
power(2, 8))
+
(convert(int,
substring(@CodeHexa, 1, 1))))
end
go
et voici un exemple d'utilisation sur une table système :
-- Obtenir le lieu de stockage physique d'un index
if
object_ID('DonnerStockageIndex')
is
not
null
drop
procedure DonnerStockageIndex
go
create
proc DonnerStockageIndex (@NomIndex sysname)
as
begin
select
convert(char(30),
name)
'NomObjet',
id 'Idinterne',
indid 'NoIndex',
dbo.ConvertirNoPage(first)
'First',
first,
dbo.ConvertirNoPage(root)
'root',
root,
dbo.ConvertirNoPage(firstIAM)
'firstIAM',
firstIAM
from sysindexes
where
name
like @NomIndex
end
go
Vérifions cela sur un exemple :
exec DonnerStockageIndex DvetableTestNom
go
qui génère :
NomObjet IdInterne NoIndex First first root root firstIAM firstIAM
------------------------------ ----------- ------- --------------- -------------- --------------- -------------- --------------- --------------
DveTableTestNom 1364199910 1 1:22068 0x345600000100 1:22068 0x345600000100 1:22069 0x355600000100
La partie intéressante ici est que cet index a sa racine (root) dans le fichier 1 à la page 22068
Il nous reste à visualiser le contenu de cette page : un utilitaire DBCC PAGE (non documenté) y pourvoira.
DBCC TRACEON(3604)
dbcc page ('AdventureWorks',1,22068,2)
Les paramètres sont (Base de données,Numéro fichier, Numéro page,Mode)
Le mode pouvant être 1,2 ou 3.
Voici un extrait du mode 2 :
Memory Dump @0x24D4C000
24D4C000: 01010400 00a00001 00000000 00000700 †................
24D4C010: 00000000 00000700 b4010000 ea1e0801 †................
24D4C020: 34560000 01000000 5e000000 96190000 †4V......^.......
24D4C030: 02000000 00000000 00000000 00000000 †................
24D4C040: 01000000 00000000 00000000 00000000 †................
24D4C050: 00000000 00000000 00000000 00000000 †................
24D4C060: 50000700 41414102 00fc0000 00000000 †P...AAA.........
24D4C070: 00004d07 00000000 50000700 41414202 †..M.....P...AAB.
24D4C080: 00fc0000 00000000 00005807 00000000 †..........X.....
Et un autre du mode 3 :
Memory Dump @0x24FFC060
00000000: 50000700 41414102 00fc0000 00000000 †P...AAA.........
00000010: 00004d07 00000000 †††††††††††††††††††..M.....
Version Information =
Transaction Timestamp: 1869
Version Pointer: Null
UNIQUIFIER = [NULL]
Slot 0 Column 1 Offset 0x4 Length 3
NOM = AAA
Ces préalables d'outillage étant faits , passons à la pratique :
Création d'un index clustered (seul pour le moment)
if
OBJECT_ID('DvetableTest')
is
not
null
drop
table DvetableTest
go
create
table DvetableTest (NOM char(3))
create
clustered
index DvetableTestNom on DvetableTest(NOM)
go
exec DonnerStockageIndex DvetableTestNom
go
NomObjet IdInterne NoIndex First first root root firstIAM firstIAM
------------------------------ ----------- ------- --------------- -------------- --------------- -------------- --------------- --------------
DveTableTestNom 1380199967 1 0:0 0x000000000000 0:0 0x000000000000 0:0 0x000000000000
Explication : la table est vide et l'espace n'a pas encore été alloué pour les données de l'index.
Par contre, l'objet index existe déjà et a donc un identifiant (1380199967 est l'identifiant de la table, 1 le numéro de l'index dans cette table)
Insérons une ligne dans cette table :
insert
into DvetableTest (NOM)
values
('AAA')
go
exec DonnerStockageIndex DvetableTestNom
go
NomObjet IdInterne NoIndex First first root root firstIAM firstIAM
------------------------------ ----------- ------- --------------- -------------- --------------- -------------- --------------- --------------
DveTableTestNom 1380199967 1 1:22068 0x345600000100 1:22068 0x345600000100 1:22069 0x355600000100
Cette fois, la page racine est en 1 :22068 (par parenthèse on voit là une optimisation de la gestion de l'espace disque ; en effet cette page était celle que je venais de libérer par un drop table)
Son contenu est :
Memory Dump @0x23A1C060
00000000: 50000700 41414102 00fc0000 00000000 †P...AAA.........
00000010: 0000e009 00000000 †††††††††††††††††††........
Version Information =
Transaction Timestamp: 2528
Version Pointer: Null
UNIQUIFIER = [NULL]
Slot 0 Column 1 Offset 0x4 Length 3
NOM = AAA
On voit apparaître la notion de slot : il s'agit d'un point d'entrée dans la page.
On retrouve notre valeur de clé AAA, ainsi que diverses valeurs binaires : nous verrons par la suite qu'il s'agit essentiellement de pointeurs.
Insérons d'autres valeurs dans notre table , et donc d'autres clés :
insert
into DvetableTest (NOM)
values
('AAB')
go
insert
into DvetableTest (NOM)
values
('AAC')
go
insert
into DvetableTest (NOM)
values
('AAD')
go
insert
into DvetableTest (NOM)
values
('AAE')
go
insert
into DvetableTest (NOM)
values
('AAF')
go
insert
into DvetableTest (NOM)
values
('AAG')
go
Memory Dump @0x23F4C000
23F4C000: 01010400 00a00001 00000000 00000700 †................
23F4C010: 00000000 00000700 b6010000 ea1e0801 †................
23F4C020: 34560000 01000000 5e000000 bd190000 †4V......^.......
23F4C030: 02000000 00000000 00000000 00000000 †................
23F4C040: 01000000 00000000 00000000 00000000 †................
23F4C050: 00000000 00000000 00000000 00000000 †................
23F4C060: 50000700 41414102 00fc0000 00000000 †P...AAA.........
23F4C070: 0000e009 00000000 50000700 41414202 †........P...AAB.
23F4C080: 00fc0000 00000000 0000f909 00000000 †................
23F4C090: 50000700 41414302 00fc0000 00000000 †P...AAC.........
23F4C0A0: 0000fa09 00000000 50000700 41414402 †........P...AAD.
23F4C0B0: 00fc0000 00000000 0000fb09 00000000 †................
23F4C0C0: 50000700 41414502 00fc0000 00000000 †P...AAE.........
23F4C0D0: 0000fc09 00000000 50000700 41414602 †........P...AAF.
23F4C0E0: 00fc0000 00000000 0000fd09 00000000 †................
23F4C0F0: 50000700 41414702 00fc0000 00000000 †P...AAG.........
On retrouve bien nos valeurs de clés, triées dans l'ordre…
L'examen de la fin de sortie de DBCC nous donne les slots :
Row - Offset
6 (0x6) - 240 (0xf0)
5 (0x5) - 216 (0xd8)
4 (0x4) - 192 (0xc0)
3 (0x3) - 168 (0xa8)
2 (0x2) - 144 (0x90)
1 (0x1) - 120 (0x78)
0 (0x0) - 96 (0x60)
On a bien nos 7 valeurs de clés dans la page, et on constate que chaque clé occupe 24 octets ; de quoi s'agit-il ? De la valeur de la clé (char(3)) suivie de pointeurs techniques ; en effet les clés d'index doivent pointer sur :
• Les pages de données (et les lignes) s'il s'agit du dernier niveau de l'index
• D'autres pages (dites intermédiaires) si l'index a une profondeur de plus de 1
En effet on peut représenter ces index sous forme d'arbre (en informatique ;la racine est toujours en l'air…)
Une des premières conclusions sur ces structures est immédiate :
Plus les clés d'index sont petites, plus il y a de clés par page d'index.
Ceci entrainera donc des lectures sur disques moins nombreuses, et , de plus , une meilleure réutilisation des pages en cache.
Prenons l'exemple d'un index sur les noms de produits (table vue précédemment)
create
clustered
index DveProductName on DveProduct(Name)
go
exec DonnerStockageIndex DveProductName
go
select
name
from dveproduct
DBCC TRACEON(3604)
dbcc page ('AdventureWorks',1,22085,2)
Voici le contenu de la page racine :
Row - Offset
12 (0xc) - 700 (0x2bc)
11 (0xb) - 649 (0x289)
10 (0xa) - 606 (0x25e)
9 (0x9) - 555 (0x22b)
8 (0x8) - 498 (0x1f2)
7 (0x7) - 441 (0x1b9)
6 (0x6) - 378 (0x17a)
5 (0x5) - 309 (0x135)
4 (0x4) - 238 (0xee)
3 (0x3) - 203 (0xcb)
2 (0x2) - 172 (0xac)
1 (0x1) - 137 (0x89)
0 (0x0) - 96 (0x60)
Seulement 13 slots ? Que s'est-il passé ? SQLServer a jugé que l'ensemble de l'index ne tiendrait pas dans une seule page et a donc augmenté la profondeur de l'index.
Chose que l'on vérifie facilement avec :
select
INDEXPROPERTY(OBJECT_ID('dbo.DVEProduct'),
'DveProductName','IndexDepth')
AS ProfondeurIndex
ProfondeurIndex
---------------
2
Plus l'index a une profondeur importante, plus d'entrées/sorties seront nécessaires pour trouver les données associées aux clés.
Ceci implique les conclusions suivantes :
La taille de la clé doit être raisonnable (encore !)
On veillera à ce que la profondeur de l'index ne varie pas lors des mises à jours nombreuses qui vont survenir dans la vie de la table.
Il pourra être plus efficace de créer un index une seule fois après un import massif de données que de mettre à jour cet index au fil de l'eau, c'est-à-dire lors de chaque INSERT.
Une des manières de diminuer la profondeur de l'index est de remplir le plus possible les pages (La Palisse n'aurait pas dit mieux)
Un paramètre permet cela : le FILL FACTOR (facteur de remplissage)
La valeur par défaut agit ainsi :
Les feuilles de l'arbre sont remplies au maximum, mais les niveaux supérieurs (vers la racine) permettent d'insérer au moins une valeur de clé supplémentaire.
Attention : cette règle de remplissage ne s'applique qu'au moment de la création de l'index, ensuite les pages d'index vivent leur vie.
create
clustered
index DveProductName on DveProduct(Name)
Crée un index avec la valeur par défaut de fillfactor (feuilles pleines)
create
clustered
index DveProductName on DveProduct(Name)
with
(fillfactor
= 50)
Crée un index avec des feuilles remplies à 50 pourcent.
Vous me direz pourquoi faire puisque c'est plus efficace pour les lectures d'avoir des feuilles bien remplies ?
Parce que l'on prévoit beaucoup d'insertions de nouvelles clés, ces clés venant s'insérer dans n'importe quel ordre.
On a toujours des compromis à faire entre lectures et mises à jour : l'optimisation de l'un se fait au détriment de l'autre.
C'est le moment d'utiliser une des fonctions de SQLServer2005 : les fonctions de gestion dynamique des objets qui sont des fonctions systèmes vous permettant de tout savoir (ou presque …) sur les objets gérés par la base de données.
La première fonction de ce type que nous utiliserons s'appelle
sys.dm_db_index_physical_stats
Elle permet de connaître l'implémentation physique d'un ou plusieurs index sur une table.
Voici sa définition :
sys.dm_db_index_physical_stats (
{ IdBaseDeDeonnées | null }
, { IdObjet | null }
, { IdIndex | null | 0 }
, { NuméroPartition | null }
, { Mode | null | DEFAULT }
)
Cette fonction est en fait appelée à remplacer le 'bon vieux' DBCC SHOW_CONTIG des versions précédentes.
Voyons tout cela :
create
clustered
index DveProductName on DveProduct(Name)
with
(fillfactor
= 50)
go
select
*
from sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),
OBJECT_ID('DveProduct'),
null,
null
,
'LIMITED');
database_id object_id index_id partition_number index_type_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
----------- ----------- ----------- ---------------- ------------------------------------------------------------ ------------------------------------------------------------ ----------- ----------- ---------------------------- -------------------- -------------------------- -------------------- ------------------------------ -------------------- -------------------- -------------------------- ------------------------ ------------------------ ------------------------ ----------------------
9 1396200024 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 29.1666666666667 8 3 24 NULL NULL NULL NULL NULL NULL NULL NULL
Parmi toutes ces informations la profondeur (index depht vue plus haut) et le nombre de pages de l'index (page count)
Ici on a une profondeur de 2 et 24 pages.
Diminuons encore le fill factor :
create
clustered
index DveProductName on DveProduct(Name)
with
(fillfactor
= 10,
drop_EXISTING
=
ON
)
go
Avec utilisation d'une nouvelle option DROP_EXISTING (hélas j'aurai aimé que cette option agisse toujours , qu'il y ait un index précédent ou pas ; Microsoft si tu m'entends….)
Cette fois on a une profondeur de 2 et 111 pages…. On a bien favorisé les mises à jour ultérieures mais aussi pénalisé les lectures.
On avait seulement 13 pages avec l'option par défaut de FILLFACTOR.
A noter une option complémentaire du FILLFACTOR :
Le PAD_INDEX permet d'appliquer aux niveaux intermédiaires et racine le même facteur de remplissage que pour les feuilles.
create
clustered
index DveProductName on DveProduct(Name)
with
(FILLFACTOR=100,PAD_INDEX=ON
,drop_EXISTING=ON)
go
Remplit tous les niveaux le plus possible..
Conclusion sur le remplissage :
Plus les pages sont pleines, plus il y a de clés par page, plus on favorise les lectures, plus on pénalise les mises à jour.
Plus la profondeur est grande, plus on ralentit les lectures.
Le remplissage va éminemment varier en fonction des mises à jour ultérieures.
Le cas particulier des index clustered :
Ces index sont particuliers dans le sens ou les feuilles de l'index sont directement les données de la table.
Ceci aura plusieurs conséquences très importantes :
• Les données de la table seront organisées dans l'ordre de cet index
• Cet index est la base de nombreux outils de réorganisation/défragmentation (car il y a assimilation entre organiser la table et organiser l'index) et vouloir se passer de ce type d'index sur SQLServer est du domaine de l'utopie
• Du fait du rangement on aura des avantages : les lectures ordonnées seront plus rapides ; mais aussi des inconvénients : une certaine fragmentation interviendra si des insertions aléatoires ou des deletes ont lieu
• Cet index est le point d'entrée de la table, et, dans le cas où l'on crée des index non clustered en plus, il y aura empilement de ces deux index pour les recherches
Voyons tout cela :
create
table DvetableTest (CLE char(900),NOM char(3000))
create
clustered
index DvetableTestCle on DvetableTest(CLE)
go
insert
into DvetableTest (CLE,NOM)
values
(replicate('A',900),replicate('A',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('B',900),replicate('B',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('E',900),replicate('E',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('F',900),replicate('F',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('G',900),replicate('G',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('H',900),replicate('H',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('I',900),replicate('I',3000))
go
J'ai volontairement utilisé la taille maximum de clé (900 octets) pour mieux voir les remplissages de pages.
La 1ère page des données (donc des feuilles car nous sommes sur un clustered) donne :
PAGE HEADER:
m_pageId = (1:21974) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0xa000
m_objId (AllocUnitId.idObj) = 459 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594068008960
Metadata: PartitionId = 72057594059423744 Metadata: IndexId = 1
Metadata: ObjectId = 1492200366 m_prevPage = (0:0) m_nextPage = (1:22081)
pminlen = 3904 m_slotCnt = 2 m_freeCnt = 250
m_freeData = 7938 m_reservedCnt = 0 m_lsn = (95:266:16)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:16176) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 3921
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFO
Memory Dump @0x23A1C060
00000000: 5000400f 41414141 41414141 41414141 †P.@.AAAAAAAAAAAA
00000010: 41414141 41414141 41414141 41414141 †AAAAAAAAAAAAAAAA
00000020: 41414141 41414141 41414141 41414141 †AAAAAAAAAAAAAAAA
…………………………………………..
23F4CF90: 41414141 41414141 41414141 41414141 †AAAAAAAAAAAAAAAA
23F4CFA0: 0300f800 00000000 00000043 0e000000 †...........C....
23F4CFB0: 00500040 0f424242 42424242 42424242 †.P.@.BBBBBBBBBBB
23F4CFC0: 42424242 42424242 42424242 42424242 †BBBBBBBBBBBBBBBB
23F4CFD0: 42424242 42424242 42424242 42424242 †BBBBBBBBBBBBBBBB
On voit que les données démarrent avec la clé AAA : c'est tout à fait normal, l'index est ordonné. La suite de la page est donc logiquement la clé BBBB.
Par ailleurs le pointeur m_prevPage = (0:0) nous indique que la clé AAAA est la 1ère dans l'ordre, donc pas de page précédente.
De même la page suivante m_nextPage = (1:22081) nous indique que des données sont chainées pour respecter la logique du clustered : les données dans les feuilles représentent l'ordre de l'index.
Voyons cette page :
2414C000: 01010400 00a00001 d6550000 0100400f †.........U....@.
2414C010: 4c560000 01000200 cb010000 fa00021f †LV..............
2414C020: 41560000 01000000 5f000000 22010000 †AV......_..."...
2414C030: 07000000 00000000 00000000 00000000 †................
2414C040: 01000000 00000000 00000000 00000000 †................
2414C050: 00000000 00000000 00000000 00000000 †................
2414C060: 5000400f 45454545 45454545 45454545 †P.@.EEEEEEEEEEEE
2414C070: 45454545 45454545 45454545 45454545 †EEEEEEEEEEEEEEEE
2414C470: 45454545 45454545 45454545 45454545 †EEEEEEEEEEEEEEEE
2414C480: 45454545 45454545 45454545 45454545 †EEEEEEEEEEEEEEEE
……………………………………………………………………………………
2414CF80: 45454545 45454545 45454545 45454545 †EEEEEEEEEEEEEEEE
2414CF90: 45454545 45454545 45454545 45454545 †EEEEEEEEEEEEEEEE
2414CFA0: 0300f800 00000000 00000045 0e000000 †...........E....
2414CFB0: 00500040 0f464646 46464646 46464646 †.P.@.FFFFFFFFFFF
2414CFC0: 46464646 46464646 46464646 46464646 †FFFFFFFFFFFFFFFF
2414D010: 46464646 46464646 46464646 46464646 †FFFFFFFFFFFFFFFF
2414D020: 46464646 46464646 46464646 46464646 †FFFFFFFFFFFFFFFF
2414D030: 46464646 46464646 46464646 46464646 †FFFFFFFFFFFFFFFF
2414D040: 46464646 46464646 46464646 46464646 †FFFFFFFFFFFFFFFF
2414D050: 46464646 46464646 46464646 46464646 †FFFFFFFFFFFFFFFF
Il n'y a pas de CCCC, effectivement mon script d'insertion passait directement de BBBB à EEEEE.
Maintenant la question que vous vous posez est :
Que va faire SQLServer si j'insère la ligne CCCCCCC ?
Un réexamen de la 1ère page de la table qui n'a pas bougé nous montre que le pointeur de page suivante , lui, a bougé :
m_nextPage = (1:22094)
(Nous avions 1 :22081 tout à l'heure)
Regardons cette page :
Memory Dump @0x252EC000
252EC000: 01010400 00e00001 d6550000 0100400f †.........U....@.
252EC010: 41560000 01000100 cb010000 4d10b10f †AV..........M...
252EC020: 4e560000 01000000 5f000000 43010000 †NV......_...C...
252EC030: 1e000000 00000000 00000000 00000000 †................
252EC040: 01000000 00000000 00000000 00000000 †................
252EC050: 00000000 00000000 00000000 00000000 †................
252EC060: 5000400f 43434343 43434343 43434343 †P.@.CCCCCCCCCCCC
Eh oui ! on trouve bien nos valeurs de clé CCCC, SQLServer a donc respecté la loi pour les clustered qui est de permettre de lire les données dans l'ordre de la clé.
Maintenant, il le fait d'une manière logique, et non pas physique, par un jeu de pointeurs (Avant, Après)
Ceci s'explique bien par le fait que, s'il devait réorganiser toutes les pages lors d'un simple insert le temps de réponse de certains INSERT pourrait être dramatique (Que dire par exemple d'un INSERT du numéro de téléphone 00.00.00.00 sur une table qui contiendrait déjà 10 millions de lignes ?
Ceci étant, cette façon de faire a plusieurs incidences qu'il faut bien comprendre :
Des mises à jour de clés complètement aléatoires (dans leur valeur et donc leur rang) pourront désorganiser physiquement la table : c'est ce que l'on appelle la fragmentation.
C'est-à-dire que logiquement les données sont correctes mais que physiquement le parcours pour les lire est très important.
A l'inverse des mises à jour sur des clés croissantes (cas des identity) auront pour effet de venir mettre à jour le pointeur de fin de chaine et donc de laisser le début de la table en l'état.
C'est une des raisons qui me font préférer les clés chronologiques numériques (après INSERT de la valeur 12345 on passe à 12346 et ainsi de suite) L'utilisation de Identity répond bien à cette exigence même si elle pose d'autres problèmes que j'évoquerai dans une autre fiche.
Cas des index non clustered seuls:
Jusqu'ici nous n'avons traité que le cas de l'index clustered dont nous avons dit que les feuilles sont les données de la table.
Parlons maintenant des index non clustered : leur particularité est que leurs feuilles pointent soit sur des clés de clustered (s'il existe) soit sur les données elles mêmes s'il n'y a pas de clustered.
Voyons tout d'abord le cas du non clustered seul :
create
table DvetableTest (CLE char(900),NOM char(3000))
create
index DvetableTestCle on DvetableTest(CLE)
go
insert
into DvetableTest (CLE,NOM)
values
(replicate('A',900),replicate('A',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('B',900),replicate('B',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('E',900),replicate('E',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('F',900),replicate('F',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('G',900),replicate('G',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('H',900),replicate('H',3000))
insert
into DvetableTest (CLE,NOM)
values
(replicate('I',900),replicate('I',3000))
go
CLE HEAP RID (key) KeyHashValue
-------- ------------------ ----------------
AAA...AA 0xD655000001000000 (f8399714873d)
BBB...BB 0xD655000001000100 (d93a360eaeb1)
EEE...EE 0x4C56000001000000 (f23cf23a19a5)
FFF...FF 0x4C56000001000100 (d33d53203029)
GGG...GG 0x4D56000001000000 (b53e2cf7908f)
HHH...HH 0x4D56000001000100 (963fce48e23a)
III...II 0x4E56000001000000 (7840cc9867de)
Cette fois, la page d'index contient (entre autre) des couples (Valeur de clé, RID)
Ces RID sont des identifiants de lignes et on peut donc les considérer comme des pointeurs sur les lignes de données.
Bien sûr, se pose la même question des INSERT : et si j'insère la clé CCC que vont devenir mon index et mes données ?
CLE (key) HEAP RID (key) KeyHashValue
------ ------------------ ----------------
AA…AAA 0xD655000001000000 (f8399714873d)
BB…BBB 0xD655000001000100 (d93a360eaeb1)
CC…CCC 0x4E56000001000100 (323b4f5d320f)
EE…EEE 0x4C56000001000000 (f23cf23a19a5)
FF…FFF 0x4C56000001000100 (d33d53203029)
GG…GGG 0x4D56000001000000 (b53e2cf7908f)
HH…HHH 0x4D56000001000100 (963fce48e23a)
II…III 0x4E56000001000000 (7840cc9867de)
On constate que les données (RID) n'ont pas bougé, sauf, bien sûr la ligne qui a été insérée.
Il n'y aura donc pas dans ce cas de fragmentation due à des inserts de clés.
Maintenant on constate que les RID sont stockés dans les index et, dans le cas d'une table avec 10 index , déplacer la ligne de données d'une page vers une autre amènera SQLServer à actualiser les 10 index ! C'est la raison d'être de l'empilement clustered/non clustered que nous allons voir ci-dessous.
Par ailleurs il faut bien comprendre que, à défaut de clustered sur une table, les réorganisations physiques devront passer par un déchargement/rechargement des données, ce qui n'est évidemment pas toujours possible.
Enfin, ce type d'index pourra générer le problème des enregistrements déplacés (forwarded records) dont je parlerai plus tard.
Cas de l'empilement des index non clustered et clustered :
Nous allons maintenant traiter le cas très fréquent où, sur une table, on a à la fois des index non clustered et un index clustered.
create
table DvetableTest (CLENUMERIQUE int,CLEALPHA char(900),NOM char(3000))
create
clustered
index DvetableTestCleAlpha on DvetableTest(CLEALPHA)
create
index DvetableTestCleNumerique on DvetableTest(CLENUMERIQUE)
go
insert
into DvetableTest (CLENUMERIQUE,CLEALPHA,NOM)
values
(1,replicate('A',900),replicate('A',3000))
insert
into DvetableTest (CLENUMERIQUE,CLEALPHA,NOM)
values
(2,replicate('B',900),replicate('B',3000))
insert
into DvetableTest (CLENUMERIQUE,CLEALPHA,NOM)
values
(3,replicate('E',900),replicate('E',3000))
insert
into DvetableTest (CLENUMERIQUE,CLEALPHA,NOM)
values
(4,replicate('F',900),replicate('F',3000))
insert
into DvetableTest (CLENUMERIQUE,CLEALPHA,NOM)
values
(5,replicate('G',900),replicate('G',3000))
insert
into DvetableTest (CLENUMERIQUE,CLEALPHA,NOM)
values
(6,replicate('H',900),replicate('H',3000))
insert
into DvetableTest (CLENUMERIQUE,CLEALPHA,NOM)
values
(7,replicate('I',900),replicate('I',3000))
go
Examinons la racine du clustered index :
ChildPageId CLEALPHA (key) UNIQUIFIER (key) KeyHashValue
----------- ------------------ ---------------- ----------------
21974 NULL NULL (d1405a3162be)
22093 EEEEEEEEEEEEEEEEEE NULL (030014bf17fb)
22094 GGGGGGGGGGGGGGGGGG NULL (030014bf17fb)
22095 IIIIIIIIIIIIIIIIII NULL (030014bf17fb)
Nous n'avons que 4 valeurs de clés, mais j'ai déjà évoqué cet aspect de la question plus haut : SQLServer a choisi d'augmenter la profondeur de l'arbre d'index.
Donc rien de nouveau. : la page 1 :22093 contient bien les clés EEEEE et FFFF (l'index donnant ce qui est supérieur ou égal à en borne inférieure de la page).
Et maintenant, examinons le contenu de l'index sur la clé numérique :
CLENUMERIQUE (key) CLEALPHA (key) UNIQUIFIER (key) KeyHashValue
------------------ ------------------- ---------------- ----------------
1 AAA..900 fois..AAAA 0 (2539d9243f97)
2 BBB..900 fois..BBBB 0 (073a836d0362)
3 EEE..900 fois..EEEE 0 (ab3c9ed746c3)
4 FFF..900 fois..FFFF 0 (8d3d452ea5d9)
5 GGG..900 fois..GGGG 0 (6f3e4ceb613c)
6 HHH..900 fois..HHHH 0 (513f3e5c5f40)
7 III..900 fois..IIII 0 (334037999ba5)
Oh là là ! qu'est-ce que c'est que ces valeurs AAAA,BBBB ?
Eh oui ! C'est dû à l'empilement de l'index non clustered au dessus de clustered.
Dans le non clustered, en plus des données techniques on a des couples (Clé non clustered, Clé clustered).
On comprend mieux l'importance d'une bonne stratégie d'indexation ; dans mon exemple il aurait sans doute été beaucoup plus efficace de créer le clustered sur la clé numérique et non l'inverse.
Pour bien enfoncer le clou, voici ce qu'il ne faut pas faire :
Soit une table PERSONNEL contenant les colonnes :
NO_MATRICULE int
NOM nvarchar(60)
PRENOM nvarchar(60)
TELEPHONE nvarchar(20)
DATE_HEURE_POINTAGE datetime
Triant souvent les personnes par nom, je crée un clustered sur NOM,PRENOM
Me rendant compte que les index couvrant accélèrent les requêtes , j'ajoute DATE_HEURE_POINTAGE dans cet index clustered.
Par ailleurs pour assurer l'unicité du matricule, je crée un index non clustered sur le NO_MATRICULE .
Et enfin faisant souvent des recherches par téléphone, je crée un 3ème index non clustered sur TELEPHONE.
Si vous avez bien compris tout ce qui précède, vous êtes capable d'expliquer pourquoi cette stratégie est une hérésie….
Maintenant quelle est la bonne stratégie dans ce cas de figure ?
Réfléchissons un peu :
Le clustered tout d'abord : il doit contenir des valeurs stables, petites en taille de stockage, se rapprochant le plus possible de l'unicité,la colonne NO_MATRICULE me parait une bonne candidate…
On aura donc :
X1_PERSONNEL clustered unique sur NO_MATRICULE
X2_PERSONNEL non clustered sur NOM,PRENOM (mais cet ordre se discute)
X3_PERSONNEL sur TELEPHONE.
Ceci sera d'autant plus pertinent que l'optimiseur de SQLServer2005 est capable de faire des UNION ou des INTERSECTION d'index.
Cet exemple sur la table DVEProduct vu plus haut le montre :
select ProductID,Name,ProductNumber,Color
from DveProduct
where ProductID < 400 and Color =
'Blue'
Magique ! L'optimiseur s'est rendu compte que deux sous-recherches utilisant chacune un index seraient plus efficaces qu'une seule.
Les accès sont donc des seek, c'est-à-dire des recherches de plages de valeur dans des pages d'index.
Conclusion :
Mes missions en expertise de performances me montrent que les bonnes pratiques peuvent toujours donner les meilleures performances … que sont ces bonnes pratiques ?
- Bien choisir sa clé primaire et (la plupart du temps) son index clustered associé : clé petite, stable, numérique
- Indexer systématiquement les clés étrangères
- Analyser les clés habituelles de recherche et les indexer
- Enfin se focaliser sur les tris habituels
Au-delà des performances, bien comprendre que des clauses d'unicité non gérées par la base auront toujours pour effet d'insérer un jour des doublons !