Partager via

sp_estimate_data_compression_savings (Transact-SQL)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Retourne la taille actuelle de l'objet demandé et estime la taille de l'objet pour l'état de compression demandé. La compression peut être évaluée pour des tables entières ou des parties de tables. Cela inclut les segments de mémoire, les index cluster, les index non cluster, les index columnstore, les vues indexées et les partitions de table et d’index. Les objets peuvent être compressés à l’aide de la compression d’archive row, page, columnstore ou columnstore. Si la table, l’index ou la partition est déjà compressée, vous pouvez utiliser cette procédure pour estimer la taille de la table, de l’index ou de la partition si elle est compressée ou stockée sans compression.

La sys.sp_estimate_data_compression_savings procédure stockée système est disponible dans Azure SQL Database et Azure SQL Managed Instance.

À compter de SQL Server 2022 (16.x), vous pouvez compresser des données XML hors ligne dans des colonnes à l’aide du type de données xml , ce qui réduit les besoins en stockage et en mémoire. Pour plus d’informations, consultez CREATE TABLE et CREATE INDEX. sp_estimate_data_compression_savings prend en charge les estimations de compression XML.

Remarque

Compression et sp_estimate_data_compression_savings ne sont pas disponibles dans chaque édition de SQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

Pour estimer la taille de l’objet s’il devait utiliser le paramètre de compression demandé, cette procédure stockée échantillonne l’objet source et charge ces données dans une table et un index équivalents créés dans tempdb. La table ou l’index créé est tempdb ensuite compressé dans le paramètre demandé et les économies de compression estimées sont calculées.

Pour modifier l’état de compression d’une table, d’un index ou d’une partition, utilisez les instructions ALTER TABLE ou ALTER INDEX . Pour obtenir des informations générales sur la compression, consultez Compression des données.

Remarque

Si les données existantes sont fragmentées, vous pouvez être en mesure de réduire leur taille sans utiliser la compression en reconstruisant l'index. Pour les index, le facteur de remplissage sera appliqué pendant une reconstruction d'index. Cela pourrait augmenter la taille de l'index.

Conventions de la syntaxe Transact-SQL

Syntaxe

sp_estimate_data_compression_savings
    [ @schema_name = ] N'schema_name'
    , [ @object_name = ] N'object_name'
    , [ @index_id = ] index_id
    , [ @partition_number = ] partition_number
    , [ @data_compression = ] N'data_compression'
    [ , [ @xml_compression = ] xml_compression ]
[ ; ]

Arguments

[ @schema_name = ] N’schema_name'

Nom du schéma de base de données qui contient la table ou la vue indexée. @schema_name est sysname, sans valeur par défaut. Si @schema_name est NULL, le schéma par défaut de l’utilisateur actuel est utilisé.

[ @object_name = ] N’object_name'

Nom de la table ou de la vue indexée sur laquelle l’index est activé. @object_name est sysname, sans valeur par défaut.

[ @index_id = ] index_id

ID de l’index. @index_id est int et peut être l’une des valeurs suivantes :

  • numéro d’ID d’un index
  • NULL
  • 0 si object_id est un tas

Pour retourner des informations pour tous les index d’une table ou d’une vue de base, spécifiez NULL. Si vous spécifiez NULL, vous devez également spécifier NULL pour @partition_number.

[ @partition_number = ] partition_number

Numéro de partition dans l’objet. @partition_number est int et peut être l’une des valeurs suivantes :

  • le numéro de partition d’un index ou d’un tas
  • NULL
  • 1 pour un index ou un tas nonpartitionné

Pour spécifier la partition, vous pouvez également spécifier la fonction $PARTITION . Pour retourner des informations pour toutes les partitions de l’objet propriétaire, spécifiez NULL.

[ @data_compression = ] N’data_compression'

Spécifie le type de compression à évaluer. @data_compression est nvarchar(60) et peut être l’une des valeurs suivantes :

  • NONE
  • ROW
  • PAGE
  • COLUMNSTORE
  • COLUMNSTORE_ARCHIVE

