sp_estimate_data_compression_savings (Transact-SQL)
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 DEFINITION
VIEW 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 NONE
sur , ROW
ou 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
Commentaires
https://aka.ms/ContentUserFeedback.
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultezEnvoyer et afficher des commentaires pour