Pour bien commencer cette nouvelle 2010, j’ai envie de commencer par un billet concernant les types de pages que l’on peut rencontrer sur SQL Server. Depuis quelques temps, je m’intéresse beaucoup au fonctionnement interne de SQL Server. Etant amateur de voiture, je ferais une brève analogie. Il n’est pas indispensable de connaître en détail le fonctionnement d’une voiture pour pouvoir la conduire. Cependant si vous êtes passionné ou tout simplement curieux, vous aurez envie un jour ou l’autre de soulever le capot… Il en va de même pour SQL Server. Administrer SQL Server ne demande pas de connaître précisément le comportement interne du moteur. Cependant « maîtriser » le fonctionnement des processus internes du moteur ne peut qu’aider dans la maîtrise et le perfectionnement dans l’administration et la conception des bases de données avec SQL Server. J’en suis intimement convaincu. Dans ce billet je commencerais par aborder le thème suivant : Les types de pages que gèrent SQL Server. Nous verrons par la même occasion les différentes commandes permettant d’en visualiser le détail des pages comme DBCC PAGE, DBCC IND ou DBCC EXTENTINFO .

De quoi est constituée une base de données ? L’exercice peut paraître difficile mais je donnerais ici une réponse (parmi tant d’autres) dans le cadre du présent billet : Une base de données est constitué d’un ensemble contiguë de pages de données qui permettent de gérer l’allocation physique des fichiers de base ainsi que le stockage physique des tables et des indexes. Plusieurs types de pages ayant chacun un rôle bien spécifique existent dans SQL Server : il s’agit des pages GAM, SGAM, IAM, PFS, BCM, DCM, d’index et bien entendu celles qui hébergent les données des objets utilisateurs. Nous commencerons par une explication détaillée pour chaque type de pages :

Pages GAM et SGAM :

L’unité de gestion d’espace de stockage d’une base de données se nomme Extent ou étendue. Une étendue correspond à une collection de 8 pages de données contigües. Cette collection peut être uniforme ou mixte. Une étendue uniforme appartient à un seul et même objet tandis qu’une étendue mixte peut être partagée par plusieurs objets. Lorsqu’une table est créée une étendue mixte est alloué. En fonction de l’augmentation des données d’une table (donc du volume de données qu’elle représente), une extension uniforme peut être allouée. L’allocation première d’une étendue mixte se fait pour des raisons d’optimisation de stockage (Une table n’occupant pas forcement l’espace totale allouée par une étendue uniforme).

Pour connaître les étendues allouées, les types utilisés et les pages de données inutilisées, SQL Server se sert de 2 types de pages : GAM (Global Allocation Map) et SGAM (Shared Global Allocation Map).

GAM : Ces pages indiquent les étendues allouées. Celle-ci possède un bit de données pour chaque étendue qu’elle gère. Une valeur de bit à 0 correspond à une étendue allouée et une valeur de bit à 1 correspond à une étendue libre. La taille d’une page GAM étant de 8Ko celle-ci peut donc gérer 64000 étendues (8000 * 8 = 64000 bits) soit un espace total de 4GB de données (64000 * 64Ko = 4GB). SQL Server peut retrouver rapidement une page GAM car celle-ci correspond toujours à la 3ème page (page ID 2) d’un fichier d’une base de données. On retrouve une page GAM toutes les 511230 pages après la 1ère page GAM.

SGAM : Ces pages indiquent les étendues utilisées et le mode d’utilisation (mixte ou uniforme) et qui possèdent au moins une page de données inutilisée. Tout comme les pages GAM, une page SGAM peut gérée 64000 étendues pour un espace total de données de 4GB. Chaque étendue correspond à un bit de données d’une page SGAM. La valeur d’un bit à 0 correspond à une étendue mixte avec une page de données inutilisée et la valeur d’un bit à 1 correspond à une étendue uniforme ou une étendue mixte avec toutes les pages de données utilisées. Une page SGAM correspond toujours à la 4ème page (page ID 3) d’un fichier d’une base de données. On retrouve une page SGAM toutes les 511230 pages après la 1ère page SGAM.

Pages IAM :

