Partager via

  • Article

Création de tables et d'index compressés

SQL Server 2008 prend en charge la compression de page et de ligne pour les tables et les index. La compression des données peut être configurée pour les objets de base de données suivants :

  • Une table entière stockée en tant que segment de mémoire.

  • Une table entière stockée en tant qu'index cluster.

  • Un index non cluster entier.

  • Une vue indexée entière.

  • Pour les tables et les index partitionnés, l'option de compression peut être configurée pour chaque partition et il n'est pas obligatoire que les différentes partitions d'un objet aient le même paramètre de compression.

Le paramètre de compression d'une table n'est pas appliqué automatiquement à ses index non cluster. Chaque index doit être défini individuellement. La compression n'est pas disponible pour les tables système. Les tables et index peuvent être compressés lors de leur création à l'aide des instructions CREATE TABLE et CREATE INDEX. Pour modifier l'état de compression d'une table, d'un index ou d'une partition, utilisez les instructions ALTER TABLE ou ALTER INDEX.

[!REMARQUE]

Si les données existantes sont fragmentées, vous pouvez être en mesure de réduire la taille de l'index en reconstruisant l'index au lieu d'utiliser la compression. Le facteur de remplissage d'un index sera appliqué pendant une reconstruction d'index, ce qui pourrait augmenter la taille de l'index. Pour plus d'informations, consultez Facteur de remplissage.

Considérations liées à l'utilisation de compression de page et de ligne

En cas d'utilisation de la compression de page et de ligne, assurez-vous de prendre en compte les considérations suivantes :

  • La compression est disponible uniquement dans les éditions SQL Server 2008 Entreprise et Developer.

  • La compression permet de stocker davantage de lignes dans une page, mais elle ne modifie pas la taille maximale des lignes d'une table ou d'un index.

  • Une table ne peut pas être activée pour la compression lorsque la taille de ligne maximale plus la charge mémoire de compression dépasse la taille de ligne maximale de 8 060 octets. Par exemple, une table qui possède les colonnes c1char(8000) et c2char(53) ne peut pas être compressée en raison de la charge de compression supplémentaire. Lorsque le format de stockage VarDecimal est utilisé, le contrôle de taille de ligne est effectué lorsque le format est activé. Pour la compression de ligne et de page, le contrôle de taille de ligne est effectué lorsque l'objet est compressé initialement, puis vérifié à mesure que chaque ligne est insérée ou modifiée. La compression met en vigueur les deux règles suivantes :

    • Une mise à jour d'un type de longueur fixe doit toujours réussir.

    • La désactivation de la compression de données doit toujours réussir. Même si la ligne compressée est adaptée à la taille de la page, ce qui signifie qu'elle est inférieure à 8 060 octets, SQL Server empêche les mises à jour qui ne seraient pas adaptées à la taille de la ligne lorsqu'elle est décompressée.

  • Lorsqu'une liste de partitions est spécifiée, le type de compression peut être défini sur ROW, PAGE ou NONE sur chacune des partitions. Si la liste de partitions n'est pas spécifiée, toutes les partitions sont définies avec la propriété de compression de données spécifiée dans l'instruction. Lorsqu'une table ou un index est créé, la compression de données est définie sur NONE, sauf indication contraire. Lorsqu'une table est modifiée, la compression existante est conservée, sauf indication contraire.

  • Si vous spécifiez une liste de partitions ou une partition hors limites, une erreur est générée.

  • Les index non cluster n'héritent pas de la propriété de compression de la table. Pour compresser des index, vous devez définir explicitement la propriété de compression des index. Par défaut, le paramètre de compression des index est défini sur NONE lorsque l'index est créé.

  • Lorsqu'un index cluster est créé sur un segment de mémoire, l'index cluster hérite de l'état de compression du segment, à moins qu'un autre état de compression soit spécifié.

  • Lorsqu'un segment de mémoire est configuré pour la compression de niveau page, les pages reçoivent la compression de niveau page uniquement des manières suivantes :

    • Les données sont importées en bloc avec les optimisations en bloc activées.

    • Les données sont insérées via la syntaxe INSERT INTO... WITH (TABLOCK).

    • Une table est reconstruite en exécutant l'instruction ALTER TABLE... REBUILD avec l'option de compression PAGE.

  • Les changements de page alloués dans un segment de mémoire dans le cadre des opérations DML n'utiliseront pas la compression PAGE tant que le segment de mémoire n'est pas reconstruit. Reconstruisez le segment de mémoire en supprimant puis en réappliquant la compression, ou en créant et en supprimant un index cluster.

  • La modification du paramètre de compression d'un segment de mémoire nécessite la reconstruction de tous les index non cluster sur la table afin qu'ils aient des pointeurs vers les nouveaux emplacements de ligne dans le segment de mémoire.

  • Vous pouvez activer ou désactiver la compression ROW ou PAGE en ligne ou hors connexion. L'activation de la compression sur un segment de mémoire est mono-thread pour une opération en ligne.

  • L'espace disque requis pour activer ou désactiver la compression de ligne ou de page est le même que pour créer ou reconstruire un index. Pour les données partitionnées, vous pouvez réduire l'espace requis en activant ou désactivant la compression pour une partition à la fois.

  • Pour déterminer l'état de compression des partitions dans une table partitionnée, interrogez la colonne data_compression de l'affichage catalogue sys.partitions.

  • Lorsque vous compressez des index, les pages de niveau nœud terminal peuvent être compressées avec la compression de ligne et de page. Les pages de niveau non feuille ne reçoivent pas de compression de page.

  • À cause de leur taille, les types de données de grande valeur sont quelquefois stockés séparément des données de ligne normales sur des pages à fonction spéciale. La compression des données n'est pas disponible pour les données stockées séparément.

  • Les tables qui implémentait le format de stockage VarDecimal dans SQL Server 2005 conservent ce paramètre en cas de mise à niveau. Vous pouvez appliquer la compression de ligne à une table qui a le format de stockage VarDecimal. Toutefois, la compression de ligne étant un sur-ensemble du format de stockage VarDecimal, il n'y a aucune raison de conserver le format de stockage VarDecimal. Les valeurs décimales ne bénéficient d'aucune compression supplémentaire lorsque vous combinez le format de stockage VarDecimal avec la compression de ligne. Vous pouvez appliquer la compression de page à une table qui a le format de stockage VarDecimal ; toutefois, les colonnes au format de stockage VarDecimal ne bénéficieront probablement pas d'une compression supplémentaire.

    [!REMARQUE]

    SQL Server 2008 prend en charge le format de stockage VarDecimal ; toutefois, la compression au niveau ligne accomplissant les mêmes objectifs, le format de stockage VarDecimal est désapprouvé. Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

