Intérêt de créer des indexes cluster uniques

Mardi, mars 16, 2010
By David Baffaleuf in SQL Server (dbaffaleuf@capdata-osmozium.com) [71 article(s)]

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,764
record_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 :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: , , , ,

Leave a Reply