Le dump d’une page de données SQL Server est plutôt indigeste à lire lorsqu’il s’agit de lire les lignes de données présentes dans la page. Je me suis amusé à regarder s’il était possible de pouvoir déchiffrer cet agglomérat de valeurs hexadécimales. Avec de la documentation et un peu d’entrainement on y arrive et on apprend tout un tas de choses surprenantes.

Tout d’abord il faut savoir que SQL Server utilise une méthode de stockage bien défini pour stocker les différentes données d’une table. Voyons ceci avec un exemple simple. Soit une table dbo.test possédant des colonnes de type INT (fixe), CHAR (fixe) et VARCHAR (variable).

CREATE TABLE test
(
id INT NOT NULL,
col1 CHAR(2) NOT NULL,
col2 VARCHAR(2) NOT NULL
);

INSERT test VALUES (1, ‘TT’, ‘TT’);
GO

Récupérons le DUMP de la page de données allouée à la table test à l’aide des commandes DBCC IND et DBCC PAGE.

DBCC IND (‘Internals’, ‘test’, -1);
GO

 

 

DBCC TRACEON(3604);
GO
DBCC PAGE(‘Internals’, 1, 598523, 1);
GO

 

Voici une représentation hexadécimale de la ligne stockée dans la page concernée. On remarque tout d’abord que chaque ligne est composé de groupes de 4 octets. Pour pouvoir « décoder » ces 2 lignes il faut connaître la structure interne d’une ligne de données. Comme je le disais tout à l’heure SQL Server stocke les données d’une table selon un schéma bien précis qui est le suivant :

On peut déjà faire quelques remarques. SQL Server stocke les colonnes possédant un type de données ayant une longueur fixe au début alors que les colonnes possédant un type de données ayant une longueur variable se situent à la fin de la ligne de données. On remarque également qu’il existe un certain nombre d’octets de gestion supplémentaires. En voici la description :

[Status Bits A] : Cette première série de bits contient les propriétés d’une ligne de données. Les bits qui nous intéressent ici sont les bits 4 et 5 qui indiquent respectivement s’il existe une matrice de gestion des NULL et des données variables pour la ligne concernée.

[Status Bits B] : Un bit qui permet d’indiquer s’il s’agit d’un enregistrement fantôme de pointage. Cela arrive lorsqu’il s’agit d’une table heap (sans cluster). Un enregistrement de pointage fait référence à un enregistrement qui a été déplacé parce qu’il ne pouvait plus être héberger au même endroit du fait de sa taille.

[Longueur de la portion fixe de la ligne de données] : Pour que SQL Server puisse savoir où se termine la partie « fixe » de la ligne, il suffit d’en connaître la longueur. Cette partie n’inclue pas les octets de gestion qui permettent de connaître le nombre de colonnes et la matrice des colonnes pouvant être nuls.

[Données des colonnes fixes] : Cette partie concerne les données de la partie fixe de la ligne. Nous pouvons avoir ici les données de plusieurs colonnes.

[Nombre de colonnes] : Les 2 octets concernés indiquent le nombre de colonnes présentes dans la ligne et par conséquent celles présentes dans la table.

[Matrice des colonnes pouvant être NULL] : Je mets volontairement le mot anglais NULL que tout le monde connaît. Vous comprendrez ici que NULL signifie potentiellement une absence de valeur. Cette matrice répertorie donc les colonnes candidates. Chaque bit de la matrice représente une colonne de la table. En fonction du nombre de colonnes le nombre d’octets alloués peut varier.

[Nombre de colonnes variables] : Ces 2 octets indiquent le nombre de colonnes ayant un type de données variable.

[Tableau des offsets de colonnes variables] : Ce tableau permet de connaître pour chaque donnée variable où elle commence et où elle se termine. Sa taille se calcule de la façon suivante : 2 X nombre de colonnes variables. Par exemple, pour une colonne possédant un type de données variable, nous aurons 2 octets.

[Données des colonnes variables] : « Colonnes variables » est un raccourci que j’emploie pour dire « Colonnes possédant un type de données variables ». Cette partie concerne donc les données de la partie variable de la ligne de données. Nous pouvons également avoir ici les données de plusieurs colonnes.

 

Voici comment on peut transposer ce schéma sur les données réelles trouvées dans le dump de la page 598523 :

 

Pour pouvoir lire correctement cette trame en hexadécimale il faut lire chaque octet de la façon suivante :

Status Bit A :

La valeur hexadécimale est 30. La conversion en binaire donne le résultat suivant : 00110000. Les bits 4 et 5 sont à 1, ce qui signifie que la ligne de données possède des colonnes possédant des types de données variables et des colonnes qui peuvent contenir une absence de valeur (NULL).

Status Bit B :

La valeur hexadécimale est 00. Nous avons une ligne de données classique.

Longueur de la portion des données fixes :

La valeur hexadécimale est 000a. La conversion en décimale donne le résultat suivant : 10. La longueur de cette portion fixe fait donc 10 octets. Elle peut se décomposer de la façon suivante :

1 octet (status bit A) + 1 octet (Status bit B) + 2 octets (longueur fixe) + 4 octets (col id INT) + 2 octets (col col1 CHAR(2))

Données des colonnes fixes :

Colonne id : La valeur hexadécimale est 00 00 00 01. La valeur décimale est 1. Nous retrouvons donc bien notre valeur pour cette colonne.

Colonne col1 : Chaque octet représente caractère non unicode. La valeur hexadécimale de chaque caractère est 54, ce qui correspond en ASCII à la lettre T.

Nombres de colonnes :

La valeur hexadécimale est 0003, ce qui correspond à 3 en décimale. Nous avons bien 3 colonnes pour la table test.

[Matrice de colonnes NULL] : La valeur hexadécimale est 00. La conversion en binaire donne : 00000000. Les 3 premiers bits représentent les 3 colonnes de la table. Les autres octets ne servent pas dans ce cas. Tous les bits sont égales à 0, ce qui signifie qu’aucune colonne ne peut contenir de NULL.

[Nombre de colonnes variables] : La valeur hexadécimale est 00 01, ce qui correspond à 1 en décimal. En effet dans la table test, il n’existe qu’une colonne possédant un type de données variable (col2 (VARCHAR(2)).

[Offset des colonnes variables] : La valeur hexadécimale est 0013.  ce qui correspond à 19. Cette valeur correspond bien à celle de la propriété Record size de la ligne. (Cf. dump de la page plus haut).

[Données des colonnes variables] : Nous pouvons suivre le même raisonnement que pour les données des colonnes fixes car on y retrouve la même valeur hexadécimale (5454).

 

Vous l’aurez compris selon si une ligne de données contient des types de données variables ou fixes, des absences de valeurs ou non, le nombre d’octets supplémentaires de gestion sera plus ou moins important. Attention, ceci n’est pas un raccourci pour dire qu’il est préférable d’utiliser un type de données fixe plutôt qu’un type de données variable. Ceci est un autre débat !! La suite dans un prochain billet

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