Ces pages indiquent quelles étendues dans un fichier d’une base de données et dans un espace de 4Go (appelé espace GAM) appartiennent à une unité d’allocation. Une unité d’allocation est un ensemble de pages hébergeant un type particulier de lignes de données. Il peut y avoir 3 types : IN-ROW (pour les lignes de donnée classiques), ROW-OVERFLOW (pour les lignes de donnée dépassant 8060 octets) et LOB (pour lignes contenant des objets LOB). Une table HEAP ou un index cluster possède par défaut au moins une partition avec une unité d’allocation de type IN-ROW. Une base de données faisant plus de 4Go possédera plusieurs pages IAM (une pour chaque espace de 4Go supplémentaire). De la même manière si une table est partitionnée, celle-ci sera gérée par plusieurs pages IAM (une pour chaque partition). Chaque page IAM peut gérer un intervalle de 512000 pages. (64000 étendues * 8 = 512000 pages). Comme il existe peu de pages IAM dans une base de données d’une part et que celles-ci sont liées entre elles par une liste chaînée d’autre part, elles ont de grandes chances de se trouver en mémoire ce qui permet à SQL Server de les retrouver rapidement.

Les pages de données d’une table HEAP ne sont pas liées comme pour un index cluster. Par conséquent, le seul moyen pour SQL Server de connaître l’ensemble des pages allouées dans ce cas précis est de lire l’ensemble des pages IAM allouées à cette table.

Exemple table1 partitionné sur 2 fichiers physiques

 

Page PFS :

Ces pages indiquent la quantité d’espace libre restante dans une page de données. A la différence des autres types de page, une page PFS associe un ensemble de 8 bits à une page de données dans un intervalle de 8088 pages (64Mo) d’un fichier. Ces pages sont utilisées lors des opérations de mise à jour dans une table HEAP par exemple. Dans le cas d’une insertion, SQL Server localise les espaces libres pour placer les nouvelles données et dans le cas d’une mise à jour, cela permet à SQL Server de déplacer une ligne de données si celle-ci possède une taille plus importante. Une page PFS correspond toujours à la 2ème page (page ID 1) d’un fichier de base de données et on retrouve une page PFS tous les 8088 pages.

Pages DCM :

Ces pages indiquent qu’une étendue a été modifié depuis la dernière sauvegarde complète. Elles sont utilisées dans le cadre des sauvegardes différentielles. SQL Server interroge alors les pages DCM pour savoir quelles étendues doivent être sauvegardées depuis la dernière sauvegarde complète. Tout comme les pages de type GAM et SGAM, une page DCM peut gérer 64000 étendues. Une valeur de bit à 1 signifie qu’une étendue a été modifié. Ces valeurs de bit sont remis à 0 lors d’une sauvegarde complète. Une page DCM correspond toujours à la 7ème page (page ID 6) d’un fichier et on les retrouve toutes les 511230 pages si l’étendue de stockage est supérieure à 4Go.

Pages BCM :

Ces pages indiquent qu’une page de données est issue d’une étendue concernée par une mise à jour minimale ou par une opération de type BULKED LOGGED. Tout comme les pages de type GAM et SGAM, une page BCM peut gérer 64000 étendues. Une valeur de bit à 1 signifie qu’une étendue a été modifié avec un enregistrement minimal dans le journal depuis la dernière sauvegarde du journal des transactions. Cette valeur de bit est remise à 0 lors d’une sauvegarde du journal. Une page BCM correspond toujours la 8ème page (page ID 7) d’un fichier et on les retrouve toutes les 511230 pages si l’étendue de stockage est supérieure à 4Go.

Pages de données :

Les pages de données comme leur nom l’indiquent contiennent les données des tables ou index. Une page de données a une taille fixe de 8192 octets ou 8Ko. Elle comporte une entête, des lignes de données et des offset de lignes permettant à SQL Server de retrouver rapidement où se situe une ligne de données dans la page. L’entête occupe les 96 premiers octets de la page laissant 8096 octets pour les lignes de données et les offsets de ligne. Je reviendrais en détail sur ces types de page lors d’un prochain billet sur le stockage interne des tables de données.

Pages d’entrées d’index:

Les pages d’index peuvent appartenir à un index cluster ou non cluster. Je ne rentrerais pas plus dans le détail dans ce billet.

Vue d’ensemble d’un fichier de base de données

 

Visualiser le contenu des pages dans SQL Server

SQL Server propose quelques commandes non documentées pour visualiser le contenu des pages. J’insiste sur le fait que ces procédures sont non documentées et que par conséquent celles-ci seront utilisées dans un but purement informatif afin de comprendre les propos de ce billet. Ces commandes sont DBCC PAGE, DBCC IND et DCC EXTENTINFO.

