Stockage des colonnes éparses faisant parties de la clause INCLUDE d'un index

**hmira** · 22/05/2019, 16h28

Bonjour,

Comment sont stockées, au niveau d'un index non cluster, les valeurs nulles d'une colonne déclarée éparse au niveau de la table elle-même (colonne éparse de la table), lorsque la dite colonne figure uniquement dans la clause INCLUDE et ne fait pas partie des clés de l'index. En d'autres termes, ces valeurs nulles, au niveau de l'index non cluster, en tant que structure physique, occupent-elles ou pas de l'espace disque ? Sont-elles gérées de la même manière que leur pendant, colonne éparse, définie au niveau de la table elle-même (ou de son index cluster) ?

Je précise que les indexes non cluster bâtis sur les colonnes éparses sont généralement filtrés, c.à.d. ils doté d’une clause WHERE, et donc le problème de stockage des valeurs nulles ne se pose pas.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE NONCLUSTERED INDEX IDX__xxxx  ON dbo.Ma_Table 
(
	Ma_Colonne_Eparse  ASC 
) 
WHERE Ma_Colonne_Eparse IS NOT NULL

Merci pour vos éclaircissements et explications.

**SQLpro** · 23/05/2019, 17h38

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
CREATE TABLE T_TEST_SPARSE_TSP
(ID        INT IDENTITY CONSTRAINT PKS PRIMARY KEY,
 NOM       CHAR(255) SPARSE NULL,
 PRENOM    VARCHAR(100) SPARSE NULL);
GO
 
CREATE TABLE T_TEST_UNSPARSE_TSP
(ID        INT IDENTITY CONSTRAINT PK PRIMARY KEY,
 NOM       CHAR(255) NULL,
 PRENOM    VARCHAR(100) NULL);
GO
 
INSERT INTO T_TEST_UNSPARSE_TSP VALUES ('DUPONT', 'Aymeric-Alexandre')
GO 10000
 
INSERT INTO T_TEST_SPARSE_TSP
SELECT NOM, PRENOM 
FROM   T_TEST_UNSPARSE_TSP
GO
 
UPDATE  T_TEST_SPARSE_TSP SET NOM = NULL    WHERE ABS(CHECKSUM(NEWID())) % 10 = 0
UPDATE  T_TEST_SPARSE_TSP SET PRENOM = NULL WHERE ABS(CHECKSUM(NEWID())) % 10 = 0
 
WITH T AS
(
SELECT ID, ABS(CHECKSUM(NEWID())) % 5 AS N
FROM   T_TEST_SPARSE_TSP
)
UPDATE T_TEST_SPARSE_TSP
SET    NOM = NULL 
WHERE  ID IN (SELECT ID FROM T WHERE N = 1);
 
 
WITH T AS
(
SELECT ID, ABS(CHECKSUM(NEWID())) % 5 AS N
FROM   T_TEST_SPARSE_TSP
)
UPDATE T_TEST_SPARSE_TSP
SET    PRENOM = NULL 
WHERE  ID IN (SELECT ID FROM T WHERE N = 1);
 
EXEC sp_spaceused 'T_TEST_UNSPARSE_TSP'
EXEC sp_spaceused 'T_TEST_SPARSE_TSP'
 
ALTER TABLE T_TEST_SPARSE_TSP REBUILD;
ALTER TABLE T_TEST_UNSPARSE_TSP REBUILD;
 
 
EXEC sp_spaceused 'T_TEST_UNSPARSE_TSP'
EXEC sp_spaceused 'T_TEST_SPARSE_TSP'
 
WITH T AS
(
SELECT ID, ABS(CHECKSUM(NEWID())) % 2 AS N
FROM   T_TEST_SPARSE_TSP
)
UPDATE T_TEST_SPARSE_TSP
SET    NOM = NULL 
WHERE  ID IN (SELECT ID FROM T WHERE N = 1);
 
 
WITH T AS
(
SELECT ID, ABS(CHECKSUM(NEWID())) % 2 AS N
FROM   T_TEST_SPARSE_TSP
)
UPDATE T_TEST_SPARSE_TSP
SET    PRENOM = NULL 
WHERE  ID IN (SELECT ID FROM T WHERE N = 1);
 
 
 
