Faire une sauvegarde de bases de donnée est une opération plus que courante pour un administrateur de bases de données. Tout DBA digne de ce nom connaît l’instruction qui permet de sauvegarder une base de données BACKUP DATABASE … TO … Cependant on connaît moins ce qui se passe en arrière plan lorsqu’une telle commande est lancée. Comprendre les processus sous jacents peuvent aider à la résolution de certains problèmes de sauvegarde que l’on peut rencontrer dans certains scénarios d’entreprise.

He bien il faut savoir qu’une série de tampons (buffers) est allouée dans la partie contigüe de la mémoire virtuelle du serveur. En générale celle-ci est généralement référencée en dehors de la mémoire qui concerne le cache des données SQL Server et est appelée MemToLeave. Dans une architecture 32 bits, cette partie de mémoire est également partagée avec le cache des données SQL Server. Cette quantité  de mémoire dépend de plusieurs paramètres pour la sauvegarde à savoir :

- MAXTRANSFERSIZE
- BUFFERCOUNT

Ces paramètres sont en fait des options de la commande BACKUP DATABASE. Elles permettent d’allouer plus ou moins de mémoire au processus de sauvegarde (ou de restauration) d’une base de données. La quantité de mémoire est déterminée par la formule suivante :

MAXTRANSFERSIZE * BUFFERCOUNT

 

Cependant ces paramètres ne sont pas obligatoires et sont généralement utilisés que dans de très rares cas. Dans ce cas SQL Server ajuste les paramètres  en fonction des métriques suivantes :

- La version de SQL Server utilisée
- du type et du nombre de support de sauvegarde
- du nombre de fichiers que possèdent la base de données.

 

Si le paramètre MAXTRANSFERTSIZE n’est pas explicitement spécifié alors sa valeur sera calculée de la façon suivante :

Disques	64K pour une version Express ou Desktop 1MB pour les autres versions
Bandes	64K
Périphériques virtuels	64K

A noter que si une base de données est inférieure à 10MB et possède un seul fichier DATA la valeur sera fixée à 64Ko.

 

Si le paramètre BUFFERCOUNT n’est pas explicitement spécifiée alors sa valeur est déterminée de la façon suivante :

SQL Server 2000	(NumberofBackupDevices * GetSuggestedIoDepth) + NumberofBackupDevices + (DatabaseDeviceCount)
SQL Server 2005 et +	(NumberofBackupDevices * GetSuggestedIoDepth) + NumberofBackupDevices + (2*DatabaseDeviceCount)

 

La valeur du paramètre GetSuggestedIoDepth est en fonction du type de support de sauvegarde :

Disques	3
Bandes	1
Périphériques virtuels	1 à 4 en fonction du besoin

 

Dans un scénario simple avec une base de données (SQL Server 2008) possédant un seul fichier DATA et une sauvegarde sur disque avec un seul périphérique et qui utilisent les paramètres par défaut nous aurions :

[1 MB] * [(1 * 3) + 1 + (2 x 1)] = 6MB d’espace mémoire allouée pour la sauvegarde de cette base.

Dans un scénario plus complexe avec une base de données (SQL Server 2008) possédant 2 fichiers DATA et une sauvegarde sur disque utilisant 2 périphériques avec les paramètres par défaut nous aurions :

[1 MB] * [(2 * 3) + 2 + (2 x 2)] = 12MB d’espace mémoire allouée pour la sauvegarde de cette base.

Maintenant si nous spécifions des valeurs de paramètres MAXTRANSFERSIZE (64Ko) et BUFFERCOUNT (20) nous aurions :

64 Ko x 20 = 1MB d’espace mémoire allouée pour la sauvegarde de cette base.

 

Voici une requête qui permet de calculer rapidement l’espace mémoire nécessaire pour une sauvegarde d’une base de données (fourni par l’équipe support SQL Server )

