SQL Server 2008, benchmark des compressions de sauvegardes

Introduction

MS SQL Server 2008 introduit la compression des sauvegardes. Des benchmarks ont été réalisés pour observer les gains en espace et la surconsommation CPU lors de la compression des sauvegardes.

Les cas des bases encryptées avec l’option TDE (Transparent Data Encryption) de SQL Server 2008 et l’influence de la compression lors des opérations de restauration sont également abordés.

Ce benchmark a pour but de comprendre 2 points essentiels évoqués dans les documentations de Microsoft :

comprendre pourquoi Microsoft évoque l’utilisation du gouverneur de ressources (Resource Governor) pour limiter la consommation CPU des opérations de sauvegarde/restauration lorsque la compression est activée.
comprendre dans quelle mesure la compression est inefficace sur les bases encryptées avec TDE (Transparent Data Encryption).

Activation de la compression des sauvegardes

La compression des sauvegardes pour une base de données est activée par 2 méthodes :

Activation au niveau serveur avec le paramètre "backup compression default".
Activation pour une base de données avec le paramètre WITH COMPRESSION de la commande BACKUP DATABASE.

Au niveau serveur, la compression des sauvegardes est activée systématiquement lorsque le paramètre serveur "backup compression default" est positionné à 1 :

exec sp_configure 'backup compression default',1
reconfigure with override
go

exec sp_configure 'backup compression default'
go
          
name                            minimum   maximum config_value run_value
------------------------------ -------- --------- ------------ ---------
backup compression default            0         1            1         1

La compression peut n’être appliquée que pour une seule base de données avec l’option WITH COMPRESSION des commandes BACKUP DATABASE et BACKUP LOG :

BACKUP DATABASE <dbname> TO DISK='<chemin_vers_fichier_sauvegarde>'
WITH INIT, COMPRESSION

BACKUP LOG <dbname> TO DISK='<chemin_vers_fichier_sauvegarde>'
WITH INIT, COMPRESSION

Il n’est pas nécessaire de spécifier la moindre option de compression lors d’opérations RESTORE DATABASE/LOG, la compression est automatiquement détectée à la restauration.

Contrairement à Sybase, il n’existe pas de taux de compression (1, 2, 3, etc.), le niveau de compression dans SQL Server est un simple flag

Contexte et outils pour le benchmark

Bases de test

Les tests sont réalisés sont 2 bases de données SQL Server 2008 avec les caractéristiques suivantes :

Base	Taille (Gb)	Encryption TDE
phobos	`35,1`	Non
CRM	`16,02`	Oui

La base CRM est encryptée avec l’option TDE (Transparent Data Encryption).

Chaque opération de sauvegarde/restauration est jouée 5 fois pour en tirer une moyenne et les sauvegardes sont réalisées vers un support unique (device), aucun stripping n’est appliqué.

Outil de détection de la consonmmation CPU (vue dynamique dm_exec_sessions)

La consommation CPU des commandes backup/restore est mesurée grâce à la vue dynamique dm_exec_sessions pour le process ID qui exécute les commandes backup/restore (sessionid = @@spid). La requête ci-dessous est exécutée avant et après chaque commande backup ou restore.

SELECT es.session_id
    ,es.program_name
    ,es.login_name
    ,es.nt_user_name
    ,es.login_time
    ,es.host_name
    ,es.cpu_time
    ,es.total_scheduled_time
    ,es.total_elapsed_time
    ,es.memory_usage
    ,es.logical_reads
    ,es.reads
    ,es.writes
FROM sys.dm_exec_sessions es
WHERE es.session_id=@@spid

Résultats des benchmarks

Pré allocation du fichier de sauvegarde lors de l’activation de la compression

Sans compression, SQL Server alloue un fichier de sauvegarde dont la taille est égale à la taille de la base en données afin de réserver un espace contigü.

Lorsque la compression est activée, une constante est observée : la taille du fichier de sauvegarde préalablement créé dès le lancement de la commande backup database est d’environ 33% de la taille de la base. SQL Server ne sait donc pas estimer le taux de compression au plus juste lors du démarrage de la sauvegarde, tout va dépendre en effet du typage des données dans la base.

Performances des taux de compression

