Ce billet est purement informatif. Je vous propose de vous expliquer brièvement la la façon dont le moteur SQL alloue les pages de données lors d’une insertion d’enregistrements dans une table. Comme vous le savez sans doute une base de données est une collection de pages de 8 Ko réparties sur un ou plusieurs fichiers physiques. Une page peut donc contenir plusieurs lignes d’une table selon le cas . Les commandes DBCC EXTENTINFO et sp_spacused nous aideront à comprendre ce mécanisme d’allocation.

Commençons par créer notre table :

CREATE TABLE test
(
texte VARCHAR(4000) NOT NULL
);
GO

J’ai volontairement choisi une colonne ayant une taille conséquente pour le test. Insérons maintenant nos données (33 lignes de données) :

WHILE @i < 33
BEGIN
    INSERT INTO dbo.test VALUES (REPLICATE(‘T’,4000));
    SET @i = @i +1
END
GO

Regardons maintenant la taille de notre table avec la commande sp_spaceused :

sp_spaceused test;
GO

et le résultat correspondant :

name       |      row     |     reserved     |      data       |      index_size    |     unused
————————————————————————————
test          |      33       |     200 KB         |      136 KB   |          8K               |      56KB

 

On retrouve bien nos 33 lignes de données.

Intéressons nous dans un premier temps à la colonne data. Nous avons insérés 33 lignes de données d’une taille de 4000 octects chacune. Une page faisant 8Ko ou 8192 octets, celle-ci pourra contenir 2 lignes de la table test. Il faudra donc au total 17 pages pour contenir l’ensemble des données de notre table (33 / 2 = 16.5 soit 17 pages). La colonne data nous le confirme (17 * 8Ko = 136 Ko).

Regardons maintenant la colonne reserved. Pour expliquer ce résultat utilisons la commande DBCC EXTENTINFO. Cette commande non documentée nous donne des informations intéressantes sur les pages et extensions d’une base, d’une table ou d’un index. Cette commande peut prendre en paramètre le nom ou l’id d’une base de données, le nom ou l’id d’une table et le nom ou l’id d’un index : DBCC EXTENTINFO (‘base’,’table’,index). Les paramètres d’entrées sont optionnelles. Exécutons cette commande pour voir les informations d’allocations concernant notre table test :

DECLARE @db_name SYSNAME;
DECLARE @tb_name SYSNAME;

SET @db_name = N’db_test';
SET @tb_name = N’test';

DBCC EXTENTINFO(@db_name,@tb_name,-1);
GO

et le résultat correspondant (Le résultat est simplifié pour ne laisser que les colonnes qui nous intéressent) :

Nous avons bien 17 pages allouées pour nos données. La colonne pg_alloc indique le nombre de pages allouées et utilisées.

La colonne ext_size indique, quant à elle, le nombre de pages allouées mais pas forcement utilisées. La dernière ligne du résultat nous le montre (page_id = 328). Il ya 8 pages allouées et une seule est utilisée pour le moment. SQL Server travaille avec des Extents soit 8 pages de données. C’est la raison pour laquelle SQL Server alloue directement un espace pour 8 pages de données. Partant de cette affirmation une question se pose légitimement : Si SQL Server travaille avec des Extents alors pourquoi voyons nous ici que les 8 premières lignes concernent seulement des pages et non des Extents ? En fait SQL Server alloue les 8 premières pages sans Extent pour ne pas gaspiller de place si l’on se retrouve dans le cas de petites tables.

Le résultat de DBCC EXTENTINFO nous révèle donc que le moteur SQL a réservé un total de 24 pages soit 192Ko. La colonne reserved du résultat de la commande sp_spaceused indique pourtant une réservation totale de 200Ko. Il faut soustraire à ce total l’espace réservé aux index pour retrouver notre résultat (200 – 8 = 192Ko).

Il nous reste enfin à expliquer la taille de l’espace inutilisé qui est la valeur de la colonne unused du résultat de la commande sp_spaceused. SQL Server a donc aune taille totale de 192Ko pour nos données mais nous avons vu que seulement 136Ko étaient utilisés (17 pages de 8 Ko). Il suffit de soustraire la taille totale des pages de données utilisées à celle des pages réservées et on retrouve le résultat de la colonne unused. (192 – 136 = 56 Ko).

Voila j’espère vous avoir éclairé sur le mécanisme d’allocation de pages contenant les données de vos tables !!

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