declare @MaxTransferSize float,
@BufferCount bigint,
@DBName varchar(255),
@BackupDevices bigint
– Default value is zero. Value to be provided in MB.
set @MaxTransferSize = 0
– Default value is zero
set @BufferCount = 0
– Provide the name of the database to be backed up
set @DBName = ‘dbBackup’
– Number of disk devices that you are writing the backup to
set @BackupDevices = 1
declare @DatabaseDeviceCount int
select @DatabaseDeviceCount=count(distinct(substring(physical_name,1,charindex(physical_name,':’)+1)))
from sys.master_files
where database_id = db_id(@DBName)
and type_desc <> ‘LOG’

if @BufferCount = 0
                set @BufferCount = (@BackupDevices*3) + @BackupDevices + (2 * @DatabaseDeviceCount)

if @MaxTransferSize = 0
                set @MaxTransferSize = 1

select ‘Total buffer space (MB): ‘ + cast ((@Buffercount * @MaxTransferSize) as varchar(10))

 

Nous savons comment prédire la quantité d’espace contigüe de mémoire virtuelle que réserve SQL Server pour ses opérations de backup. Cependant par défaut rien ne nous permet par défaut de connaître réellement cette quantité lorsqu’une sauvegarde est effectuée. De plus si des outils tiers effectuent les sauvegardes SQL Server, il est difficile de savoir à priori quelles valeurs de paramètres sont utilisés sans avoir à lire la documentation constructeur de l’application ou en demandant aux administrateurs de sauvegarde. Je reviendrais sur ces types de sauvegarde dans un autre billet. Heureusement il existe certaines valeurs de trace (3212, 3605 et 3004) qui permettent d’obtenir cette information depuis les logs SQL Server. A utiliser avec parcimonie comme toujours car ces valeurs de traces peuvent ne plus être valables lors d’une prochaine version. Pour le moment celles-ci fonctionnent pour SQL Server 2000, 2000, 2008 et 2008 R2.

 

dbcc traceon(3004, 3605, 3213, -1)
GO

BACKUP DATABASE DB_TEST
TO DISK = ‘E:\BACKUP\DB_TEST.BAK';

Le résultat est le suivant :

 

Les logs SQL Server nous révèlent des informations intéressantes. Nous voyons que SQL Server a alloué 8MB pour la sauvegarde (Total buffer space). L’architecture de base DB_TEST est la suivante :

Comme nous l’avons vu plus haut, cette quantité est déterminée par les paramètres buffer count (ici 8) et Max Transfer size (ici 1048576 octets). Nous pouvons le vérifier par le calcul en utilisant les formules présentées plus haut :

[1MB] x [(1 x 3) + 1 + (2 x 2)] = 6MB avec NumberOfBackupDevices = 1 et DatabaseDeviceCount = 2

 

Maintenant si l’on utilise 3 périphériques pour la sauvegarde à base de disques voici que l’on obtient :

BACKUP DATABASE DB_TEST
TO DISK = ‘E:\BACKUP\DB_TEST.BAK’,
   DISK = ‘E:\BACKUP\DB_TEST2.BAK’,
   DISK = ‘E:\BACKUP\DB_TEST3.BAK';

 

Le nombre de buffers a augmenté à cause du nombre de périphériques utilisés pour la sauvegarde. Ceci augmente implicitement la quantité de mémoire allouée pour cette opération. Si l’on se réfère à la méthode de calcul vu plus haut on retrouve le résultat trouvé par la commande DBCC dans les logs SQL Server.

[1MB] x [(3 x 3) + 3 + (2 x 2)] = 16MB avec NumberOfBackupDevices = 3 et DatabaseDeviceCount = 2

 

Enfin si nous spécifions une valeur explicite pour les paramètres MAXTRANSFERSIZE et BUFFERCOUNT on obtient :

BACKUP DATABASE DB_TEST
TO DISK = ‘E:\BACKUP\DB_TEST.BAK’,
   DISK = ‘E:\BACKUP\DB_TEST2.BAK’,
   DISK = ‘E:\BACKUP\DB_TEST3.BAK’
WITH MAXTRANSFERSIZE = 65536, BUFFERCOUNT = 20;

Ici pour obtenir la quantité de mémoire allouée pour la sauvegarde il suffit de prendre les valeurs de paramètres passés durant l’opération :

65536 x 20 / (1024 x 1024) = 1MB

 

On peut donc commencer à voir les problèmes de mémoire qui peuvent être engendrés par les opérations de sauvegarde. En effet un mauvais paramétrage des options MAXTRANSFERSIZE ou BUFFERCOUNT peut très vite provoquer un manque de mémoire. Ceci est d’autant plus vrai sur des architectures 32 bits où le manque d’espace et la fragmentation de la mémoire virtuelle sont fréquents. La fragmentation engendre inévitablement un manque d’espace libre en mémoire en allouant plus de pages à un processus que nécessaire. Il en résulte une ratification des espaces libres contigües dans l’espace de mémoire virtuelle.

Depuis SQL Server 2005 il est facile de connaître la taille du plus grand bloc contigüe dans l’espace de mémoire virtuelle (VAS) à l’aide de la DMV sys.dm_os_virtual_address_dump.  La requête suivante permet de connaître cet espace :

WITH VASummary(Size,Reserved,Free) AS
(SELECT
    Size = VaDump.Size,
    Reserved =  SUM(CASE(CONVERT(INT, VaDump.Base)^0)
    WHEN 0 THEN 0 ELSE 1 END),
    Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
    WHEN 0 THEN 1 ELSE 0 END)
FROM
(
    SELECT  CONVERT(VARBINARY, SUM(region_size_in_bytes))
    AS Size, region_allocation_base_address AS Base
    FROM sys.dm_os_virtual_address_dump 
    WHERE region_allocation_base_address <> 0x0
    GROUP BY region_allocation_base_address 
  UNION  
    SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
    FROM sys.dm_os_virtual_address_dump
    WHERE region_allocation_base_address  = 0x0
)
AS VaDump
GROUP BY Size
)
SELECT
    SUM(CONVERT(BIGINT,Size)*Free)/1024/1024 AS [Total avail mem, MB] ,
    CAST(MAX(Size) AS BIGINT)/1024/1024 AS [Max free size, MB] 
FROM VASummary 
WHERE Free <> 0

 

Sur mon serveur de test j’obtiens le résultat suivant :

Ici je ne pourrais pas bénéficier de plus de 321 MB pour mes sauvegardes . Faisons le test suivant :

BACKUP DATABASE DB_TEST
TO DISK = ‘E:\BACKUP\DB_TEST.BAK’,
   DISK = ‘E:\BACKUP\DB_TEST2.BAK’,
   DISK = ‘E:\BACKUP\DB_TEST3.BAK’
WITH MAXTRANSFERSIZE = 1048576, BUFFERCOUNT = 400;

Pour que cette commande fonctionne, il faut pouvoir allouer 1MB x 400 = 400 MB dans l’espace de mémoire virtuelle libre. L’exécution de la commande provoque l’erreur suivante :

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE s’est terminé anormalement.
Msg 701, Level 17, State 17, Line 1
Mémoire système insuffisante pour exécuter cette requête.

 

Evidemment il existe un mécanisme qui permet à SQL Server de réduire et d’ajuster la quantité de mémoire allouée au processus de sauvegarde lorsqu’il existe une pression mémoire sur le serveur. La quantité de mémoire ne peut pas dépassée le ratio suivant : 1/16 de la mémoire physique. Dans ce cas SQL Server réduit le nombre de buffers ou la taille maximale de transfert pour arriver à une taille raisonnable de mémoire. Ceci est valable si les paramètres MAXTRANSFERSIZE et BUFFERCOUNT ne sont pas spécifiés lors de la sauvegarde.

 

Les solutions relatives au problème d’espace mémoire peuvent être multiples dans ce cas :

- Utiliser le commutateur -g de SQL Server pour allouer plus d’espace dans l’espace de mémoire virtuelle 
- Modifier les paramètres de sauvegardes pour allouer moins de mémoire lors du processus de sauvegarde. Les performances peuvent être affectés dans ce cas.
- Réduire le nombre de supports de sauvegarde (affecte les performances)

Un grand merci aux équipes de support Microsoft (et leurs articles) qui m’ont permis de comprendre les processus sous jacents aux sauvegardes SQL Server et résoudre par la même occasion certains problèmes de backup rencontrés avec des outils tiers

David BARBARIN (Mikedavem)
MVP SQL Server