ALTER TABLE T_TEST_SPARSE_TSP REBUILD;
ALTER TABLE T_TEST_UNSPARSE_TSP REBUILD;
 
-- plus de 50% de vide :
 
EXEC sp_spaceused 'T_TEST_UNSPARSE_TSP'
EXEC sp_spaceused 'T_TEST_SPARSE_TSP'
 
/*
name                      rows                 reserved           data               index_size         unused
------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
T_TEST_UNSPARSE_TSP       10000                2952 KB            2864 KB            16 KB              72 KB
 
name                      rows                 reserved           data               index_size         unused
------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
T_TEST_SPARSE_TSP         10000                1416 KB            1320 KB            16 KB              80 KB
*/
 
 
CREATE INDEX XE ON T_TEST_SPARSE_TSP (NOM, PRENOM)
CREATE INDEX X ON T_TEST_UNSPARSE_TSP (NOM, PRENOM)
 
EXEC sp_spaceused 'T_TEST_UNSPARSE_TSP'
EXEC sp_spaceused 'T_TEST_SPARSE_TSP'
 
/*
name                      rows                 reserved           data               index_size         unused
------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
T_TEST_UNSPARSE_TSP       10000                5968 KB            2864 KB            3008 KB            96 KB
 
name                      rows                 reserved           data               index_size         unused
------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
T_TEST_SPARSE_TSP         10000                4304 KB            1320 KB            2888 KB            96 KB

*/

Avec ce test tu verras que l'index est légèrement moins gros en SPARSE qu'à la normale… En effet, le NULL n'élimine pas la référence de ligne. Elle élimine la valeur…. Autrement dit, le SPARSE profite pas mal à la table, mais peu aux index.

Mieux vaut donc faire des index filtrés avec des clauses WHERE... même avec des colonnes SPARSE !

A +

**hmira** · 24/05/2019, 12h05

Bonjour,

Merci beaucoup SQLPro pour cette démonstration implacable. J'ai suivi le scénario de test que tu as indiqué et effectivement au niveau de l'index non Cluster, l'impact en termes du gain de stockage des colonnes éparses, est minime, voire négligeable.

