[SQL Server] Eléments d’audit d’un fichier de données: état des pages d’allocation

Ce présent billet traite de quelques éléments d’audit relatifs à l’état des pages d’allocation d’un fichier de données. Pour comprendre l’architecture et le fonctionnement des pages d’allocation, référez-vous au billet [SQL Server] Un peu de théorie sur l’architecture d’un fichier de données…

Créons une base de données MaBase[1], ainsi qu’une table appelée MaTable :

USE master
GO
CREATE DATABASE MaBase
GO
USE MaBase
GO
–- Création d'une table de test MaTable
CREATE TABLE MaTable (MaTable_ID INT,MaTable_Desc NVARCHAR(50))
GO
Insérons 5 enregistrements au hasard :
USE MaBase
GO
–- Insertion de 5 enregistrements au sein de MaTable
DECLARE @i INT
SET @i = 0
WHILE @i < 5
BEGIN
     INSERT INTO dbo.MaTable VALUES (@i,'ENREG' + CAST(@i AS VARCHAR(50)));
     SET @i = @i + 2
END
SET @i = 1
WHILE @i < 5
BEGIN
     INSERT INTO dbo.MaTable VALUES (@i,'ENREG' + CAST(@i AS VARCHAR(50)));
     SET @i = @i + 2;
END
GO

Comme évoqué dans le billet [SQL Server] Un peu de théorie sur l’architecture d’un fichier de données..., une page GAM (Global Allocation Map, pour rappel) correspond toujours à la 3ème page (ou PID 2) d’une base de données. Pour voir son contenu, utilisons la commande DBCC PAGE :

DBCC TRACEON(3604)
GO
DBCC PAGE(MaBase, 1, 2, 3)
GO

Voici le résultat de la commande :


Le résultat ci-dessus précise bien que la page auditée est bien une page GAM. On note que tous les extents des pages 0 à 168 sont alloués à l’espace GAM alors que les autres non.

Jetons un coup d’œil aux informations d’une page de type SGAM (Shared Global Allocation Map) qui est, rappelons-le, toujours de PID 3 :

DBCC TRACEON(3604)
GO
DBCC PAGE(MaBase, 1, 3, 3)
GO

Voici le résultat de la commande :


Le résultat ci-dessus est assez similaire à celui de la commande lancée pour la page GAM. On note que l’ensemble des extents des pages 72 à 88 sont alloués à l’espace SGAM, ainsi que ceux des pages 104 à 120.

Par contre, il n’y a pas d’informations concrètes sur le type d’extent (uniforme ou mixte). Pour remédier à cela, il suffit d’utiliser la commande DBCC PAGE sur la page PFS (Page Free Space) qui correspond toujours au PID 1 :

DBCC TRACEON(3604)
GO
DBCC PAGE(MaBase, 1, 1, 3)
GO

Le résultat de la commande donne les informations suivantes :


Ci-dessus, on constate que les extents alloués sont principalement des extents de type mixte (choix par défaut de MSSQL, pour des raisons d’optimisation de stockage). On y trouve également le taux de remplissage des pages. De plus, on peut savoir s’il s’agit d’une page IAM (Index Allocation Map) ou non.

Par exemple, la page 73 est une page pleine à 50% et fait partie d’un extent mixte.

Lançons maintenant la commande DBCC EXTENTINFO :

DBCC EXTENTINFO (MaBase, MaTable, -1)
GO

Voici le résultat de la commande :


On note que la page 73 est utilisée pour héberger des données. Comme nous n’avons inséré 5 lignes de données de taille globale inférieure à 8 Ko (taille standard d’une page), nous n’avons donc qu’une seule page de données hébergeant la table MaTable.

En outre, cette page fait partie d’un extent mixte (ext_size égal à 1, sachant qu’une valeur égale à 8 indique un extent uniforme), ne correspond pas à une table indexée (index_id égal à 0) et ses données sont stockées dans une unité d’allocation de type IN_ROW.

