Les colonnes fragmentées ou SPARSE sont apparues avec la version 2008 de SQL Server. Celles-ci permettent d’optimiser le stockage pour les tables ayant une majorité de colonnes avec une absence de valeurs (NULL). Cependant ce type de colonne n’est réellement efficace que si une table possède une majorité de valeurs NULL pour les colonnes concernées. Pourquoi une telle condition ? C’est ce que nous verrons dans ce billet.

Commençons par créer 2 tables :

CREATE TABLE T_WITH_NULL
(
col1 INT IDENTITY,
col2 CHAR(5) NOT NULL,
col3 DATETIME NULL,
col4 CHAR(5) NULL
);
GO

CREATE TABLE T_WITH_SPARSE
(
col1 INT IDENTITY,
col2 CHAR(5) NOT NULL,
col3 DATETIME SPARSE,
col4 CHAR(5) SPARSE
);
GO

La première possède 2 colonnes définies à NULL (col3 et col4) et la deuxième possède les mêmes colonnes définies comme colonne fragmentée (SPARSE).

Insérons maintenant un jeu de données pour les 2 tables. On remplit la colonne « col2″ d’une chaine de caractère « TTTTT ». La colonne « col1″ est, quant à elle, alimentée automatiquement via la propriété IDENTITY. Les colonnes « col3″ et « col4″ restent à NULL.

INSERT T_WITH_NULL (col2) VALUES (‘TTTTT’);
GO 100000

INSERT T_WITH_SPARSE (col2) VALUES (‘TTTTT’);
GO 100000

On peut maintenant visualiser le nombre de pages utilisées par chaque table à l’aide des DMV sys.partitions et sys.allocation_units :

SELECT
OBJECT_NAME(P.object_id) AS table_name,
P.rows,
A.type_desc,
A.used_pages
FROM sys.partitions AS P
INNER JOIN sys.allocation_units AS A
ON P.partition_id = A.container_id
WHERE p.object_id IN (OBJECT_ID(‘T_WITH_NULL’), OBJECT_ID(‘T_WITH_SPARSE’));

Le résultat est le suivant :

>> On remarque ici que le fait de définir une colonne comme colonne fragmentée a permis de réduire l’espace de stockage avec 235 pages pour la table T_WITH_SPARSE contre 403 pages pour la table T_WITH_NULL.

Réalisons maintenant le test inverse en remplissant les colonnes définies à NULL ou SPARSE.

CREATE TABLE T2_WITH_NULL
(
col1 INT IDENTITY,
col2 CHAR(5) NOT NULL,
col3 DATETIME NULL,
col4 CHAR(5) NULL
);
GO

CREATE TABLE T2_WITH_SPARSE
(
col1 INT IDENTITY,
col2 CHAR(5) NOT NULL,
col3 DATETIME SPARSE,
col4 CHAR(5) SPARSE
);
GO

INSERT T2_WITH_NULL (col2, col3, col4) VALUES (‘TTTTT’, GETDATE(), ‘TTTTT’);
GO 100000

INSERT T2_WITH_SPARSE (col2, col3, col4) VALUES (‘TTTTT’, GETDATE(), ‘TTTTT’);
GO 100000

SELECT
OBJECT_NAME(P.object_id) AS table_name,
P.rows,
A.type_desc,
A.used_pages
FROM sys.partitions AS P
INNER JOIN sys.allocation_units AS A
ON P.partition_id = A.container_id
WHERE p.object_id IN (OBJECT_ID(‘T2_WITH_NULL’), OBJECT_ID(‘T2_WITH_SPARSE’));

L’espace de stockage des 2 tables est le suivant :

>> Cette fois le nombre de pages requis pour la table avec colonnes fragmentées est plus important que celle qui possède des colonnes définies à NULL.

Comment cela est possible ? Pour le savoir il faut comprendre comment SQL Server stocke ces fameuses colonnes SPARSE dans une page de données. Pour cela nous devons utiliser les commandes DBCC IND et DBCC PAGE. (Dans mon cas les tables ont été créées dans une base nommée TEST).

