Hello,
Un petit post rapide sur l’impact que peuvent avoir des valeurs de clés d’index clusterisé dupliquées sur les pages d’indexes non clusterisés.
En effet, par défaut la commande CREATE CLUSTERED INDEX va créer un index clusterisé non-unique, qui donc autorise l’insertion de valeurs dupliquées. Or cela pose un certain nombre de problèmes, notamment lorsque l’optimiseur choisit d’utiliser un index non clusterisé pour résoudre un prédicat et de faire des lectures additionnelles dans l’index clusterisé pour lire des colonnes non inclues dans l’index NC, ce qu’on appelle un bookmark lookup (bookmark = marque pages).
Exemple: une table MATABLE (col1,col2,col3) avec un index clusterisé sur col1 (bigint) et un index non clusterisé sur col2 (datetime)
set showplan_text on select col1, col2, col3 from MATABLE where col2 = '2010/03/16'
|--Nested Loops(Inner Join, OUTER REFERENCES:([CAPDATA].[dbo].[MATABLE].[col1]))
|--Index Seek(OBJECT:([CAPDATA].[dbo].[MATABLE].[MATABLE_IDXNCLU_col2]),
SEEK:([CAPDATA].[dbo].[MATABLE].[col2]='2010-03-16 00:00:
00.000') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([CAPDATA].[dbo].[MATABLE].[MATABLE_IDXCLU_col1]),
SEEK:([CAPDATA].[dbo].[MATABLE].[col1]=[CAPDATA].[dbo].[MATABLE].[col1])
LOOKUP ORDERED FORWARD)L’opérateur Index Seek est celui utilisé pour résoudre le prédicat sur la date et le Clustered Index Seek est l’opérateur choisi par l’optimiseur pour lire les colonnes additionnelles col1 et col3 (LOOKUP ORDERED FORWARD…).
Dans les pages d’un index non clusterisé, on va stocker à la fois la valeur de la clé de l’index NC (donc ici col2) ainsi que la valeur de la clé de l’index clusterisé (donc ici col1), qui va servir de pointeur entrant dans l’index clusterisé pour faire ce fameux Bookmark Lookup. La valeur de col1 représente donc le ‘marque-pages’ vers les pages de l’index clusterisé.
Maintenant comment savoir vers quelle page l’index NC va pointer lorsque col1 peut se trouver à plusieurs endroits de l’index clusterisé (lorsqu’il y a des valeurs dupliquées) ? Lorsqu’il y a des valeurs de clé dupliquées dans l’index clusterisé, SQL Server est obligé d’ajouter en plus de col2 et col1 un identifiant unique de type uniquifier (non visible par les outils courants SSMS et autres sp_helpindex) pour pointer vers la bonne ligne dans la bonne page. Et cet uniquifier ajoute 16 octets supplémentaires par ligne dans les pages de l’index NC.
Pour le démontrer, nous allons d’abord créer une table TABLE1 avec 3 colonnes, un index clusterisé non unique sur col1 et un index non clusterisé non unique sur col2.
CREATE TABLE TABLE1 ( col1 bigint NOT NULL, col2 bigint NOT NULL DEFAULT 10000, col3 CHAR (400) NOT NULL DEFAULT 'abcdefghijklmnopqrstuvwxyz'); CREATE CLUSTERED INDEX TABLE1_IDXCLU_col1 ON TABLE1 (col1); CREATE NONCLUSTERED INDEX TABLE1_IDXNCLU_col2 ON TABLE1 (col2); GO
Nous allons ensuite alimenter la table en forçant la génération de valeurs dupliquées:
DECLARE @a INT, @random BIGINT; SELECT @a = 1; SELECT @random = cast(rand(@a)*100 as bigint); WHILE (@a < 10000) BEGIN INSERT INTO TABLE1 VALUES (@random, DEFAULT, DEFAULT); SELECT @a = @a + 1; SELECT @random = cast(rand(@a)*100 as bigint); END; GO
En insérant 9999 valeurs comprises entre 1 et 100, il y a des chances pour que j’obtienne un certain nombre de valeurs en double:
select col1, COUNT(*) as compte from TABLE1 group by col1 having COUNT(*) > 1
col1 compte ---- --------- 89 531 72 537 75 537 78 536 86 537 81 536 84 536 87 536 73 537 85 537 76 537 88 537 79 537 82 537 77 537 80 537 71 344 83 537 74 536
Maintenant je vais m’intéresser à la structure de mon index NC, en utilisant l’option DETAILED de la DMF sys.dm_db_index_physical_stats(), afin de voir l’espace utilisé à chaque niveau de l’index NC ( la valeur 2 = indid du NC):
select index_level, min_record_size_in_bytes,
max_record_size_in_bytes, avg_record_size_in_bytes,
record_count, page_count
from sys.dm_db_index_physical_stats (db_id('CAPDATA'),object_id('TABLE1'),2,NULL,'DETAILED')
index_level min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
----------- ------------------------ ------------------------ ------------------------
0 17 25 24,984
1 23 31 30,764record_count page_count -------------------- -------------------- 9999 34 34 1
L’index NC compte 35 pages, 1 pages de niveau root et 34 pages de niveau feuille. Le niveau 1 représente le niveau root. Il compte une seule page donc et 34 lignes pointant vers les 34 pages du niveau inférieur. On voit que les tailles minimale et maximale varient entre 23 et 31 octets. Celà est dû au fait que presque chaque ligne contient en plus des valeurs de col2 et col1 un uniquifier permettant de distinguer les lignes dupliquées qui sont référencées. On peut le voir en allant lire le contenu de cette root page. Le dbcc ind() va nous permettre d’identifier cette root page:
dbcc ind('CAPDATA','TABLE1',2)
PageFID PagePID IAMFID IAMPID ObjectID IndexID (...) IndexLevel (...)
------- ----------- ------ ----------- ----------- ----------- --------------- --------------------
1 25916 NULL NULL 1138103095 2 0
1 25915 1 25916 1138103095 2 0
1 23719 1 25916 1138103095 2 1
(...)index_level à 1, notre page est la 23719. un bref dbcc page:
dbcc traceon(3604) dbcc page(5,1,23719,3) FileId PageId Row Level ChildFileId ChildPageId col2 (key) col1 (key) UNIQUIFIER (key) KeyHashValue ------ ----------- ------ ------ ----------- ----------- -------------------- -------------------- ---------------- ---------------- 1 23719 0 1 1 25915 NULL NULL NULL NULL 1 23719 1 1 1 23728 10000 71 300 NULL 1 23719 2 1 1 23729 10000 72 256 NULL 1 23719 3 1 1 23730 10000 73 19 NULL (...)
On voit qu’une colonne a donc été ajoutée. On pourra faire les mêmes observations avec les pages de niveau feuille de l’index NC.
Maintenant si on crée une seconde table TABLE 2 avec cette fois un index clusterisé UNIQUE:
CREATE TABLE TABLE2 ( col1 bigint NOT NULL, col2 bigint NOT NULL DEFAULT 10000 , col3 CHAR (400) NOT NULL DEFAULT 'abcdefghijklmnopqrstuvwxyz'); CREATE UNIQUE CLUSTERED INDEX TABLE2_IDXCLU_col1 ON TABLE2 (col1); CREATE NONCLUSTERED INDEX TABLE2_IDXNCLU_col2 ON TABLE2 (col2); GO DECLARE @a INT; SELECT @a = 1; WHILE (@a < 10000) BEGIN INSERT INTO TABLE2 VALUES (@a, DEFAULT, DEFAULT); SELECT @a = @a + 1; END; GO
Jetons un coup d’oeil à sa structure:
select index_level,min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, record_count, page_count
from sys.dm_db_index_physical_stats (db_id('CAPDATA'),object_id('TABLE2'),2,NULL,'DETAILED')index_level min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes record_count page_count ----------- ------------------------ ------------------------ ------------------------ -------------------- -------------------- 0 17 17 17 9999 24 1 23 23 23 24 1
Il ne fait plus que 25 pages (24+1), tout simplement parce qu’il n’y a plus de uniquifier dans les pages. La taille moyenne de chaque ligne est redevenue normale. On retrouve la root page correspondante de la même façon que plus haut, et en lisant son contenu:
dbcc traceon(3604) dbcc page(5,1,23745,3) FileId PageId Row Level ChildFileId ChildPageId col2 (key) col1 (key) KeyHashValue ------ ----------- ------ ------ ----------- ----------- -------------------- -------------------- ---------------- 1 23745 0 1 1 25923 NULL NULL NULL 1 23745 1 1 1 23746 10000 427 NULL 1 23745 2 1 1 23747 10000 853 NULL (...)
La colonne UNIQUIFIER a disparu. Donc morale de l’histoire, il faut penser à ajouter le mot-clé UNIQUE dans les créations d’indexes clusterisés.
J’ajoute aussi que le uniquifier n’est ajouté que lorsqu’il y a effectivement des doublons dans l’index clusterisé. Tant qu’il n’y a pas de doublon détecté, même si l’index a été créé sans préciser UNIQUE, il n’y aura pas de colonne additionnelle.
A+, David B.
Continuez votre lecture sur le blog :
- Fragmentation sur des tables stockées en S-GAM (David Baffaleuf) [SQL Server]
- Alter table rebuild (Benjamin VESAN) [SQL Server]
- Récupérer l’espace consommé par le versionning de lignes (David Baffaleuf) [SQL Server]
- Error 8976 / 8978, problèmes de chaînage, comment récupérer les données (David Baffaleuf) [SQL Server]
- Oracle et SQL Server: La Fragmentation (Benjamin VESAN) [OracleSQL Server]