Pour SQL Server 2022 (16.x) et versions ultérieures, NULL il s’agit également d’une valeur possible. @data_compression ne peut pas être NULL si @xml_compression est NULL.

[ @xml_compression = ] xml_compression

S’applique à : SQL Server 2022 (16.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Spécifie s’il faut calculer des économies pour la compression XML. @xml_compression est bit et peut être l’une des valeurs suivantes :

  • NULL (valeur par défaut)
  • 0
  • 1

@xml_compression ne peut pas être NULL si @data_compression est NULL.

Valeurs des codes de retour

0 (réussite) ou 1 (échec).

Jeu de résultats

Le jeu de résultats suivant est retourné pour fournir la taille actuelle et estimée de la table, de l'index ou de la partition.

Nom de la colonne Type de données Description
object_name sysname Nom de la table ou de la vue indexée.
schema_name sysname Schéma de la table ou de la vue indexée.
index_id int ID d'index d'un index :

0 = Tas
1 = Index cluster
>1 = Index non cluster
partition_number int Numéro de partition. Retourne 1 une table ou un index nonpartitionné.
size_with_current_compression_setting (KB) bigint Taille de la table, de l'index ou de la partition demandés tels qu'ils existent actuellement.
size_with_requested_compression_setting (KB) bigint Taille estimée de la table, de l’index ou de la partition qui utilise le paramètre de compression demandé ; et, le cas échéant, le facteur de remplissage existant et en supposant qu’il n’y a pas de fragmentation.
sample_size_with_current_compression_setting (KB) bigint Taille de l'exemple avec le paramètre de compression actuel. Cette taille inclut toute fragmentation.
sample_size_with_requested_compression_setting (KB) bigint Taille de l'échantillon créé à l'aide du paramètre de compression demandé et, le cas échéant, du facteur de remplissage existant, sans fragmentation.

Notes

Permet sp_estimate_data_compression_savings d’estimer les économies qui peuvent se produire lorsque vous activez une table ou une partition pour la compression de ligne, de page, de columnstore, d’archive columnstore ou xml. Par exemple, si la taille moyenne de la ligne peut être réduite de 40 pour cent, vous pouvez potentiellement réduire la taille de l’objet de 40 %. Vous n'économiserez peut-être pas d'espace car cela dépend du facteur de remplissage et de la taille de la ligne. Par exemple, si vous avez une ligne de 8 000 octets de long et que vous réduisez sa taille de 40 pour cent, vous ne pouvez toujours tenir qu’une seule ligne sur une page de données. Il n’y a pas d’économies.

Si les résultats de l’exécution sp_estimate_data_compression_savings sur une table ou un index non compressé indiquent que la taille augmente, cela signifie que de nombreuses lignes utilisent presque toute la précision des types de données, et l’ajout de la petite surcharge nécessaire pour le format compressé est plus que les économies de compression. Dans ce cas rare, n’activez pas la compression.

Si une table est déjà activée pour la compression, vous pouvez l’utiliser sp_estimate_data_compression_savings pour estimer la taille moyenne de la ligne si la table n’est pas compressée.

Un verrou partagé d’intention (IS) est acquis sur la table pendant cette opération. Si un verrou IS ne peut pas être obtenu, la procédure est bloquée. La table est analysée sous le niveau d’isolation validé par défaut en lecture.

Si le paramètre de compression demandé est identique au paramètre de compression actuel, la procédure stockée retourne la taille estimée sans fragmentation des données, en utilisant le facteur de remplissage existant pour les index sur l’objet source.

Si l’ID d’index ou de partition n’existe pas, aucun résultat n’est retourné.

Autorisations

Nécessite SELECT une autorisation sur la table et VIEW DEFINITIONVIEW DATABASE STATE sur la base de données contenant la table et sur tempdb.

Limites

Dans SQL Server 2017 (14.x) et les versions antérieures, cette procédure ne s’appliquait pas aux index columnstore, et n’acceptait donc pas les paramètres COLUMNSTORE de compression des données et COLUMNSTORE_ARCHIVE. Dans SQL Server 2019 (15.x) et versions ultérieures, et dans Azure SQL Database et Azure SQL Managed Instance, les index columnstore peuvent être utilisés à la fois comme objet source pour l’estimation et comme type de compression demandé.

Lorsque les métadonnées TempDB optimisées en mémoire sont activées, la création d’index columnstore sur des tables temporaires n’est pas prise en charge. En raison de cette limitation, sp_estimate_data_compression_savings n’est pas prise en charge avec les paramètres de compression des données et COLUMNSTORE_ARCHIVE les COLUMNSTORE paramètres de compression de données lorsque les métadonnées TempDB optimisées en mémoire sont activées.

Considérations relatives aux index columnstore

À compter de SQL Server 2019 (15.x) et dans Azure SQL Database et Azure SQL Managed Instance, sp_estimate_compression_savings prend en charge l’estimation de la compression d’archive columnstore et columnstore. Contrairement à la compression de page et de ligne, l’application de la compression columnstore à un objet nécessite la création d’un index columnstore. Pour cette raison, lorsque vous utilisez les COLUMNSTORE options de COLUMNSTORE_ARCHIVE cette procédure, le type de l’objet source fourni à la procédure détermine le type d’index columnstore utilisé pour l’estimation de la taille compressée. Le tableau suivant illustre les objets de référence utilisés pour estimer les économies de compression pour chaque type d’objet source lorsque le paramètre @data_compression est défini sur l’une ou l’autre .COLUMNSTORE_ARCHIVECOLUMNSTORE

Objet source Objet Reference
**Tas Index columnstore cluster
Index cluster Index columnstore cluster
Index non cluster Index columnstore non cluster (y compris les colonnes clés et toutes les colonnes incluses de l’index non cluster fourni et la colonne de partition de la table, le cas échéant)
Index columnstore non cluster Index columnstore non cluster (y compris les mêmes colonnes que l’index columnstore non cluster fourni)
Index columnstore cluster Index columnstore cluster

Remarque

Lors de l’estimation de la compression columnstore à partir d’un objet source rowstore (index cluster, index non cluster ou tas), s’il existe des colonnes dans l’objet source qui ont un type de données qui n’est pas pris en charge dans un index columnstore, sp_estimate_compression_savings échoue avec une erreur.

De même, lorsque le paramètre @data_compression est défini NONEsur , ROWou que PAGE l’objet source est un index columnstore, le tableau suivant présente les objets de référence utilisés.

Objet source Objet Reference
Index columnstore cluster Segment de mémoire (heap)
Index columnstore non cluster Index non cluster (y compris les colonnes contenues dans l’index columnstore non cluster en tant que colonnes clés et colonne de partition de la table, le cas échéant, en tant que colonne incluse)

Remarque

Lors de l’estimation de la compression rowstore (NONE, ROW ou PAGE) à partir d’un objet source columnstore, assurez-vous que l’index source ne contient pas plus de 32 colonnes clés, car il s’agit de la limite prise en charge dans un index rowstore (non cluster).

Exemples

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

A. Estimer les économies avec la compression ROW

L’exemple suivant évalue la taille de la Production.WorkOrderRouting table si elle est compressée à l’aide ROW de la compression.

EXEC sys.sp_estimate_data_compression_savings
     'Production', 'WorkOrderRouting', NULL, NULL, 'ROW';
GO

B. Estimer les économies avec la compression PAGE et XML

S’applique à : SQL Server 2022 (16.x) et versions ultérieures

L’exemple suivant évalue la taille de la Production.ProductModel table si elle est compressée à l’aide PAGE de la compression et que la valeur @xml_compression est activée.

EXEC sys.sp_estimate_data_compression_savings
     'Production', 'ProductModel', NULL, NULL, 'PAGE', 1;
GO

Contenu connexe