РR̩cup̩ration des pages de la table T2_WITH_SPARSE
DBCC IND (‘TEST’, ‘T2_WITH_SPARSE’, -1);
GO
– Visualisation d’une page de données de la table T2_WITH_SPARSE
DBCC TRACEON(3604);
GO
DBCC PAGE(‘TEST’, 1, 1179, 3);
GO

Le dump ci-dessus ne comporte que la 1ère ligne de données de la table. La partie qui nous intéresse ici est encadrée en rouge. Celle-ci concerne les colonnes SPARSE pour notre 2ème exemple. SQL Server stocke les colonnes fragmentées sous la forme d’une colonne VARCHAR spéciale représentant un vecteur qui se retrouve à la fin de chaque ligne de données.

Ce vecteur est composé des éléments suivants :

	Nb d’octets	Description
Entête de colonnes complexes	2	Une valeur 05 indique le début d’un vecteur SPARSE
Nb de colonnes SPARSE	2	Â
Ensemble des ID de colonnes	2 X nb colonnes SPARSE	Ensemble des numéros ordinaux des colonnes SPARSE dans la table
Offsets des colonnes	2 X nb colonnes SPARSE	Offset de chaque fin de position de chaque colonne SPARSE
Données SPARSE	En fonction des données de colonnes dans la table	Â

Maintenant que nous avons le descriptif de la composition d’un vecteur sparse, il suffit de transposer ces informations à celles du dump de notre page. Pour la partie avant le vecteur je vous invite à voir ce précédent billet. La partie concernant le vecteur spare donne ceci (valeurs hexadécimales) :

00 05 : Entête du vecteur

00 02 : Nb de colonnes SPARSE : 2  (En décimal égale à 2)

00 03 et 00 04 : colonne numéro 3  et colonne numéro 4. Ceci correspond bien aux colonnes « col3″ et « col4″

00 14 et 00 19 : Offset des colonnes « col3″ et « col4″ (Valeur décimale 20 et 25)

00 00 9D FD 00 A8 1C D0 : Valeur de la colonne « col3″. Une donnée de type DATETIME est composée de 2 entiers (00 00 9D FD et 00 A8 1C D0 qui correspondent à 40445 et 11017242 en décimal. 40445 est le nombre de jours après le 1er janvier 1900 et 11017242 le nombre de ticks après minuit)

54 54 54 54 54 : Valeur de la colonne « col4″. En ASCII la valeur hexadécimale 54 correspond au caractère T.

>> Le vecteur SPARSE a enregistré les informations des colonnes qui possèdent une valeur non nulle. Celui-ci comprend à la fois le numéro de la colonne et sa valeur. Ce vecteur peut donc se représenté sous cette forme : (col2,col3)(valeur col2, valeur col3). Pour les colonnes fragmentées SQL Server est donc obligé de stocker plus d’informations. Par rapport à une table classique composée de colonnes définies à NULL, il existe des octets supplémentaires de gestion (octets d’entête + ensemble des Id de colonnes + offset des données ..), ce qui explique notre résultat de notre second test (Cas où une table avec colonnes SPARSE est remplie de valeurs NON NULL)

A titre de comparaison voici le DUMP d’une page de la table T_WITH_SPARSE qui possède des valeurs NULL pour les colonnes SPARSE :

>> Il n’existe aucune information concernant les colonnes SPARSE dans ce cas. Le vecteur SPARSE n’est pas présent.

Au fait que gagne t’on par rapport à une ligne de données de la table T_WITH_NULL ? Regardons le DUMP d’une page de cette table :

Le DUMP de la page de la table T_WITH_NULL montre que les informations concernant les colonnes « col3″ et « col4″ sont présentes même si celles-ci sont nulles. Ceci est normal car ces 2 colonnes possèdent des données de longueur fixe. Leur longueur est donc constante.

Comme nous venons de le voir, les colonnes SPARSE peuvent grandement aider à l’optimisation du stockage à la condition que celles-ci soient utilisées à bon escient. La mise en place de ce type de colonnes doit être murement réfléchie !!!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM 
MVP SQL Server