En ce qui concerne le filtre de l'index, je définis des indexes filtrés uniquement si la clé de l'index (ou la première colonne de l'index) porte sur une colonne éparse. Exemple :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE NONCLUSTERED INDEX IDX__xxxx  ON dbo.Ma_Table 
(
	Ma_Colonne_Eparse  ASC 
) 
WHERE Ma_Colonne_Eparse IS NOT NULL

En revanche, lorsque la colonne éparse figure uniquement dans la clause INCLUDE de l'index et que les clés du dit index portent sur d'autres colonnes (généralement non éparses), mes index ne sont pas filtrés sur la colonne éparse figurant dans la clause INCLUDE. Cela, n'aurait pas de sens, puisque la distribution des valeurs de la clé de l'index n'ont rien à voir avec la distribution des valeurs de la colonne éparse figurant uniquement dans la clause INCLUDE ! Ou alors j'ai loupé quelque chose (?).

Pour avoir un pourcentage représentatif des colonnes candidates pour être transformées en colonnes éparses, selon les seuils fournis par Microsoft, j'ai utilisé la fonction TABLESAMPLE (65 PERCENT) avec une CTE pour faire en sorte qu'il y ait au moins 65% (normalement le seuil mentionne 60% suffit pour les char et varchar) de valeurs nulles (NULL), pour chacune des colonne NOM et PRENOM, et ce, dans les deux tables T_TEST_UNPARSE_TSP et T_TEST_SPARSE_TSP

Voilà une variante de ton exemple. Tu me diras que t'en penses

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
CREATE TABLE T_TEST_SPARSE_TSP
(ID        INT IDENTITY CONSTRAINT PKS PRIMARY KEY,
 NOM       CHAR(255) SPARSE NULL,
 PRENOM    VARCHAR(100) SPARSE NULL);
GO
 
CREATE TABLE T_TEST_UNSPARSE_TSP
(ID        INT IDENTITY CONSTRAINT PK PRIMARY KEY,
 NOM       CHAR(255) NULL,
 PRENOM    VARCHAR(100) NULL);
GO
 
INSERT INTO T_TEST_UNSPARSE_TSP 
VALUES ('DUPONT', 'Aymeric-Alexandre')
GO 10000
 
INSERT INTO T_TEST_SPARSE_TSP
SELECT NOM, PRENOM 
FROM   T_TEST_UNSPARSE_TSP; 
GO
 
; WITH CTE_Sparse AS 
( SELECT *
  FROM dbo.T_TEST_SPARSE_TSP TABLESAMPLE (65 PERCENT) ) 
UPDATE  CTE_Sparse 
  SET NOM = NULL; 
 GO 
 
; WITH CTE_Sparse AS 
( SELECT *
  FROM dbo.T_TEST_SPARSE_TSP TABLESAMPLE (65 PERCENT) ) 
UPDATE  CTE_Sparse 
  SET PRENOM = NULL; 
GO   
 
; WITH CTE_UnSparse AS 
( SELECT *
  FROM dbo.T_TEST_UNSPARSE_TSP TABLESAMPLE (65 PERCENT) ) 
UPDATE  CTE_UnSparse 
  SET NOM = NULL ; 
GO 
 
; WITH CTE_UnSparse AS 
( SELECT *
  FROM dbo.T_TEST_UNSPARSE_TSP TABLESAMPLE (65 PERCENT) ) 
UPDATE  CTE_UnSparse 
  SET PRENOM = NULL ;  
 
ALTER TABLE T_TEST_SPARSE_TSP REBUILD;
ALTER TABLE T_TEST_UNSPARSE_TSP REBUILD;
 
EXEC sp_spaceused 'T_TEST_UNSPARSE_TSP'
EXEC sp_spaceused 'T_TEST_SPARSE_TSP'
 
CREATE INDEX XE ON T_TEST_SPARSE_TSP (NOM, PRENOM)
GO 
CREATE INDEX X ON T_TEST_UNSPARSE_TSP (NOM, PRENOM)
GO 
 
ALTER TABLE T_TEST_SPARSE_TSP REBUILD;
GO 
ALTER TABLE T_TEST_UNSPARSE_TSP REBUILD;
GO 
 
EXEC sp_spaceused 'T_TEST_UNSPARSE_TSP'
GO 
EXEC sp_spaceused 'T_TEST_SPARSE_TSP'
GO

Résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
-- Résultat avant la création des indexes (XE et X) et après Rebuild 
name			rows		reserved	data		index_size	unused
T_TEST_UNSPARSE_TSP	10000      	2888 KB		2760 KB		16 KB		112 KB
 
T_TEST_SPARSE_TSP	10000      	1224 KB		1136 KB		16 KB		72 KB 
 
-- Résultat après la création des indexes (XE et X) et après Rebuild 
name			rows		reserved	data		index_size	unused
T_TEST_UNSPARSE_TSP	10000      	5776 KB		2760 KB		2880 KB		136 KB
 
T_TEST_SPARSE_TSP	10000      	4112 KB		1136 KB		2872 KB		104 KB

Encore une fois, Merci beaucoup SQLPro pour tes exemples et explications.

A+

**SQLpro** · 24/05/2019, 12h55

Oulala, je vais pas avoir le temps… J'ai un avion à prendre…. Hier j'ai lancé un exercice compliqué à mes petits stagiaires, c'est pour cela que j'avais le temps de t'envoyer ce script…. Mais hélas aujourd'hui plus de temps…..

Désolé !

A +

**hmira** · 24/05/2019, 13h09

Pas de soucis

A l'occasion, quand tu auras le temps. IL n'y aucune urgence. Les explications que tu as données sont déjà largement suffisantes et m'ont beaucoup éclairé sur le sujet.

Merci et bon voyage !

A+

Stockage des colonnes éparses faisant parties de la clause INCLUDE d'un index

Administration SQL Server

Discussions similaires

Partager

Partager