Articles

Utilisation de la COMPRESSION et de la DÉCOMPRESSION dans SQL Server pour Économiser de l’espace disque

Par: Eduardo Pivaral | Mise à jour: 2018-10-09 | Commentaires | Connexes: Plus > Compression

Problème

Vous avez l’une des nombreuses tables SQL Server occupant beaucoup d’espace disque, les données stockées sont principalement des historiques et / ou des journaux de votre application, le client indique youthat vous ne pouvez pas purger les données ou les stocker en dehors de la base de données, car de temps en temps, les utilisateurs doivent vérifier les anciennes données, les données doivent donc être disponibles pour les interroger.Étant donné que les données sont rarement accessibles, les performances ne sont pas critiques pour ces oldrecords, vous recherchez donc une solution de compression pour les tables SQL Server historiques.

Solution

Depuis SQL Server 2016, une solution de compression / décompression a été introduite pour les types de données les plus string et binary, de sorte que vous pouvez compresser les données pour économiser de l’espace disque et ensuite les décompresser lorsque vous devez y accéder.

Dans cette astuce, je vais vous montrer comment implémenter un processus de compression et de décompression dans votre base de données de serveur SQL.

COMPRESSER et DÉCOMPRESSER SQL Server

La fonction utilisée pour COMPRESSER les données est COMPRESS, elle prend une expression et la compresse à l’aide d’un algorithme GZIP, elle renvoie ensuite un type de données varbinary (max).

L’utilisation de base est:

COMPRESS(expression)

Pour décompresser une valeur précédemment compressée, vous devez utiliser la fonction de décompression, elle renvoie un type de données varbinary (max), vous devez donc usecast ou convert pour le renvoyer à son type de données d’origine.

L’utilisation de base est:

DECOMPRESS(expression)

Nous ferons un exemple sur la façon de l’utiliser et nous verrons combien d’espace nous pouvons économiser.

Exemple de COMPRESSION et de DÉCOMPRESSION de SQL Server

Nous allons créer deux tables, l’une qui stockera les données sans compression et l’autre qui stockera les données avec compression, nous allons simplement créer un champ de texte et l’ID respectif comme suit:

CREATE TABLE ( IDENTITY(1,1) NOT NULL, (max) NOT NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON TEXTIMAGE_ON GO-----------CREATE TABLE ( NOT NULL, (max) NOT NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON TEXTIMAGE_ON GO

Ensuite, nous procédons à remplir la table Uncompressed_Table avec des données aléatoires, nous insérons 60 000 enregistrements:

DECLARE @i intSET @i =RAND()*500INSERT INTO VALUES(REPLICATE ('ABC' ,@i ))INSERT INTO VALUES(REPLICATE ('X' ,@i ))INSERT INTO VALUES(REPLICATE ('Y' ,@i ))INSERT INTO VALUES(REPLICATE ('Z' ,@i ))INSERT INTO VALUES(REPLICATE ('$' ,@i ))INSERT INTO VALUES(REPLICATE ('--' ,@i ))GO 10000

Nous pouvons voir l’espace utilisé par la table et le nombre de lignes:

 Espace utilisé par la table avant l'exemple

Et le type de données que nous avons stockées dans la table:

 un instantané des données stockées sur la table

Nous allons compresser les données stockées sur la colonne Text_stored et storeit sur la table compressée, pour voir combien d’espace nous pouvons économiser.

Compresser les données SQL Server

Comme le nombre de lignes est assez petit et que nous sommes sur un serveur de développement, nous procédons avec une simple instruction INSERT INTO et utilisons la fonction COMPRESSER comme suit:

INSERT INTO SELECT ID, COMPRESS()FROM 

Cela insère 60 000 lignes.

REMARQUE: si vous avez un nombre élevé de lignes ou de champs à compresser, ou si vous travaillez sur un environnement non développé, je recommande d’implémenter une solution par lots comme je l’explique dans cette astuce, car la fonction de compression sur un grand ensemble de lignes peut conduire à une utilisation élevée du processeur et des E/S.

Ensuite, nous procédons à la vérification du nombre de lignes et de l’espace utilisé par la table avec les données compressées:

 comparaison d'espace entre les 2 tables

La comparaison est claire, l’espace économisé en compressant les données est énorme, car nous n’utilisons qu’environ 6% de l’espace d’origine. Donc, nous avons atteint le premier de nos objectifs, nous pouvons économiser de l’espace disque maintenant.

La seule chose en attente est de pouvoir interroger les données, car c’est ce à quoi il ressemble lorsque nous sélectionnons la table telle quelle:

À l’heure actuelle, les données sont stockées dans un format GZIP compressé, pour pouvoir les lire, nous devons d’abord les décompresser.

Décompressez les données SQL Server

Pour pouvoir présenter à nouveau les données à votre application, vous devez d’abord décompresser à l’aide de la fonction DÉCOMPRESSER, puis les convertir en type de données d’origine.

Pour notre exemple, nous utiliserons le code suivant:

CAST(DECOMPRESS(Text_stored) as nvarchar(max))

Et l’utiliser dans notre déclaration SELECT:

SELECT ID, CAST(DECOMPRESS(Text_stored) AS NVARCHAR(max)) AS Readable_textFROM 

Et maintenant, les données sont lisibles, elles peuvent donc être utilisées dans notre application:

 décompressez les résultats pour lire les données

Remarque: Comme avec l’instruction INSERT, essayez de minimiser le nombre de lignes à décompresser, car cela peut entraîner une utilisation élevée du processeur.Vous pouvez vérifier comment traiter par lots les déclarations de sélection dans cette astuce.

Nous avons atteint les deux exigences, économiser de l’espace disque et pouvoir interroger les données à l’aide des fonctions compresser et décompresser.

Prochaines étapes

  • Vous pouvez consulter la documentation officielle de COMPRESS ici.
  • Vous pouvez consulter la documentation officielle de DÉCOMPRESSION ici.
  • Vous pouvez en savoir plus sur le formulaire GZIP ici.
  • Notez que puisque le type de données renvoyé est varbinary (max), vous ne pouvez pas utiliser des données compressées sur les index comme colonnes clés (et cela ne fera pas beaucoup d’effet puisque les données sont dans un autre format). Vous pouvez cependant l’utiliser sur les colonnes indexesforINCLUDED, avec l’inconvénient qu’elles doivent être construites hors ligne.
  • Cette solution est la mieux adaptée aux données qui ne sont pas fréquemment interrogées, car l’utilisation des fonctions nécessite une surcharge CPU supplémentaire, surveillez toujours et testez d’abord les performances sur un serveur de développement.

Dernière mise à jour: 2018-10-09

 obtenir des scripts

 bouton conseil suivant

À propos de l’auteur
 Auteur de MSSQLTips Eduardo Pivaral Eduardo Pivaral est un administrateur et développeur de bases de données MCSA, SQL Server avec plus de 15 ans d’expérience dans de grands environnements.
Voir tous mes conseils
Ressources connexes

  • Plus de conseils SQL Server DBA…

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.