DBCC PAGE

Cette commande permet de visualiser le contenu de n’importe quelle page à savoir son entête, ses lignes de données et ses offsets de lignes. La syntaxe est la suivante :

DBCC PAGE (dbid | dbname, filenum, pagenum, printopt)

- dbid ou dbname : L’id ou le nom de la base de données.
- filenum : Numéro de fichier de la base de données.
- pagenum : Numéro de la page à visualiser.
- printopt : Ce paramètre est optionnel et peut prendre 4 valeurs :
    0 : Par défaut. Affiche l’entête du buffer et l’entête de la page.
    1 : Affiche l’entête du buffer, l’entête de la page et une ligne pour chaque ligne de données et une ligne pour les offsets.
    2 : Affiche l’ensemble des données du buffer et la page ainsi que les offsets de ligne.
    3 : Affiche l’entête du cache, l’entête de la page, chaque ligne séparément et les offsets de ligne. De plus chaque ligne est suivi par leurs valeurs de colonnes listées séparément.

Cette commande doit être utilisée conjointement avec l’indicateur de trace 3604. Dans le cas contraire aucune information n’est retournée au client. Nous utiliserons l’option 3 pour nos tests.

DBCC IND

Cette commande permet de visualiser les pages utilisées par un index d’une table. La syntaxe est la suivante :

DBCC IND (dbid | dbname, objetid |objetname, printopt)

- dbid ou dbname : Numéro ou nom de la base de données.
- objetid ou objetname : Numéro ou nom de l’objet
- printopt : Numéro de l’index concerné. (-1 tous les index et -2 seulement les pages IAM)

DBCC EXTENTINFO

La commande DBCC EXTENTINFO fournit des informations intéressantes concernant l’allocation des pages et des étendues pour un objet donné. Pour une explication de la commande je vous invite à regarder un de mes précédents billets.

 

Cas d’étude

Voyons dans un cas simple l’organisation des types de pages décrites précédemment. Le script suivant permet de créer une base de données et une table de test avec un jeu de données.

CREATE DATABASE internal_database;
GO

USE internal_database;
GO

CREATE TABLE dbo.test1
( 
test VARCHAR(8000) NOT NULL
);
GO

INSERT INTO dbo.test1 VALUES(REPLICATE(‘D’,8000));
GO

Tout d’abord, comme nous l’avons vu une page GAM est toujours la page 3 (ou PID 2) d’une base de données. Pour voir son contenu nous allons utiliser la commande DBCC PAGE :

DBCC TRACEON(3604);
GO
DBCC PAGE (internal_database, 1, 2, 3);
GO

Le résultat est le suivant :

La lecture du résultat de la commande DBCC PAGE indique que cette page est bien une page GAM. On remarque également que toutes les étendues concernant les pages 0 à 168 sont allouées (ce qui correspond à un espace d’environ 1,3 Mo et 21 étendues) alors que les suivantes ne le sont pas.

Voyons ensuite ce que donne le résultat de la commande DBCC PAGE sur une page de type SGAM :

DBCC TRACEON(3604);
GO
DBCC PAGE (internal_database, 1, 3, 3);
GO

Le résultat est le suivant :

La sortie de la commande DBCC est assez similaire. On constate que l’ensemble des étendues concernant les pages 72 à 88 sont allouées ainsi que les pages 104 à 120. Mais cette commande ne nous donne pas d’information précise concernant le type d’étendue en place (mixe ou uniforme). Pour cela nous utiliserons conjointement la commande DBCC PAGE sur une page PFS et la commande DBCC EXTENTINFO.

DBCC TRACEON(3604);
GO
DBCC PAGE (internal_database, 1, 1, 3);
GO

Le résultat est le suivant :

.

La sortie de la commande DBCC PAGE sur une page PFS est intéressante. Elle nous fournit des informations concernant le taux de remplissage d’une page, l’appartenance à une étendue mixte ou uniforme et si celle-ci est une page IAM. La page 55 dans notre cas est pleine et fait partie d’une étendue mixte.

DBCC EXTENTINFO (internal_database, test1, -1);

Le résultat est le suivant :