Implémentation de la compression

Pour un résumé de l'implémentation de la compression des données, consultez Implémentation de la compression de ligne et Implémentation de la compression de page.

Évaluation du gain d'espace obtenu grâce à la compression

Pour déterminer la façon dont la modification de l'état de compression affectera une table ou un index, utilisez la procédure stockée sp_estimate_data_compression_savings. La procédure stockée sp_estimate_data_compression_savings est disponible uniquement dans les éditions de SQL Server qui prennent en charge la compression de données.

Impact de la compression sur les tables et les index partitionnés

Lorsque vous utilisez la compression des données avec des tables et des index partitionnés, assurez-vous de prendre en compte les considérations suivantes :

  • Fractionnement d'une plage

    Lorsque des partitions sont fractionnées à l'aide de l'instruction ALTER PARTITION, les deux partitions héritent de l'attribut de compression des données de la partition d'origine.

  • Fusion d'une plage

    Lorsque deux partitions sont fusionnées, la partition résultante hérite de l'attribut de compression des données de la partition de destination.

  • Changement de partitions

    Pour changer une partition, la propriété de compression des données de la partition doit correspondre à la propriété de compression de la table.

  • Reconstruction d'une partition ou de toutes les partitions

    Il existe deux variantes de syntaxe que vous pouvez utiliser pour modifier la compression d'une table ou d'un index partitionné :

    • La syntaxe suivante reconstruit uniquement la partition référencée :

      ALTER TABLE <table_name> 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  <option>)

    • La syntaxe suivante reconstruit la table entière en utilisant le paramètre de compression existant pour toute partition qui n'est pas référencée :

      ALTER TABLE <table_name> 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),
... )

    Les index partitionnés suivent le même principe à l'aide de l'instruction ALTER INDEX.

  • Suppression d'un index cluster partitionné

    Lorsqu'un index cluster est supprimé, les partitions des segments correspondants conservent leur paramètre de compression des données, à moins que le schéma de partitionnement soit modifié. Si le schéma de partitionnement est modifié, toutes les partitions sont reconstruites dans un état non compressé. Pour supprimer un index cluster et modifier le schéma de partitionnement, vous devez effectuer les opérations suivantes :

    1. supprimer l'index cluster ;

    2. modifier la table en utilisant l'option ALTER TABLE ... REBUILD ... qui spécifie l'option de compression.

    La suppression d'un index cluster HORS CONNEXION est une opération très rapide, car seuls les niveaux supérieurs des index clusters sont supprimés. Lorsqu'un index cluster est supprimé EN LIGNE, SQL Server doit reconstruire le segment de mémoire deux fois, une fois pour l'étape 1 et une fois pour l'étape 2.

