{"id":768,"date":"2010-03-16T18:16:24","date_gmt":"2010-03-16T17:16:24","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=768"},"modified":"2022-12-01T17:54:59","modified_gmt":"2022-12-01T16:54:59","slug":"interet-de-creer-des-indexes-cluster-uniques","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/interet-de-creer-des-indexes-cluster-uniques\/","title":{"rendered":"Int\u00e9r\u00eat de cr\u00e9er des indexes cluster uniques"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Hello,<\/p>\n

Un petit post rapide sur l’impact que peuvent avoir des valeurs de cl\u00e9s d’index clusteris\u00e9 dupliqu\u00e9es sur les pages d’indexes non clusteris\u00e9s.<\/p>\n

En effet, par d\u00e9faut la commande CREATE CLUSTERED INDEX va cr\u00e9er un index clusteris\u00e9 non-unique, qui donc autorise l’insertion de valeurs dupliqu\u00e9es. Or cela pose un certain nombre de probl\u00e8mes, notamment lorsque l’optimiseur choisit d’utiliser un index non clusteris\u00e9 pour r\u00e9soudre un pr\u00e9dicat et de faire des lectures additionnelles dans l’index clusteris\u00e9 pour lire des colonnes non inclues dans l’index NC, ce qu’on appelle un bookmark lookup<\/em> (bookmark = marque pages).<\/p>\n

Exemple: une table MATABLE (col1,col2,col3) avec un index clusteris\u00e9 sur col1 (bigint) et un index non clusteris\u00e9 sur col2 (datetime)
\n<\/strong><\/em><\/p>\n

set showplan_text on\r\nselect col1, col2, col3 from MATABLE where col2 = '2010\/03\/16'<\/pre>\n
 |--Nested Loops(Inner Join, OUTER REFERENCES:([CAPDATA].[dbo].[MATABLE].[col1]))\r\n    |--Index Seek(OBJECT:([CAPDATA].[dbo].[MATABLE].[MATABLE_IDXNCLU_col2]),\r\n                 SEEK:([CAPDATA].[dbo].[MATABLE].[col2]='2010-03-16 00:00:\r\n                  00.000') ORDERED FORWARD)\r\n    |--Clustered Index Seek(OBJECT:([CAPDATA].[dbo].[MATABLE].[MATABLE_IDXCLU_col1]),\r\n                 SEEK:([CAPDATA].[dbo].[MATABLE].[col1]=[CAPDATA].[dbo].[MATABLE].[col1])\r\n                 LOOKUP ORDERED FORWARD)<\/pre>\n
L’op\u00e9rateur Index Seek<\/strong> est celui utilis\u00e9 pour r\u00e9soudre le pr\u00e9dicat sur la date et le Clustered Index Seek<\/strong> est l’op\u00e9rateur choisi par l’optimiseur pour lire les colonnes additionnelles col1 et col3 (LOOKUP ORDERED FORWARD…).<\/p>\n
Dans les pages\u00a0 d’un index non clusteris\u00e9, on va stocker\u00a0 \u00e0 la fois la valeur de la cl\u00e9 de l’index NC (donc ici col2) ainsi que la valeur de la cl\u00e9 de l’index clusteris\u00e9 (donc ici col1), qui va servir de pointeur entrant dans l’index clusteris\u00e9 pour faire ce fameux Bookmark Lookup<\/em>. La valeur de col1 repr\u00e9sente donc le ‘marque-pages’ vers les pages de l’index clusteris\u00e9.<\/p>\n
Maintenant comment savoir vers quelle page l’index NC va pointer lorsque col1 peut se trouver \u00e0 plusieurs endroits de l’index clusteris\u00e9 (lorsqu’il y a des valeurs dupliqu\u00e9es) ? Lorsqu’il y a des valeurs de cl\u00e9 dupliqu\u00e9es dans l’index clusteris\u00e9, SQL Server est oblig\u00e9 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\u00e9mentaires par ligne dans les pages de l’index NC.<\/p>\n
Pour le d\u00e9montrer, nous allons d’abord cr\u00e9er une table TABLE1 avec 3 colonnes, un index clusteris\u00e9 non unique sur col1 et un index non clusteris\u00e9 non unique sur col2.<\/p>\n
CREATE TABLE TABLE1 (\r\n col1 bigint NOT NULL,\r\n col2 bigint NOT NULL DEFAULT 10000,\r\n col3 CHAR (400) NOT NULL DEFAULT 'abcdefghijklmnopqrstuvwxyz');\r\n\r\nCREATE CLUSTERED INDEX TABLE1_IDXCLU_col1 ON TABLE1 (col1);\r\nCREATE NONCLUSTERED INDEX TABLE1_IDXNCLU_col2 ON TABLE1 (col2);\r\nGO<\/pre>\n
Nous allons ensuite alimenter la table\u00a0 en for\u00e7ant la g\u00e9n\u00e9ration de valeurs dupliqu\u00e9es:<\/p>\n
DECLARE @a INT, @random BIGINT;\r\nSELECT @a = 1;\r\nSELECT @random = cast(rand(@a)*100 as bigint);\r\nWHILE (@a < 10000)\r\nBEGIN\r\n INSERT INTO TABLE1 VALUES (@random, DEFAULT, DEFAULT);\r\n SELECT @a = @a + 1;\r\n SELECT @random = cast(rand(@a)*100 as bigint);\r\nEND;\r\nGO<\/pre>\n
En ins\u00e9rant 9999 valeurs comprises entre 1 et 100, il y a des chances pour que j’obtienne un certain nombre de valeurs en double:<\/p>\n
select col1, COUNT(*) as compte\r\nfrom TABLE1 group by col1 having COUNT(*) > 1<\/pre>\n
col1\u00a0\u00a0 compte\r\n----\u00a0\u00a0\u00a0 ---------\r\n89\u00a0\u00a0 \u00a0531\r\n72\u00a0\u00a0 \u00a0537\r\n75\u00a0\u00a0 \u00a0537\r\n78\u00a0\u00a0 \u00a0536\r\n86\u00a0\u00a0 \u00a0537\r\n81\u00a0\u00a0 \u00a0536\r\n84\u00a0\u00a0 \u00a0536\r\n87\u00a0\u00a0 \u00a0536\r\n73\u00a0\u00a0 \u00a0537\r\n85\u00a0\u00a0 \u00a0537\r\n76\u00a0\u00a0 \u00a0537\r\n88\u00a0\u00a0 \u00a0537\r\n79\u00a0\u00a0 \u00a0537\r\n82\u00a0\u00a0 \u00a0537\r\n77\u00a0\u00a0 \u00a0537\r\n80\u00a0\u00a0 \u00a0537\r\n71\u00a0\u00a0 \u00a0344\r\n83\u00a0\u00a0 \u00a0537\r\n74\u00a0\u00a0 \u00a0536<\/pre>\n
Maintenant je vais m’int\u00e9resser \u00e0 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\u00e9 \u00e0 chaque niveau de l’index NC ( la valeur 2 = indid du NC):<\/p>\n
select index_level, min_record_size_in_bytes,\r\nmax_record_size_in_bytes, avg_record_size_in_bytes,\r\nrecord_count, page_count\r\nfrom sys.dm_db_index_physical_stats (db_id('CAPDATA'),object_id('TABLE1'),2,NULL,'DETAILED')\r\n\r\nindex_level min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes\r\n----------- ------------------------ ------------------------ ------------------------\r\n0\u00a0\u00a0 \u00a0       17\u00a0\u00a0 \u00a0                    25\u00a0\u00a0 \u00a0                   24,984\u00a0\u00a0 \u00a0\r\n1\u00a0\u00a0 \u00a0       23\u00a0\u00a0 \u00a0                    31\u00a0\u00a0 \u00a0                   30,764<\/pre>\n
record_count\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 page_count\r\n-------------------- --------------------\r\n9999\u00a0\u00a0 \u00a0             34\r\n34\u00a0\u00a0 \u00a0               1<\/pre>\n
L’index NC compte 35 pages, 1 pages de niveau root et 34 pages de niveau feuille. Le niveau 1 repr\u00e9sente le niveau root. Il compte une seule page donc et 34 lignes pointant vers les 34 pages du niveau inf\u00e9rieur. On voit que les tailles minimale et maximale varient entre 23 et 31 octets. Cel\u00e0 est d\u00fb au fait que presque chaque ligne\u00a0 contient en plus des valeurs de col2 et col1 un uniquifier permettant de distinguer les lignes dupliqu\u00e9es qui sont r\u00e9f\u00e9renc\u00e9es. On peut le voir en allant lire le contenu de cette root page. Le dbcc ind() va nous permettre d’identifier cette root page:<\/p>\n
dbcc ind('CAPDATA','TABLE1',2)\r\nPageFID\u00a0 PagePID\u00a0\u00a0\u00a0\u00a0 IAMFID \u00a0\u00a0 IAMPID\u00a0\u00a0\u00a0\u00a0\u00a0 ObjectID\u00a0\u00a0\u00a0 IndexID\u00a0\u00a0\u00a0\u00a0 (...)            IndexLevel      (...)\r\n------- \u00a0----------- ------ \u00a0 \u00a0----------- ----------- ----------- --------------- -------------------- \u00a0 \u00a0 \u00a0\u00a0\r\n1\u00a0\u00a0 \u00a0 \u00a0 \u00a025916\u00a0\u00a0 \u00a0\u00a0\u00a0 NULL\u00a0\u00a0 \u00a0\u00a0 NULL\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 1138103095\u00a0\u00a0  2\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\r\n1\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 25915\u00a0\u00a0 \u00a0\u00a0\u00a0 1\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 25916\u00a0\u00a0 \u00a0\u00a0\u00a0 1138103095\u00a0\u00a0 \u00a02\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\n1\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 23719\u00a0\u00a0 \u00a0\u00a0\u00a0 1\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 25916\u00a0\u00a0 \u00a0\u00a0\u00a0 1138103095\u00a0\u00a0 \u00a02\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n(...)<\/pre>\n
index_level \u00e0 1, notre page est la 23719<\/strong>. un bref dbcc page:<\/p>\n
dbcc traceon(3604)\r\ndbcc page(5,1,23719,3)\r\nFileId PageId\u00a0\u00a0\u00a0\u00a0\u00a0 Row\u00a0\u00a0\u00a0 Level\u00a0 ChildFileId ChildPageId col2 (key)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 col1 (key)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNIQUIFIER (key) KeyHashValue\r\n------ ----------- ------ ------ ----------- ----------- -------------------- -------------------- ---------------- ----------------\r\n1\u00a0\u00a0 \u00a023719\u00a0\u00a0 \u00a0     0\u00a0\u00a0 \u00a0   1\u00a0\u00a0 \u00a0  1\u00a0\u00a0 \u00a0       25915\u00a0\u00a0 \u00a0   NULL\u00a0\u00a0 \u00a0              NULL\u00a0\u00a0 \u00a0           NULL\u00a0\u00a0 \u00a0          NULL\r\n1\u00a0\u00a0 \u00a023719\u00a0\u00a0 \u00a0     1\u00a0\u00a0 \u00a0   1\u00a0\u00a0 \u00a0  1\u00a0\u00a0 \u00a0       23728\u00a0\u00a0 \u00a0   10000\u00a0\u00a0 \u00a0             71\u00a0\u00a0 \u00a0             300\u00a0\u00a0 \u00a0           NULL\r\n1\u00a0\u00a0 \u00a023719\u00a0\u00a0 \u00a0     2\u00a0\u00a0 \u00a0   1\u00a0\u00a0 \u00a0  1\u00a0\u00a0 \u00a0       23729\u00a0\u00a0 \u00a0   10000\u00a0\u00a0 \u00a0             72\u00a0\u00a0 \u00a0             256\u00a0\u00a0 \u00a0           NULL\r\n1\u00a0\u00a0 \u00a023719\u00a0\u00a0 \u00a0     3\u00a0\u00a0 \u00a0   1\u00a0\u00a0 \u00a0  1\u00a0\u00a0 \u00a0       23730\u00a0\u00a0 \u00a0   10000\u00a0\u00a0 \u00a0             73\u00a0\u00a0 \u00a0             19\u00a0\u00a0 \u00a0            NULL\r\n(...)<\/pre>\n
On voit qu’une colonne a donc \u00e9t\u00e9 ajout\u00e9e. On pourra faire les m\u00eames observations avec les pages de niveau feuille de l’index NC.<\/p>\n
Maintenant si on cr\u00e9e une seconde table TABLE 2 avec cette fois un index clusteris\u00e9 UNIQUE:<\/p>\n
CREATE TABLE TABLE2 (\r\n col1 bigint NOT NULL,\r\n col2 bigint NOT NULL DEFAULT 10000 ,\r\n col3 CHAR (400) NOT NULL DEFAULT 'abcdefghijklmnopqrstuvwxyz');\r\n\r\nCREATE UNIQUE CLUSTERED INDEX TABLE2_IDXCLU_col1 ON TABLE2 (col1);\r\nCREATE NONCLUSTERED INDEX TABLE2_IDXNCLU_col2 ON TABLE2 (col2);\r\nGO\r\n\r\nDECLARE @a INT;\r\nSELECT @a = 1;\r\nWHILE (@a < 10000)\r\nBEGIN\r\n INSERT INTO TABLE2 VALUES (@a, DEFAULT, DEFAULT);\r\n SELECT @a = @a + 1;\r\nEND;\r\nGO<\/pre>\n
Jetons un coup d’oeil \u00e0 sa structure:<\/p>\n
select index_level,min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, record_count, page_count\r\nfrom sys.dm_db_index_physical_stats (db_id('CAPDATA'),object_id('TABLE2'),2,NULL,'DETAILED')<\/pre>\n
index_level min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes record_count\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 page_count\r\n----------- ------------------------ ------------------------ ------------------------ -------------------- --------------------\r\n0\u00a0\u00a0 \u00a0        17\u00a0\u00a0 \u00a0                  17\u00a0\u00a0 \u00a0                    17\u00a0\u00a0 \u00a0                   9999\u00a0\u00a0 \u00a0             24\r\n1\u00a0\u00a0 \u00a0        23\u00a0\u00a0 \u00a0                  23\u00a0\u00a0 \u00a0                    23\u00a0\u00a0 \u00a0                    24\u00a0\u00a0 \u00a0              1<\/pre>\n
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\u00eame fa\u00e7on que plus haut, et en lisant son contenu:<\/p>\n
dbcc traceon(3604)\r\ndbcc page(5,1,23745,3)\r\nFileId PageId\u00a0\u00a0\u00a0\u00a0\u00a0 Row\u00a0\u00a0\u00a0 Level\u00a0 ChildFileId ChildPageId col2 (key)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 col1 (key)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 KeyHashValue\r\n------ ----------- ------ ------ ----------- ----------- -------------------- -------------------- ----------------\r\n1\u00a0\u00a0 \u00a0  23745\u00a0\u00a0 \u00a0   0\u00a0\u00a0 \u00a0   1\u00a0\u00a0 \u00a0    1\u00a0\u00a0      \u00a025923\u00a0\u00a0 \u00a0     NULL\u00a0\u00a0 \u00a0            NULL\u00a0\u00a0 \u00a0            NULL\r\n1\u00a0\u00a0 \u00a0  23745\u00a0\u00a0 \u00a0   1\u00a0\u00a0 \u00a0   1\u00a0\u00a0     \u00a01\u00a0\u00a0 \u00a0     23746\u00a0\u00a0 \u00a0     10000\u00a0\u00a0 \u00a0           427\u00a0\u00a0 \u00a0             NULL\r\n1\u00a0\u00a0 \u00a0  23745\u00a0\u00a0 \u00a0   2\u00a0\u00a0 \u00a0   1\u00a0\u00a0 \u00a0    1\u00a0\u00a0 \u00a0     23747\u00a0\u00a0 \u00a0     10000\u00a0\u00a0 \u00a0           853\u00a0\u00a0 \u00a0             NULL\r\n(...)<\/pre>\n
La colonne UNIQUIFIER a disparu. Donc morale de l’histoire, il faut penser \u00e0 ajouter le mot-cl\u00e9 UNIQUE dans les cr\u00e9ations d’indexes clusteris\u00e9s.<\/p>\n
J’ajoute aussi que le uniquifier n’est ajout\u00e9 que lorsqu’il y a effectivement des doublons dans l’index clusteris\u00e9. Tant qu’il n’y a pas de doublon d\u00e9tect\u00e9, m\u00eame si l’index a \u00e9t\u00e9 cr\u00e9\u00e9 sans pr\u00e9ciser UNIQUE, il n’y aura pas de colonne additionnelle.<\/p>\n
A+, David B.<\/p>\n