sp_estimate_data_compression_savings (Transact-SQL)
S’applique à : SQL Server
Azure SQL Database
Azure 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 = Tas1 = 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 DEFINITION
VIEW 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_ARCHIVE
COLUMNSTORE
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 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.
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