Impact de la compression sur la réplication

Lorsque vous utilisez la compression des données avec la réplication, assurez-vous de prendre en compte les considérations suivantes :

  • Lorsque l'Agent de capture instantanée génère le script de schéma initial, le nouveau schéma utilise les mêmes paramètres de compression pour la table et ses index. La compression ne peut être activée simplement sur la table et pas sur l'index.

  • Pour la réplication transactionnelle, l'option de schéma d'article détermine les objets et propriétés dépendants qui doivent être écrits. Pour plus d'informations, consultez sp_addarticle.

    L'Agent de distribution ne vérifie pas les Abonnés de bas niveau lorsqu'il applique des scripts. Si la réplication de compression est sélectionnée, la création de la table sur des Abonnés de bas niveau échoue. Dans le cas d'une topologie mixte, n'activez pas la réplication de compression.

  • Pour la réplication de fusion, le niveau de compatibilité de la publication remplace les options de schéma et détermine les objets de schéma qui seront écrits. Pour plus d'informations sur le niveau de compatibilité, consultez Utilisation de plusieurs versions de SQL Server dans une topologie de réplication.

    Dans le cas d'une topologie mixte, s'il n'est pas obligatoire de prendre en charge les nouvelles options de compression, le niveau de compatibilité de la publication doit être défini sur la version de l'Abonné de bas niveau. Si la prise en charge est requise, compressez les tables sur l'Abonné après qu'elles ont été créées.

Le tableau suivant illustre les paramètres de réplication qui contrôlent la compression pendant la réplication.

Intention de l'utilisateur

Répliquer le schéma de partition pour une table ou un index

Répliquer les paramètres de compression

Comportement de script

Répliquer le schéma de partition et activer la compression sur l'Abonné sur la partition.

Vrai

Vrai

Inclut dans le script le schéma de partition et les paramètres de compression.

Répliquer le schéma de partition mais ne pas compresser les données sur l'Abonné.

Vrai

Faux

Exclut le schéma de partition du script, mais pas les paramètres de compression pour la partition.

Ne pas répliquer le schéma de partition et ne pas compresser les données sur l'Abonné.

Faux

Faux

Exclut du script la partition et les paramètres de compression.

Compresser la table sur l'Abonné si toutes les partitions sont compressées sur le serveur de publication, mais ne pas répliquer le schéma de partition.

Faux

Vrai

Vérifie si toutes les partitions sont activées pour la compression.

Exclut la compression du script au niveau de la table.

Impact de la compression sur les autres composants SQL Server

La compression se produit dans le moteur de stockage et les données sont présentées à la plupart des autres composants de SQL Server dans un état non compressé. Cela limite les effets de la compression sur les autres composants de la manière suivante :

  • Opérations d'importation et d'exportation en bloc

    Lorsque des données sont exportées, même au format natif, les données sont sorties au format de ligne non compressé. La taille du fichier de données exporté peut par conséquent être beaucoup plus grande que les données sources.

    Lorsque des données sont importées, si la table cible a été activée pour la compression, les données sont converties par le moteur de stockage au format de ligne compressé. Cela peut provoquer une augmentation de l'utilisation de l'UC, par rapport à une importation des données dans une table non compressée.

    Lorsque des données sont importées en bloc dans un segment de mémoire avec la compression de page, l'opération d'importation en bloc tente de compresser les données avec la compression de page lorsque les données sont insérées.

  • La compression n'affecte pas la sauvegarde et la restauration.

  • La compression n'affecte pas la copie des journaux de transaction.

  • La compression de données est incompatible avec les colonnes fragmentées. Par conséquent, les tables qui contiennent des colonnes fragmentées ne peuvent pas être compressées et les colonnes fragmentées ne peuvent pas être ajoutées à une table compressée.

  • L'activation de la compression peut provoquer la modification des plans de requêtes, car les données sont stockées avec un nombre différent de pages et de lignes par page.

  • La compression des données est prise en charge par SQL Server Management Studio via l'Assistant Compression de données.

Pour démarrer l'Assistant Compression de données

  • Dans l'Explorateur d'objets, cliquez avec le bouton droit sur une table, un index ou une vue indexée, pointez sur Stockage, puis cliquez sur Compresser.

Contrôle de la compression

Pour contrôler la compression de l'instance entière de SQL Server, utilisez les compteurs Page compression attempts/sec et Pages compressed/sec de SQL Server, Access Methods Object.

