Parmi les fondamentaux de SQL Server figure la notion d’Index Clustered, souvent mal comprise par les débutants. Dans ce deuxième billet concernant l’organisation des données, nous chercherons à comprendre ce qu’est un index clustered.
Tout d’abord, présentons la notion d’index. Un index s’appuie sur une ou plusieurs colonnes, constituant la clé. Au niveau final de l’index, les différents enregistrements sont classés suivant la clé. Pour cela, en partant d’une page au niveau racine, et éventuellement à travers un certain nombre de niveaux intermédiaires, un arbre B (voir https://fr.wikipedia.org/wiki/Arbre_B). Ce type de structure permet, en consommant uniquement un nombre réduit de lectures de pages, d’arriver rapidement aux données que l’on cherche à lire.
Un index Clustered est donc un arbre B qui va partir d’une page racine et arriver au niveau des pages terminales, appelées feuilles. Ces feuilles contiennent toutes les colonnes de chacun des enregistrements (je passerai volontairement ici outre les données sorties des pages, comme peuvent l’être par exemple les données de chaîne ou binaires d’une longueur supérieure à 8Ko).
Prenons dans un premier temps un exemple très simple, avec seulement quelques enregistrements.
create table TableDonnees (Id bigint identity, Donnee1 varchar(500), Donnee2 varchar(500))
insert into TableDonnees (Donnee1,Donnee2)
values ('Court1A','Court2A'),
('Court1X','Court2X'),
('Court1B','Court2B'),
('Court1C','Court2C'),
('Court1Z','Court2Z')
create unique clustered index Index_Clustered on TableDonnees(Id)
Voici le contenu de la page feuille de l’index Clustered pour cette table :
Slot 0 Offset 0x60 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000000CFBA060
0000000000000000: 30000c00 01000000 00000000 03000002 †0……………
0000000000000010: 001c0023 00436f75 72743141 436f7572 †…#.Court1ACour
0000000000000020: 743241†††††††††††††††††††††††††††††††t2A
Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 1
Slot 0 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1A
Slot 0 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2A
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (1b7fe5b8af93)
Slot 1 Offset 0x83 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000000CFBA083
0000000000000000: 30000c00 02000000 00000000 03000002 †0……………
0000000000000010: 001c0023 00436f75 72743158 436f7572 †…#.Court1XCour
0000000000000020: 743258†††††††††††††††††††††††††††††††t2X
Slot 1 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 2
Slot 1 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1X
Slot 1 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2X
Slot 1 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (f03d7d8b0dcc)
Slot 2 Offset 0xa6 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000000CFBA0A6
0000000000000000: 30000c00 03000000 00000000 03000002 †0……………
0000000000000010: 001c0023 00436f75 72743142 436f7572 †…#.Court1BCour
0000000000000020: 743242†††††††††††††††††††††††††††††††t2B
Slot 2 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 3
Slot 2 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1B
Slot 2 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2B
Slot 2 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (a903f5656cf9)
Slot 3 Offset 0xc9 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000000CFBA0C9
0000000000000000: 30000c00 04000000 00000000 03000002 †0……………
0000000000000010: 001c0023 00436f75 72743143 436f7572 †…#.Court1CCour
0000000000000020: 743243†††††††††††††††††††††††††††††††t2C
Slot 3 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 4
Slot 3 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1C
Slot 3 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2C
Slot 3 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (cd59dedaa3da)
Slot 4 Offset 0xec Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000000CFBA0EC
0000000000000000: 30000c00 05000000 00000000 03000002 †0……………
0000000000000010: 001c0023 00436f75 7274315a 436f7572 †…#.Court1ZCour
0000000000000020: 74325a†††††††††††††††††††††††††††††††t2Z
Slot 4 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 5
Slot 4 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1Z
Slot 4 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2Z
Slot 4 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (94675634c2ef)
Le principe de l’index est que les données sont toujours classées par ordre. Ainsi, si l’on rajoute une valeur, elle ne vient pas par défaut se placer en fin de liste, mais va trouver sa place quitte à décaler les autres enregistrements.
set identity_insert TableDonnees on
insert into TableDonnees (Id,Donnee1,Donnee2) values (-1,'Court1-A','Court2-A') set identity_insert TableDonnees off
On remarque bien que la valeur -1 est bien venue se placer sur le slot 0, et a de fait décalé l’ensemble des autres slots. A noter toutefois que ce décalage n’a eu lieu que sur les numéros de slots (qui sont stockés en fin de la page), mais que pour minimiser l’impact, les données en elles-mêmes n’ont pas été décalées.
Slot 0 Offset 0x10f Length 37
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 37
Memory Dump @0x000000000FD5A10F
0000000000000000: 30000c00 ffffffff ffffffff 03000002 †0…ÿÿÿÿÿÿÿÿ….
0000000000000010: 001d0025 00436f75 7274312d 41436f75 †…%.Court1-ACou
0000000000000020: 7274322d 41††††††††††††††††††††††††††rt2-A
Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = -1
Slot 0 Column 2 Offset 0x15 Length 8 Length (physical) 8
Donnee1 = Court1-A
Slot 0 Column 3 Offset 0x1d Length 8 Length (physical) 8
Donnee2 = Court2-A
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (ffffffffffff)
Slot 1 Offset 0x60 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000000FD5A060
0000000000000000: 30000c00 01000000 00000000 03000002 †0……………
0000000000000010: 001c0023 00436f75 72743141 436f7572 †…#.Court1ACour
0000000000000020: 743241†††††††††††††††††††††††††††††††t2A
Slot 1 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 1
Slot 1 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1A
Slot 1 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2A
Slot 1 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (1b7fe5b8af93)
Slot 2 Offset 0x83 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000000FD5A083
0000000000000000: 30000c00 02000000 00000000 03000002 †0……………
0000000000000010: 001c0023 00436f75 72743158 436f7572 †…#.Court1XCour
0000000000000020: 743258†††††††††††††††††††††††††††††††t2X
Slot 2 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 2
Slot 2 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1X
Slot 2 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2X
Slot 2 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (f03d7d8b0dcc)
Slot 3 Offset 0xa6 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000000FD5A0A6
0000000000000000: 30000c00 03000000 00000000 03000002 †0……………
0000000000000010: 001c0023 00436f75 72743142 436f7572 †…#.Court1BCour
0000000000000020: 743242†††††††††††††††††††††††††††††††t2B
Slot 3 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 3
Slot 3 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1B
Slot 3 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2B
Slot 3 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (a903f5656cf9)
Slot 4 Offset 0xc9 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000000FD5A0C9
0000000000000000: 30000c00 04000000 00000000 03000002 †0……………
0000000000000010: 001c0023 00436f75 72743143 436f7572 †…#.Court1CCour
0000000000000020: 743243†††††††††††††††††††††††††††††††t2C
Slot 4 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 4
Slot 4 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1C
Slot 4 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2C
Slot 4 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (cd59dedaa3da)
Slot 5 Offset 0xec Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000000FD5A0EC
0000000000000000: 30000c00 05000000 00000000 03000002 †0……………
0000000000000010: 001c0023 00436f75 7274315a 436f7572 †…#.Court1ZCour
0000000000000020: 74325a†††††††††††††††††††††††††††††††t2Z
Slot 5 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 5
Slot 5 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1Z
Slot 5 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2Z
Slot 5 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (94675634c2ef)
De même, si l’on enlève des enregistrements, les slots seront renommés en conséquence.
La question qui vient assez rapidement est : que se passe-t-il s’il n’y a pas suffisamment de plage dans la page de 8Ko pour stocker la donnée ? Pour un stockage sans index clustered, on ne se pose pas la question, le système crée simplement une nouvelle page dans laquelle il peut stocker les nouvelles données. Mais là, comme il est impératif de respecter l’ordre de classement imposé par l’index, il y a tout simplement un découpage de la page en deux (aussi appelé Split). A noter que cela aura en cascade un impact sur l’ensemble de l’arbre d’index, et pourra même potentiellement aller jusqu’à ajouter un niveau à l’arbre (mais ce cas extrême est bien entendu très rare).
Même si le nombre de pages impactées reste très souvent réduit, le coût reste néanmoins conséquent au regard du fait qu’il s’agit d’une simple insertion d’un unique enregistrement. C’est pourquoi il est conseillé de choisir pour les index clustered un ordre tel que les derniers enregistrements viennent se placer à la fin (par exemple avec, en début d’index, un numéro chrono, ou une colonne DateTime de création), ou bien d’utiliser de manière judicieuse le paramètre de Taux de Remplissage (Fillfactor, voir https://docs.microsoft.com/fr-fr/previous-versions/sql/sql-server-2008-r2/ms177459(v=sql.105)) afin de conserver un certain pourcentage des pages disponible pour justement placer les nouveaux enregistrements insérés (ou bien permettre aux enregistrements en plage de prendre un peu d’embonpoint).
Dès lors qu’une table commence à grossir un peu, il devient très vite évident que l’usage d’un index pour retrouver les données est primordial, afin de ne pas avoir à chercher nos données en parcourant systématiquement la Terre entière…
Par exemple, remplissons une grosse table :
create table GrosseTableSansIndexClustered (Id int Identity, Donnee varchar(50)) go create table GrosseTableAvecIndexClustered (Id int Identity, Donnee varchar(50)) create unique clustered index Index_GrosseTable on GrosseTableAvecIndexClustered(Id) go declare @Donnee varchar(50) select @Donnee=CONVERT(varchar(50),NEWID()) insert into GrosseTableSansIndexClustered(Donnee) values (@Donnee) insert into GrosseTableAvecIndexClustered(Donnee) values (@Donnee) go 100000
Si maintenant nous cherchons un enregistrement particulier, les statistiques de durées et d’entrées-sorties parlent d’elles-mêmes…
set nocount on set statistics io on set statistics time on print 'Sans index' select * from GrosseTableSansIndexClustered where id=90000 print 'Avec index' select * from GrosseTableAvecIndexClustered where id=90000
Sans index
SQL Server \endash Temps d’exécution :
, Temps UC = 0 ms, temps écoulé = 0 ms.
Temps d’analyse et de compilation de SQL Server :
, Temps UC = 0 ms, temps écoulé = 0 ms.
Table ‘GrosseTableSansIndexClustered’. Nombre d’analyses 1, lectures logiques 685, lectures physiques 0, lectures anticipées 38, lectures logiques de données d’objets volumineux 0, lectures physiques de données d’objets volumineux 0, lectures anticipées de données d’objets volumineux 0.
SQL Server \endash Temps d’exécution :
, Temps UC = 16 ms, temps écoulé = 11 ms.
Avec index
SQL Server \endash Temps d’exécution :
, Temps UC = 0 ms, temps écoulé = 0 ms.
Temps d’analyse et de compilation de SQL Server :
, Temps UC = 0 ms, temps écoulé = 0 ms.
Table ‘GrosseTableAvecIndexClustered’. Nombre d’analyses 0, lectures logiques 3, lectures physiques 0, lectures anticipées 0, lectures logiques de données d’objets volumineux 0, lectures physiques de données d’objets volumineux 0, lectures anticipées de données d’objets volumineux 0.
SQL Server \endash Temps d’exécution :
, Temps UC = 0 ms, temps écoulé = 0 ms.
Dans un prochain billet, nous étudierons un autre type d’index, les indexes non clustered. En attendant, n’hésitez pas m’envoyer moult questions par mail sur les indexes clustered, j’y répondrai avec plaisir…
Bonjour,
– Qu’appelez-vous « slot » ?
– Comment avez-vous fait les « captures » de ces slots ? Existe-t-il des commandes Sql server qui donnent le résultat que vous publiez ? Si oui lesquelles ?
– Un index clustered est-il imbriqué avec les données de la table ? Les données ne sont pas « dupliquées » ?
Merci d’avance pour vos réponses…
Cordialement
Jean-Pierre
Bonjour,
Voici quelques éléments de réponse à vos questions :
– SQL Server stocke ses données dans des pages de 8Ko (invariable). Lorsque ces pages contiennent des données (c’est-à-dire lorsqu’il s’agit de pages de type segment de données ou bien de pages terminales (feuilles) d’index), un ensemble de lignes sont stockées (une ligne par enregistrement de la table, sachant que les données d’un enregistrement ne seront jamais à cheval sur 2 pages). Ce sont ces emplacements de stockage de lignes au sein d’une page qui sont appelés Slots. (leur taille varie suivant ce qu’il y a à stocker, et donc leur nombre peut varier d’une page à l’autre).
– Les captures du contenu des pages ont été faites avec la commande DBCC PAGE.
– Lorsqu’une table ne contient pas d’index clustered, les données sont rangées comme elles viennent (HEAP). Lorsqu’une table contient un index clustered, il vient complètement remplacer le stockage sous forme HEAP (et donc il n’y a pas de duplication de données). Pour vous en convaincre, je vous conseille de créer une table MaTable sans index clustered, puis de lancer la commande SELECT * from sys.indexes where object_id=OBJECT_ID(‘MaTable’). Vous constaterez un index avec index_id=0, qui correspond au segment de données. Si ensuite vous créez un index clustered sur cette table et relancez le SELECT, vous verrez votre index avec index_id=1 (toujours cette valeur pour l’index clustered) qui aura remplacé le stockage précédent.
Cordialement.
JN BERGER