Le mode de récupération BULK LOGGED n’est pas le mode le plus utilisé avec SQL Server. Ce mode est pratique lorsqu’il s’agit de journaliser au minimum certains traitements ou certaines opérations de maintenance comme les reconstructions d’index par exemple. Ces opérations ont tendance à augmenter de façon importante la taille du journal des transactions. Dans ce billet j’expliquerai les processus internes lorsque ce mode de récupération est utilisé.

Comme vous le savez certaines opérations peuvent être journalisées au minimum avec le mode de récupération BULK LOGGED. L’avantage est que le journal des transactions héberge beaucoup moins de données que dans les autres modes. De plus les performances de certaines opérations peuvent être améliorées mais ce n’est pas une obligation. J’expliquerais cela un peu plus tard.

Qu’est se passe t’il en interne lorsqu’une opération minimale est exécutée ? Pour l’expliquer prenons l’exemple suivant :

–  Création de la base de données DB_TEST_BULK_LOGGED
CREATE DATABASE DB_TEST_BULK_LOGGED;
GO

USE DB_TEST_BULK_LOGGED;
GO

– Création d’une table test
CREATE TABLE test
(
id INT IDENTITY,
texte VARCHAR(800)
);
GO

– Insertion d’un jeu de données
INSERT INTO dbo.test (texte) VALUES (REPLICATE(‘T’, 800));
GO 100

РOn effectue une premi̬re sauvegarde compl̬te qui servira de r̩f̩rence
BACKUP DATABASE DB_TEST_BULK_LOGGED TO DISK = ‘E:\MSSQL\B\BACKUP\DB_TEST_BULK_LOGGED.BAK’
WITH INIT
GO

РPassage en mode de r̩cup̩ration BULK_LOGGED
ALTER DATABASE DB_TEST_BULK_LOGGED SET RECOVERY BULK_LOGGED;
GO

– Création d’un index cluster pour la table test
РCette op̩ration est journalis̩e au minimum avec le mode
Рde r̩cup̩ration BULK_LOGGED
CREATE CLUSTERED INDEX PK_test
ON dbo.test
(
id
);
GO

A l’aide du script ci-dessus, une base de données nommée DB_TEST_BULK_LOGGED est créée ainsi qu’une table dbo.test. Nous avons ensuite alimenté cette table à l’aide d’un jeu de données et initier une sauvegarde complète qui servira de référence pour les futures sauvegardes. Enfin nous avons basculé la base de données en mode de récupération BULK_LOGGED et créer un index cluster sur la table dbo.test qui est une opération minimale dans ce mode de récupération.

Gestion interne des opérations minimales

Comme je l’ai montré dans ce billet , SQL Server héberge une page de gestion appelée page BCM (Bulk Change Map). Cette page permet de gérer les extensions qui sont concernées par une opération minimale.

Regardons le détail de cette page à l’aide de la commande DBCC PAGE.

DBCC TRACEON(3604);
GO
DBCC PAGE(‘DB_TEST_BULK_LOGGED’, 1, 7, 3);
GO

Le contenu de cette page est le suivant :

Celui-ci nous révèle que 4 extents sont concernés par une opération minimale. (Le 1er extent commence à la page 88, le 2ème extent commence à la page 104, le 3ème à la page 112 et enfin le dernier à la page 192).

On peut également vérifier qu’une des pages d’un extent concerné par une opération minimale est marquée comme tel. Prenons au hasard une page de données de la table dbo.test. Pour cela nous utiliserons la commande DBCC IND.

DBCC IND(‘DB_TEST_BULK_LOGGED’, ‘test’, -1);
GO

qui donne le résultat suivant :

La page 192 fait bien parti de la table dbo.test et est de plus concernée par une opération minimale d’après le contenu de la page BCM.

DBCC PAGE(‘DB_TEST_BULK_LOGGED’, 1, 192, 3);
GO

qui donne le résultat suivant :

L’entête de la page 192 indique bien que celle-ci est concernée par une opération minimale. Voici donc comment SQL Server gère en interne les modifications apportées par une opération minimale.

Gestion au niveau des sauvegardes

Lorsqu’une sauvegarde du journal est effectuée dans le mode de récupération BULK LOGGED, les extents concernés par une opération minimale sont également sauvegardés à l’intérieure de cette sauvegarde. Pour cela SQL Server se sert de la fameuse page BCM que nous avons vu précédemment. Les pages du journal sont copiées à la suite de ces extents. C’est la raison pour laquelle une sauvegarde du journal dans le mode de récupération BULK LOGGED peut avoir parfois une taille importante. Ce facteur dépend bien entendu du nombre d’extents concernés par une ou plusieurs opérations minimales.

Effectuons une sauvegarde du journal des transactions de la base de données DB_TEST_BULK_LOGGED.

BACKUP LOG DB_TEST_BULK_LOGGED TO DISK = ‘E:\MSSQL\B\BACKUP\DB_TEST_BULK_LOGGED.TRN’
WITH INIT;
GO

Processed 40 pages for database ‘DB_TEST_BULK_LOGGED’, file ‘DB_TEST_BULK_LOGGED’ on file 1.
Processed 24 pages for database ‘DB_TEST_BULK_LOGGED’, file ‘DB_TEST_BULK_LOGGED_log’ on file 1.
BACKUP LOG successfully processed 64 pages in 0.206 seconds (2.427 MB/sec).

On observe que la sauvegarde du journal a sauvegardé 40 pages du fichier de données de la base en plus des pages habituelles présentes dans le fichier journal. Ce nombre correspond aux 4 extents recensés par la page BCM + 1 extent qui implique la page BCM elle même. Nous arrivons donc bien à un total de 5 X 8 = 40 pages.

Ecriture des pages de données

Comme je le disais au début, le mode de récupération BULK LOGGED ne garantit pas forcément qu’une opération soit plus rapide que dans un autre mode. En effet, comme l’enregistrement des modifications dans le journal est minime, SQL Server est obligé de forcer les écritures des pages de données au fur et à mesure sur disque. Nous passons donc d’un mode d’écriture asynchrone en passant par CHECKPOINT à un mode d’écriture synchrone pour les pages de données. De plus cela implique des écritures aléatoires alors qu’habituellement celles-ci sont par nature séquentielles (c’est le processus CHECKPOINT qui permet cela en rassemblant les pages de données à écriture et en les écrivant sur disque de manière séquentielle). Ce point peut être important à prendre en compte surtout si le sous système disque hébergeant le(s) fichier(s) de données n’est pas performant.

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