En utilisant la vue dynamique système sys.dm_db_index_physical_stats, vous avez sans doute remarqué la présence d’une colonne ghost_record_count que je traduirais par nombre d’enregistrement fantômes. Cette colonne est incrémentée à chaque fois qu’un enregistrement est supprimé d’une table comportant au moins un index.

Qu’est-ce qu’un enregistrement fantôme ? C’est un enregistrement qui a été supprimé logiquement d’une page d’un index d’une table. Lors d’une opération de suppression à l’aide d’une commande TSQL DELETE par exemple, on pourrait penser qu’une donnée qui est supprimée d’une page d’index l’est physiquement mais il n’en est rien. Un bit est positionné sur l’enregistrement concerné. La page de données où il se situe est marqué comme possédant un enregistrement fantôme (ou ghost records) ainsi que l’entête de la page PFS associée à la page d’index. Enfin l‘état de la base de données hôte est également mis à jour et celle-ci indique qu’elle possède un enregistrements fantôme.

Note: Une page PFS permet à SQL Server de savoir quelles pages de données sont soit allouées soit libres et leur pourcentage d’espace libre.

Par la suite une tâche d’arrière plan nommée ghost cleanup va supprimer physiquement ces enregistrements. Elle se déclenche toutes les 5 secondes environ, vérifie à chaque passage l’ensemble des bases de données marquées comme possédant des enregistrements fantômes, lit les pages PFS et supprime enfin les enregistrements fantômes des pages de données concernées. Un nombre limité de pages est nettoyé à chaque passage de la tâche ghost cleanup pour ne pas perturber le système. Une base qui ne possède pas d’enregistrements fantômes au moment de l’exécution de la tâche Ghost Cleanup ne sera pas prise en compte à sa prochaine exécution .

Le schéma ci-dessous résume et illustre le fonctionnement de la tâche ghost cleanup après la suppression d’un enregistrement.

 

 

Nous allons maintenant détailler chaque étape de suppression d’un enregistrement en prenant un exemple. Pour cela commençons par créer une table de test, un index cluster sur la colonne id et un jeu de données :

USE tempdb;
GO

CREATE TABLE dbo.test
(
id INT IDENTITY(1,1) NOT NULL,
nom VARCHAR(20) NOT NULL
);
GO

CREATE CLUSTERED INDEX PK_test
ON dbo.test
(
id
);
GO

INSERT dbo.test (nom) VALUES (‘David’)
INSERT dbo.test (nom) VALUES (‘mikedavem’);
GO

Le jeu de données est donc le suivant :

 

Etape 1 : Suppression de l’enregistrement « mikedavem »

On commence par initier une transaction nommée TRAN_GHOSTRECORD et on supprimer l’enregistrement « mikedavem »

BEGIN TRAN TRAN_GHOSTRECORD

DELETE FROM dbo.test
WHERE id = 2;
GO

On peut maintenant voir apparaître un enregistrement fantôme en regardant l’état de l’index cluster à l’aide de la procédure sys.dm_db_index_physical_stats.

SELECT
    DB_NAME(database_id) AS [db_name],
    OBJECT_NAME(object_id) AS [object_name],
    index_id,
    index_type_desc,
    page_count,
    record_count,
    ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(‘test’),NULL,NULL,’DETAILED’);
GO

Le résultat est le suivant :

On constate que l’index cluster de la table test est constitué d’une page, d’un enregistrement de données et d’un enregistrement fantôme, ce qui paraît logique puisque l’on vient de supprimer l’enregistrement « mikedavem ». Nous ne devons visualiser qu’un seul résultat dans la table test. Vérifions le :

SELECT * FROM dbo.test;

qui donne le résultat suivant :

Pour rappel, un enregistrement fantôme ne peut être lu et ceci même en mode de transaction READ UNCOMMITED ou avec l’option NOLOCK.

Qu’est devenu la page de données ? Est-elle marquée comme possédant un enregistrement fantôme ? Pour répondre à cette question nous utiliserons 2 procédures stockées systèmes non documentées : DBCC IND qui permet de lire les pages d’un index et DBCC PAGE qui permet de lire le contenu détaillé d’une page spécifique. Recherchons d’abord la page qui nous intéresse dans l’index cluster de la table test.

DBCC IND(‘tempdb’,’test’,1)
GO

qui nous donne :

C’est la ligne 2 qui nous intéresse ici. En effet, le type de page correspondant est égale à 1 ce qui signifie que nous sommes en présence d’une page de données. La première ligne correspond à une page IAM (type de page est égale à 10) qui indique quelles pages appartiennent à un objet. C’est la raison pour laquelle la page de données (PagePID 175) est référencée par la page IAM (PagePID 176).

Regardons maintenant en détail les informations de la page de données ayant le PID 175. Pour cela on doit activer le flag de trace 3604 pour pouvoir visualiser le résultat de la sortie de la commande DBCC PAGE.

DBCC TRACEON(3604);
GO

DBCC PAGE (‘tempdb’, 1, 175, 3);
GO

qui nous donne :

 

