SQL Server vu par Christian Robert

SQL Server : C’est quoi une page ? Ou la structure interne des données stockées par SQL Server

Que faire quand vous avez des insomnies à 3h du mat à l'hôtel (enfin ici à Denver) et bien un petit topo sur les « Internals » de SQL Server.

Alors qu'est ce qu'un page ?

Une page c'est l'unité de stockage des données, SQL Server la rend fixe en taille (contrairement à d'autres moteur de bases de données, tel que Oracle qui permet d'en définir la taille, notez que le nom n'est pas le même sous ce moteur), elle fait 8ko. Basiquement SQL Server ne peut jamais traiter moins d'une page. Il en découle que le nombre de page traitées par une requête est très important pour nous. Les pages sont stockées dans les fichiers de données (extensions MDF et NDF par défaut).

Que contient une page ?

Il existe 3 grands types de page : les pages systèmes (IAM, PFS, GAM, SGAM, Diff Bitmap, etc.), les pages contenant des données de tables et les pages contenant les éléments d'index. Je ne reviendrais pas sur le premier type qui permet de suivre l'usage des autres pages. En ce qui concerne les données et les index, sachez que SQL Server ne stockera que des données d'une même table dans une page, ou des éléments d'un seul index. Jamais vous ne trouverez dans une même page les données de la table X et de la table Y.

Qu'elle est sa structure ?

Chaque page dispose d'un entête de 96 octets (je reviendrais plus bas sur son contenu). La partie libre de la page contient les enregistrements ou les éléments d'index (traités ici de la même manière par le moteur). En fin de page on trouve un bloc d'offset, ce dernier permet de connaitre l'emplacement des enregistrements ou des éléments d'index dans la page, chaque entrée dans ce bloc définie le début d'un enregistrement dans la page.

La structure de l'entête

L'entête contient beaucoup d'informations importantes, sans être exhaustif voici une petite liste :

• Le type de la page
  • Index / Données, système, etc.
• Le numéro de la page suivante
  • Utilisé par le moteur lors d'un scan d'un index il peut dès lors suivre l'ordre logique de ce dernier et permettre des tris plus aisés ou des recherches sur des plages de valeur
• Le numéro de la page précédente
  • Idem au précédent
• Le numéro de l'unité d'allocation dont ont fait partie (SQL Server 2005) ou le nom de l'objet dont ont fait partie (SQL Server 7 et 2000)
  • Directement (7 et 2000) ou indirectement (2005) définie l'objet (table ou index) auquel appartient la page
• Le CheckSum ou TornPage bits de la page
  • Valeur permettant de vérifier la page lors des lectures et des écritures. Le contenu est fonction de l'option PAGE_VERIFY de la base de données (2005), ou le l'option « Torn Page Detection »  (2000 et 7)
• Le numéro de la dernière transaction ayant modifiée la page
  • Le numéro de la dernière transaction (numéro physique dans le journal aussi appelé LSN) ayant modifié la page.
• Et d'autres infos…

Comment lire une page ?

On s'en sert généralement qu'à titre de débogage. C'est la commande non documentée DBCC PAGE qui permet de lire une page dans le fichier de données.

Exemple :

-- Pour afficher le retour de DBCC Page dans la fenêtre de message

DBCC TRACEON(3604)

-- Lecture de la page 141 de la base Master

DBCC PAGE(1, 1, 141, 3)

On fournit dans l'ordre à DBCC Page : le numéro de la base de données, le numéro du fichier, le numéro de la page. Pour finir le détail souhaité (0 à 3, 3 étant le plus détaillé et listant entre autres les enregistrements).

Bonnes structures internes…

(Je ferais une suite sur la structure des enregsitrements)