La colonne pfs_bytes « résume » toutes les informations des autres colonnes. Ainsi, la valeur hexadécimale de cette colonne fait référence à l’ensemble des 8 bits de la page PFS qui gère cette page de données. On rappelle (voir section 1.2.4) qu’une page PFS réserve 1 octet (8 bits) pour chaque page qu’elle gère. En convertissant cette valeur hexadécimale 0x6100000000000000 en binaire on obtient les valeurs de chaque bit de l’octet : 01100001. Nous devons interpréter cette valeur de la manière suivante (en partant de la droite) :

  • Bit 0 à 2 : 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%. Dans notre cas, il s’agit de la valeur 001.
  • Bit 3 : Indique si la page contient un enregistrement fantôme (Ghost). La valeur 0 précise que ce n’est pas le cas.
  • Bit 4 : Indique si la page est une page IAM. La valeur 0 indique que ce n’est pas notre cas.
  • Bit 5 : Indique si la page est un extent mixte. La valeur 1 signifie que la page est un extent mixte.
  • Bit 6 : Indique si la page est allouée. La valeur 1 signifie que la page est allouée.
  • Bit 7 : non significatif dans notre cas.
En conclusion, on peut décrire notre page 73 de la manière suivante : il s’agit d’une page allouée faisant partie d’un extent mixte. Elle n’est pas une page IAM et est susceptible d’être remplie à 50%.

Par ailleurs, si l’on se réfère à l’entête d’une page de données (la page 73, par exemple), on note que celle-ci nous donne quelques précisions sur les valeurs d’octets (ou de bits) des pages d’allocation (GAM, SGAM,…).

Exécutons la commande DBCC PAGE sur la page 73 en guise de confirmation :

DBCC TRACEON(3604)
GO
DBCC PAGE(MaBase, 1, 73, 3)
GO

Voici un extrait significatif de son résultat (entête de page) :

Une consultation rapide de l’entête nous indique que la page GAM qui gère la page 73 est la page 3 (PID 2). Cette page indique que la page 73 est allouée. Nous pouvons lire la même chose concernant la page SGAM (PID 3).

On retrouve également la valeur hexadécimale de la page PFS (PID 1) ainsi qu’une description textuelle de l’état de la page 73. La page DCM (PID 6) indique que les données de la page 73 ont changé. Enfin la page BCM (PID 7) indique que la page 73 n’est pas concernée par une opération minimale ou de type BULK_LOGGED.

Regardons désormais, pour la table MaTable, quelle est la page IAM qui la gère en utilisant la commande DBCC IND :

DBCC IND(MaBase,MaTable,-1)
GO

En voici le résultat :


On note ci-dessus que la page IAM qui gère la page 73 est la page 80 (un PageType égal à 10 correspond à une page IAM, sachant qu’une valeur égale à 1 renvoie à une page de données). C’est donc cette page qui gère l’unité d’allocation de la page 73 qui est de type IN_ROW. On peut également connaître la page IAM de la page 73 en consultant la colonne IAMPID (qui, par contre, est toujours nulle pour une page IAM).

Pour vérifier que la page 73 contient bien les données de la table MaTable, il suffit de lancer la commande DBCC PAGE :

DBCC TRACEON(3604)
GO
DBCC PAGE(MaBase, 1, 73, 3)
GO

Puis de regarder la partie DATA du résultat, dont voici un extrait de la 1ère ligne :


On peut, ci-dessus, noter la présence d’ENREG0 ce qui correspond à la 1ère ligne de données de la table MaTable :



[1]Attention : la base étant une base de test pour nos audits, elle sera, ici, créée avec des valeurs par défaut (i.e., filegrowth à 1 Mo pour le fichier de données, 10% pour le fichier de journal de transactions ; chemin d’accès des fichiers par défaut de l’instance ; etc…). En environnement de production, il est recommandé de donner des valeurs plus adaptées.
Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s