La page de données a bien été marqué comme possédant un enregistrement fantôme et l’enregistrement concerné est bien « mikedavem ».

 

Etape 2 : Marquage de l’entête de la page PFS associée à la page de données

Dans cette 2ème étape il s’agit de vérifier si l’entête de la page PFS a été marqué. Pour cela nous devons lire le journal des transactions à l’aide de la fonction système fn_dblog().

Visualisons dans un 1er temps l’opération de suppression de l’enregistrement « mikedavem » dans le journal :

SELECT *
FROM fn_dblog(NULL,NULL)
WHERE [Transaction ID] = (SELECT [Transaction ID]
                          FROM fn_dblog(NULL,NULL)
                          WHERE [Transaction Name] LIKE ‘%TRAN_GHOSTRECORD%’);

avec le résultat suivant :

 

 

Regardons maintenant les opérations qui ont été exécuté sur la page PFS associée à la page de données :

SELECT
    Description,
    AllocUnitName,
    *
FROM fn_dblog(NULL,NULL)
WHERE AllocUnitName LIKE ‘%test%’
AND Context LIKE ‘%PFS%';
GO

et son résultat :

On voit ici que a page PFS associée à l’index cluster de la table test a également été modifié et mis à jour (LOP_MODIFY_ROW et LOP_SET_BITS).

Etape 3 : La tâche Ghost Cleanup s’exécute

La tâche Ghost Cleanup s’exécute en arrière plan. On peut vérifier son existence en utilisant le script TSQL suivant qui se base sur la vue système sys.dm_exec_request :

IF EXISTS(SELECT * FROM tempdb.sys.sysobjects WHERE name LIKE ‘%#temp_exec_requests%’)
DROP TABLE #temp_exec_requests;
GO
SELECT TOP 0 * INTO #temp_exec_requests FROM sys.dm_exec_requests;
GO

DECLARE @i INT;
SET @i = 0;

WHILE @i < 1
BEGIN
INSERT INTO #temp_exec_requests
SELECT * FROM sys.dm_exec_requests WHERE command LIKE ‘%ghost%';
SELECT @i = COUNT(*) FROM #temp_exec_requests;
END

SELECT GETDATE(), * FROM #temp_exec_requests; 
DROP TABLE  #temp_exec_requests;

et son résultat :

En lançant plusieurs fois ce script on voit que la tâche Ghost Cleanup s’exécute toutes les 5 secondes environ. Pour le moment cette tâche ne pourra pas supprimer l’enregistrement « mikedavem » car la transaction TRAN_GHOSTRECORD est toujours ouverte.

Etape 4 : La tâche Ghost Cleanup, après avoir détecté l’enregistrement peut maintenant le supprimer

Enregistrons tout d’abord le dernier LSN du journal des transactions. Celui-ci nous servira pour récupérer les futurs enregistrements du journal suite à la validation de la transaction TRAN_GHOSTRECORD qui est encore en cours :

SELECT MAX([Current LSN])
FROM fn_dblog(NULL,NULL);

– Chez moi : 00000017:00000087:0008

Ensuite validons cette transaction pour que la tâche Ghost Cleanup puisse supprimer l’enregistrement fantôme « mikedavem » :

COMMIT TRAN TRAN_GHOSTRECORD;

Regardons maintenant les transactions inscrites dans le journal :

SELECT
    [Page ID],
    Operation,
    AllocUnitName,
    *
FROM fn_dblog(NULL,NULL)
WHERE [Current LSN] > ‘00000017:00000087:0008′;
GO

et son résultat :

On visualise trois transactions importantes : La validation de la transaction (LOP_COMMIT_XACT), la purge de l’enregistrement fantôme dans l’index cluster (LOP_EXPUNGE_ROWS) et la mise à jour de la page PFS associée (LOP_SET_BITS) car la page de données ne contient plus aucun enregistrement fantôme.

Qu’est devenu la page de données ?

DBCC TRACEON(3604);
GO

DBCC PAGE (‘ghost’, 1, 78, 2);
GO

et son résultat :

La page de données n’est plus marquée comme possédant un enregistrement fantôme. On peut également noter que l’enregistrement « mikedavem » est toujours présent dans la page de données. Pourquoi ? En fait la suppression de l’enregistrement par la tâche Ghost Cleanup efface le slot associé à l’enregistrement en fin de page. Lorsque ce slot sera de nouveau utilisé, l’enregistrement « mikedavem » sera ecrasé par la réécriture d’un futur enregistrement.

Pourquoi implémenter un tel système pour supprimer des données d’un index ? Quels en sont les avantages ? Le nettoyage physique d’un enregistrement à chaque opération de suppression pourrait peser sur les performances. Les enregistrements fantômes et l’exécution asynchrone en arrière plan de la tâche Ghost Cleanup simplifient la gestion des verrous de clés (Key-Range lock) et les annulations de transactions (ROLLBACK) car il suffit dans ce cas de repositionner un bit de données à la bonne valeur.

Voilà en ce qui concerne les enregistrements fantômes. Bonne lecture !!!

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