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 ligne, 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.

À compter de SQL Server 2022 (16.x), vous pouvez compresser des données XML hors ligne dans des colonnes à l’aide du xml type de données, ce qui réduit les besoins en stockage et en mémoire. Pour plus d’informations, consultez CREATE TABLE (Transact-SQL) et CREATE INDEX (Transact-SQL). 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.

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

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 = ] 'schema_name'
   , [ @object_name = ] 'object_name'
   , [ @index_id = ] index_id
   , [ @partition_number = ] partition_number
   , [ @data_compression = ] 'data_compression'
   , [ @xml_compression = ] xml_compression
[ ; ]

Arguments

[ @schema_name = ] 'schema_name'

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

[ @object_name = ] 'object_name'

Nom de la table ou de la vue indexée sur laquelle l’index est activé. object_name est sysname.

[ @index_id = ] index_id

ID de l’index. index_id est int et peut être l’une des valeurs suivantes : le numéro d’ID d’un index, NULL ou 0 si object_id est un tas. Pour retourner des informations sur tous les index d'une table de base ou d'une vue, spécifiez la valeur 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 segment de mémoire, NULL ou 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 = ] 'data_compression'

Type de compression à évaluer. data_compression peut être l’une des valeurs suivantes : NONE, ROW, PAGE, COLUMNSTORE ou COLUMNSTORE_ARCHIVE.

Pour SQL Server 2022 (16.x) et versions ultérieures, NULL est également une valeur possible. data_compression ne peut pas être NULL si xml_compression a la valeur NULL.

[ @xml_compression = ] xml_compression

S'applique à : SQL Server 2022 (16.x) et versions ultérieures, base de données Azure SQL, Azure SQL Managed Instance

Spécifie s’il faut calculer des économies pour la compression XML. xml_compression est bit et peut avoir la valeur NULL, 0 ou 1. Le par défaut est NULL.

xml_compression ne peut pas être NULL si data_compression a la valeur 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 = Segment de mémoire

1 = Index cluster

> 1 = Index non cluster
partition_number int Numéro de partition. Retourne 1 pour une table ou un index non partitionnés.
size_with_current_compression_setting (Ko) bigint Taille de la table, de l'index ou de la partition demandés tels qu'ils existent actuellement.
size_with_requested_compression_setting (Ko) 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 (Ko) bigint Taille de l'exemple avec le paramètre de compression actuel. Cela inclut toute fragmentation éventuelle.
sample_size_with_requested_compression_setting (Ko) 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 et 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

Avant SQL Server 2019 (15.x), cette procédure ne s’appliquait pas aux index columnstore et n’acceptait donc pas les paramètres de compression de données COLUMNSTORE et COLUMNSTORE_ARCHIVE. À compter de SQL Server 2019 (15.x), 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 pris en charge avec COLUMNSTORE et COLUMNSTORE_ARCHIVE paramètres de compression de données lorsque les métadonnées TempDB optimisées en mémoire sont activées.

SQL Server 2022 (16.x) Release Candidate (RC) 0 n’estime pas les économies pour les index XML.

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, lors de l’utilisation des options COLUMNSTORE et COLUMNSTORE_ARCHIVE de 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 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 @data_compression paramètre est défini sur COLUMNSTORE ou COLUMNSTORE_ARCHIVE.

Source, objet Objet Reference
Segment de mémoire (heap) 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 @data_compression paramètre 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.

Source, objet 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

R. 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.

USE AdventureWorks2022;
GO
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)

L’exemple suivant estime 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.

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

Contenu connexe