Les résultats sont sans appel, la compression est inefficace sur les bases encryptées avec TDE. 80% pour une base classique non encryptée, 0,34% pour une base encryptée TDE.

Base	Taille sauvegarde non compressée (Gb)	Taille sauvegarde compressée (Gb)	Taux de compression (%)
phobos (non encryptée TDE)	`35,12`	`6,8`	`80,64 %`
CRM (encryptée TDE)	`16,04`	`15,99`	`0,34 %`

Performances des temps de sauvegardes avec la compression

Dans tous les cas de figure, base encryptée avec TDE ou non, les temps de sauvegarde semblent meilleurs de 15 à 25% avec la compression.

Base	Sans compression	Avec compression	Gain en temps
phobos (non encryptée TDE)	`105,18 Mb/sec`	`131,26 Mb/sec`	`24,8 %`
CRM (encryptée TDE)	`68,07 Mb/sec`	`79,05 Mb/sec`	`16,13 %`

L’amélioration des temps de sauvegarde est tout de même la plus notable pour la base phobos non encryptée.

Surconsommation CPU avec la compression

La surconsommation CPU pour une base normale est de 30% environ, ce qui est très raisonnable, en revanche lorsqu’il s’agit d’une base encryptée TDE, la consommation CPU explose d’un facteur de 1000.

Base	Consommation CPU sans compression	Consommation CPU avec compression	Surconsommation
phobos (non encryptée TDE)	`1594`	`2067`	`29,67 %`
CRM (encryptée TDE)	`737`	`9414`	`1177,34 %`

Performances des restaurations avec des sauvegardes compressées

Il n’a pas été noté une quelconque amélioration ou dégradation des temps de restaurations à partir de sauvegardes compressées.

Base	Temps de restauration sauvegarde non compressée	Temps de restauration sauvegarde compressée
phobos (non encryptée TDE)	`85,02 MB/sec`	`84,99 MB/sec`
CRM (encryptée TDE)	`63,7 MB/sec`	`63,67 MB/sec`

La contention sur les disques vient immédiatement à l’esprit pour expliquer un temps équivalent de restauration à partir d’une sauvegarde compressée ou non, cependant la contention sur les disques ne peut pas à elle seule expliquer la surconsommation CPU très forte observée lors de la restauration de sauvegardes compressées : 100 à 120% pour une base non encryptée, plus de 800% pour une base encryptée TDE.

Base	Consommation CPU Sauvegarde non compressée	Consommation CPU Sauvegarde compressée	Surconsommation CPU
phobos (non encryptée TDE)	`1593,75`	`3484,25`	`118 %`
CRM (encryptée TDE)	`850,75`	`7937,5`	`833 %`

L’utilisation du gouverneur de ressources (resource governor) prend tout son sens lors des restaurations à partir de sauvegardes compressées, y compris pour les bases non encryptées avec TDE.

Conclusions

Les informations des documentations Microsoft sont confirmées.

Les taux de compression sont très intéressants pour des bases normales : 80% pour des bases sans typage exotique et contenant beaucoup de colonnes typées avec du varchar. Le taux de compression est quasi nul pour des bases encryptées avec TDE. Dans les 2 cas, les temps de sauvegarde sont améliorés de 15 à 25%.
La surconsommation CPU est d’environ 30% lors de la sauvegarde des bases classiques avec la compression. La mise en place de la limitation CPU via le gouverneur de ressources dépend de l’activité critique dans la base. Elle est dramatique pour les bases encryptées avec TDE : plus de 1000% de surconsommation CPU.
La surconsommation CPU est très forte lors de la restauration de sauvegardes compressées. Elle est énorme lorsqu’il s’agit d’une base encryptée avec TDE.

Pour conclure : ne jamais appliquer la compression des sauvegardes pour les bases encryptées avec TDE, cette fonctionnalité n’apporte rien en taux de compression et a un impact dramatique sur la consommation CPU de SQL Server.

Quant à l’utilisation du gouverneur de ressources pour limiter la surconsommation CPU lors de la sauvegarde compressée de bases classiques non encryptées, elle n’est nécessaire que si les heures des sauvegardes coïncident avec une activité forte dans la base.

MS SQL Server 2008 - Benchmark de la compression des sauvegardes