La sortie de cette commande nous fait remarquer qu’une page est allouée pour héberger la ligne de donnée de la table dbo.test1. Comme nous n’avons inséré qu’une ligne de données de 8000 octets nous n’avons qu’une seule page de données hébergeant la table dbo.test1. Nous avons vu précédemment que cette page était pleine. Ceci s’explique par le fait que la colonne test de cette table est de type varchar(8000) et que nous avons inséré une ligne de 8000 caractères ‘D’. Cette page faisait partie d’une étendue mixte. On le constate ici avec la valeur de colonne ext_size. Une valeur de 1 pour cette colonne signifie que la page fait parti d’une étendue mixte alors qu’une valeur de 8 signifie que l »Ã©tendue hôte est uniforme. La colonne pfs_bytes nous informe également sur l’ensemble de ces informations. En effet, la valeur hexadécimale de cette colonne correspond en fait à l’ensemble des 8 bits de la page PFS qui gère cette page de données. Pour rappel une page PFS réserve 1 octet (8 bits) pour chaque page qu’elle gère. En convertissant cette valeur hexadécimale 0x6400000000000000 en binaire on obtient les valeurs de chaque bit de l’octet : 01100100. Nous devons interpréter cette valeur de la manière suivante :

Bit 0 : non significatif dans notre cas.
Bit 1 : Indique si la page est allouée. La valeur 1 signifie que la page est allouée.
Bit 2 : Indique si la page est un étendue mixte. La valeur 1 signifie que la page est un étendue mixte.
Bit 3 : Indique si la page est une page IAM. La valeur 1 signifie que l’on a à faire à une page IAM.
Bit 4 : Indique si la page contient un enregistrement fantôme. Pour plus de détails concernant les enregistrements fantômes référez vous à un de mes précédents billets.
Bit 5 à 7 : Ces 3 bits représentent le taux de remplissage de la page. La valeur binaire 000 signifie que la page est pleine est vie. La valeur binaire 001 signifie que la page est pleine de 1 à 50%. La valeur 010 binaire signifie que la page est pleine de 51 à 80%. La valeur binaire 011 signifie que la page est pleine de 81 à 95%. Enfin la valeur binaire 100 signifie que la page est pleine de 96 à 100%.

En se référant à cette interprétation, on peut en déduire que la page 55 est allouée, qu’elle fait partie d’une étendue mixte, qu’elle n’est pas une page IAM et qu’elle est susceptible d’être remplie à 100%. Dans notre cas, cela s’avère tout à fait exact.

Si l’on regarde l’entête d’une page de données, on remarque que celle-ci nous informe également sur les valeurs des bits ou octets des pages hôtes GAM, SGAM, IAM, DCM, BCM et PFS.

DBCC TRACEON(3604);
GO
DBCC PAGE (internal_database, 1, 55, 3);
GO

Le résultat est le suivant :

Une simple lecture de l’entête nous indique que la page GAM qui gère la page 55 est la page 3 (PID 2). Cette page indique que la page 55 est allouée. Nous pouvons lire la même chose concernant la page SGAM (PID 3). On retrouve la valeur hexadécimale de la page PFS (PID 1) ainsi qu’une description textuelle de l’état de la page 55. La page DCM (PID 6) indique que les données de la page 55 ont changé. Enfin la page BCM (PID 7) indique que la page 55 n’est pas concernée par une opération minimale ou de type BULK_LOGGED.

Pour finir regardons pour notre table test1 quelle est la page IAM qui la gère en utilisant la commande DBCC IND.

DBCC IND(‘internal_database’,’test1′,-1);
GO

et son résultat :

Nous pouvons dire que la page 55 (PagePID = 55) est une bien une page de données (PageType = 1 correspond à une page de données). La page 77 (PagePID = 77), quant à elle, est une page IAM (PageType =10 correspond à une page IAM). C’est donc cette page qui permet de gérer l’unité d’allocation concernant la table dbo.test1. On connaît également le numéro de la page IAM qui mappe la page 55 grâce à la valeur de la colonne IAMPID (77). Pour une page IAM cette valeur est nulle.

Pour vérifier que la page 55 correspond bien les données de notre table il suffit de regarder le détail de cette page :

DBCC TRACEON(3604);
GO
DBCC PAGE (‘internal_database’, 1, 55, 3);
GO

Le résultat est le suivant :

On retrouve bien les caractères ‘D’ insérés dans la table dbo.test1.

Voilà pour ce qui est de ce 1er billet de l’année 2010. Je continuerais tout à long de cette année une série de billets sur ce thème. J’espère que vous aurez apprécié. Vous pouvez vous même testé le comportement du moteur en créant par exemple des indexes ou en rajouter d’autres lignes à la table dbo.test1.

Bon test et bonne année !!

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