Pour obtenir des statistiques de compression de page pour des partitions individuelles, interrogez la fonction de gestion dynamique sys.dm_db_index_operational_stats.

Exemples

Certains des exemples suivants utilisent des tables partitionnées et requièrent une base de données qui possède des groupes de fichiers. Pour créer une base de données qui possède des groupes de fichiers, exécutez l'instruction suivante :

CREATE DATABASE TestDatabase
ON  PRIMARY
( NAME = TestDatabase,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf'),
FILEGROUP test1fg
( NAME = TestDBFile1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile1.mdf'),
FILEGROUP test2fg
( NAME = TestDBFile2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile2.ndf'),
FILEGROUP test3fg
( NAME = TestDBFile3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile3.ndf'),
FILEGROUP test4fg
( NAME = TestDBFile4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile4.ndf') ;
GO

Pour basculer vers la nouvelle base de données :

USE TestDatabase
GO

A. Création d'une table qui utilise la compression de ligne

L'exemple suivant crée une table et affecte la valeur ROW à la compression.

CREATE TABLE T1 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = ROW);
GO

B. Création d'une table qui utilise la compression de page

L'exemple suivant crée une table et affecte la valeur PAGE à la compression.

CREATE TABLE T2 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = PAGE);
GO

C. Définition de l'option DATA_COMPRESSION sur une table partitionnée

L'exemple suivant utilise la table TestDatabase créée en utilisant le code fourni précédemment dans cette section. L'exemple crée une fonction et un schéma de partition, puis crée une table partitionnée et spécifie les options de compression pour les partitions de la table. Dans cet exemple, la partition 1 est configurée pour la compression ROW et les partitions restantes sont configurées pour la compression PAGE.

Pour créer un fonction de partition :

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO

Pour créer un schéma de partition :

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO

Pour créer une table partitionnée qui possède des partitions compressées :

CREATE TABLE PartitionTable1 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1),
  DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)
);
GO

D. Définition de l'option DATA_COMPRESSION sur une table partitionnée

L'exemple suivant utilise la base de données utilisée dans l'exemple C. L'exemple crée une table à l'aide de la syntaxe pour les partitions non contiguës.

CREATE TABLE PartitionTable2 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1,3),
  DATA_COMPRESSION = NONE ON PARTITIONS (2,4)
);
GO

E. Modification d'une table pour modifier la compression

L'exemple suivant modifie la compression de la table non partitionnée créée dans l'exemple A.

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

F. Modification de la compression d'une partition dans une table partitionnée

L'exemple suivant modifie la compression de la table partitionnée créée dans l'exemple C. La syntaxe REBUILD PARTITION = 1 provoque uniquement la reconstruction de la partition numéro 1.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

La même opération qui utilise la syntaxe suivante provoque la reconstruction de toutes les partitions dans la table.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

G. Modification de la compression de plusieurs partitions dans une table partitionnée

La syntaxe REBUILD PARTITION = ... peut reconstruire une seule partition. Pour reconstruire plusieurs partitions, vous devez exécuter plusieurs instructions ou exécuter l'exemple suivant afin de reconstruire toutes les partitions, à l'aide des paramètres de compression actuels pour les partitions non spécifiées.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO

H. Modification de la compression sur un index

L'exemple suivant utilise la table créée dans l'exemple A et crée un index sur la colonne C2.

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2) 
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

Exécutez le code suivant pour activer la compression de page sur l'index :

ALTER INDEX IX_INDEX_1 
ON T1
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO

I. Modification de la compression d'une partition unique dans un index partitionné

L'exemple ci-dessous illustre la création d'un index sur une table partitionnée qui utilise la compression de ligne sur toutes les partitions de l'index.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

Pour créer l'index de sorte qu'il utilise différents paramètres de compression pour différentes partitions, utilisez la syntaxe ON PARTITIONS. L'exemple ci-dessous illustre la création d'un index sur une table partitionnée qui utilise la compression de ligne sur la partition 1 de l'index et la compression de page sur les partitions 2 à 4 de l'index.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1),
    DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4 ) ) ;
GO

L'exemple suivant modifie la compression de l'index partitionné.

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

J. Modification de la compression de plusieurs partitions dans un index partitionné

La syntaxe REBUILD PARTITION = ... peut reconstruire une seule partition. Pour reconstruire plusieurs partitions, vous devez exécuter plusieurs instructions ou exécuter l'exemple suivant afin de reconstruire toutes les partitions, à l'aide des paramètres de compression actuels pour les partitions non spécifiées.

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO