sp_estimate_data_compression_savings (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Devuelve el tamaño actual del objeto solicitado y calcula el tamaño del objeto para el estado de compresión solicitado. La compresión se puede evaluar para tablas enteras o partes de tablas. Esto incluye montones, índices agrupados, índices no agrupados, índices de almacén de columnas, vistas indexadas y particiones de tabla e índice. Los objetos se pueden comprimir mediante la compresión de archivo de fila, página, almacén de columnas o almacén de columnas. Si la tabla, el índice o la partición ya están comprimidos, puede usar este procedimiento para calcular el tamaño de la tabla, el índice o la partición si se vuelve a comprimir o se almacena sin compresión.

A partir de SQL Server 2022 (16.x), puede comprimir datos XML fuera de fila en columnas mediante el xml tipo de datos, lo que reduce los requisitos de almacenamiento y memoria. Para obtener más información, vea CREATE TABLE (Transact-SQL) y CREATE INDEX (Transact-SQL). sp_estimate_data_compression_savings admite estimaciones de compresión XML.

Nota:

Compresión y sp_estimate_data_compression_savings no están disponibles en todas las ediciones de SQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL Server, consulte Ediciones y características admitidas de SQL Server 2022.

El sys.sp_estimate_data_compression_savings procedimiento almacenado del sistema está disponible en Azure SQL Database y Azure SQL Instancia administrada.

Para calcular el tamaño del objeto si se usara la configuración de compresión solicitada, este procedimiento almacenado muestra el objeto de origen y carga estos datos en una tabla e índice equivalentes creados en tempdb. A continuación, la tabla o el índice creados en tempdb se comprimen en la configuración solicitada y se calcula el ahorro de compresión estimado.

Para cambiar el estado de compresión de una tabla, índice o partición, use las instrucciones ALTER TABLE o ALTER INDEX . Para obtener información general sobre la compresión, consulte Compresión de datos.

Nota:

Si se fragmentan los datos existentes, es posible que pueda reducir su tamaño regenerando el índice y sin necesidad de utilizar la compresión. Para los índices, el factor de relleno se aplicará cuando se vuelva a generar el índice. Esto podría aumentar el tamaño del índice.

Convenciones de sintaxis de Transact-SQL

Sintaxis

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
[ ; ]

Argumentos

[ @schema_name = ] 'schema_name'

Nombre del esquema de base de datos que contiene la tabla o vista indizada. schema_name es sysname. Si schema_name es NULL, se usa el esquema predeterminado del usuario actual.

[ @object_name = ] 'object_name'

Nombre de la tabla o vista indizada en la que está el índice. object_name es sysname.

[ @index_id = ] index_id

Identificador del índice. index_id es int y puede ser uno de los siguientes valores: el número de identificador de un índice, NULL o 0 si object_id es un montón. Para obtener información de todos los índices de una tabla base o vista, especifique NULL. Si especifica NULL, también debe especificar NULL para partition_number.

[ @partition_number = ] partition_number

Número de partición del objeto. partition_number es int y puede ser uno de los siguientes valores: el número de partición de un índice o montón, NULL o 1 para un índice o montón sin particiones.

Para especificar la partición, también puede especificar la función $PARTITION . Para obtener información sobre todas las particiones del objeto propietario, especifique NULL.

[ @data_compression = ] 'data_compression'

Tipo de compresión que se va a evaluar. data_compression puede ser uno de los siguientes valores: NONE, ROW, PAGE, COLUMNSTORE o COLUMNSTORE_ARCHIVE.

Para SQL Server 2022 (16.x) y versiones posteriores, NULL también es un valor posible. data_compression no puede ser NULL si xml_compression es NULL.

[ @xml_compression = ] xml_compression

Se aplica a SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database y Azure SQL Managed Instance.

Especifica si se deben calcular los ahorros para la compresión XML. xml_compression es bit y puede ser NULL, 0 o 1. El valor predeterminado de es NULL.

xml_compression no puede ser NULL si data_compression es NULL.

Valores de código de retorno

0 (correcto) o 1 (erróneo).

Conjunto de resultados

El siguiente conjunto de resultados se devuelve para proporcionar el tamaño actual y estimado de la tabla, índice o partición.

Nombre de la columna Tipo de datos Descripción
object_name sysname Nombre de la tabla o vista indizada.
schema_name sysname Esquema de la tabla o vista indizada.
id_de_índice int Identificador de índice de un índice:

0 = Montón

1 = Índice clúster

> 1 = Índice no agrupado
partition_number int Número de partición. Devuelve 1 para una tabla o índice sin particiones.
size_with_current_compression_setting (KB) bigint Tamaño actual de la tabla, índice o partición solicitados.
size_with_requested_compression_setting (KB) bigint Tamaño estimado de la tabla, índice o partición que usa la configuración de compresión solicitada; y, si procede, el factor de relleno existente y suponiendo que no haya ninguna fragmentación.
sample_size_with_current_compression_setting (KB) bigint Tamaño del ejemplo con la opción de compresión actual. Esto incluye cualquier fragmentación.
sample_size_with_requested_compression_setting (KB) bigint Tamaño del ejemplo que se crea utilizando el valor de compresión solicitado y, si es aplicable, factor de relleno existente, sin fragmentación.

Comentarios

Use sp_estimate_data_compression_savings para calcular el ahorro que puede producirse al habilitar una tabla o partición para la fila, página, almacén de columnas, archivo de almacén de columnas o compresión XML. Por ejemplo, si el tamaño medio de la fila se puede reducir en un 40 por ciento, puede reducir el tamaño del objeto en un 40 por ciento. Es posible que no consiga ahorrar espacio, ya que depende del factor de relleno y del tamaño de la fila. Por ejemplo, si tiene una fila de 8000 bytes de longitud y reduce su tamaño en un 40 por ciento, todavía puede ajustarse solo a una fila de una página de datos. No hay ahorros.

Si los resultados de la ejecución sp_estimate_data_compression_savings en una tabla o índice sin comprimir indican que el tamaño aumentará, esto significa que muchas filas usan casi toda la precisión de los tipos de datos y la adición de la pequeña sobrecarga necesaria para el formato comprimido es mayor que el ahorro de compresión. En este caso poco frecuente, no habilite la compresión.

Si una tabla ya está habilitada para la compresión, puede usar sp_estimate_data_compression_savings para calcular el tamaño medio de la fila si la tabla está sin comprimir.

Durante esta operación se adquiere un bloqueo compartido de intenciones (IS). Si no se puede obtener un bloqueo IS, se bloqueará el procedimiento. La tabla se examina en el nivel de aislamiento predeterminado de lectura confirmada.

Si la configuración de compresión solicitada es la misma que la configuración de compresión actual, el procedimiento almacenado devolverá el tamaño estimado sin fragmentación de datos y el uso del factor de relleno existente para los índices en el objeto de origen.

Si el índice o el identificador de partición no existen, no se devuelve ningún resultado.

Permisos

Requiere SELECT permiso en la tabla VIEW DATABASE STATE y VIEW DEFINITION en la base de datos que contiene la tabla y en tempdb.

Limitaciones

Antes de SQL Server 2019 (15.x), este procedimiento no se aplicaba a los índices de almacén de columnas y, por tanto, no aceptaba los parámetros de compresión de datos COLUMNSTORE y COLUMNSTORE_ARCHIVE. A partir de SQL Server 2019 (15.x) y en Azure SQL Database y Azure SQL Instancia administrada, los índices de almacén de columnas se pueden usar como un objeto de origen para la estimación y como un tipo de compresión solicitado.

Cuando se habilitan los metadatos tempDB optimizados para memoria, no se admite la creación de índices de almacén de columnas en tablas temporales. Debido a esta limitación, sp_estimate_data_compression_savings no se admite con COLUMNSTORE y COLUMNSTORE_ARCHIVE parámetros de compresión de datos cuando los metadatos tempDB optimizados para memoria están habilitados.

SQL Server 2022 (16.x) Release Candidate (RC) 0 no calcula el ahorro de índices XML.

Consideraciones sobre los índices de almacén de columnas

A partir de SQL Server 2019 (15.x) y en Azure SQL Database y Azure SQL Instancia administrada, sp_estimate_compression_savings admite la estimación de la compresión de archivo de almacén de columnas y almacén de columnas. A diferencia de la compresión de página y fila, aplicar la compresión de almacén de columnas a un objeto requiere la creación de un nuevo índice de almacén de columnas. Por este motivo, cuando se usan las opciones COLUMNSTORE y COLUMNSTORE_ARCHIVE de este procedimiento, el tipo del objeto de origen proporcionado al procedimiento determina el tipo de índice de almacén de columnas utilizado para la estimación de tamaño comprimido. En la tabla siguiente se muestran los objetos de referencia usados para calcular el ahorro de compresión de cada tipo de objeto de origen cuando el @data_compression parámetro se establece en COLUMNSTORE o en COLUMNSTORE_ARCHIVE.

Source (objeto) Reference (objeto)
Montón Índice de almacén de columnas agrupado
Índice agrupado Índice de almacén de columnas agrupado
Índice no clúster Índice de almacén de columnas no agrupado (incluidas las columnas de clave y las columnas incluidas del índice no clúster proporcionado y la columna de partición de la tabla, si existe)
índice no clúster de almacén de columnas Índice de almacén de columnas no agrupado (incluidas las mismas columnas que el índice de almacén de columnas no agrupado proporcionado)
Índice de almacén de columnas agrupado Índice de almacén de columnas agrupado

Nota:

Al calcular la compresión del almacén de columnas de un objeto de origen de almacén de filas (índice agrupado, índice no agrupado o montón), si hay columnas en el objeto de origen que tienen un tipo de datos que no se admite en un índice de almacén de columnas, sp_estimate_compression_savings se producirá un error.

Del mismo modo, cuando el @data_compression parámetro se establece NONEen , ROWo PAGE y el objeto de origen es un índice de almacén de columnas, en la tabla siguiente se describen los objetos de referencia usados.

Source (objeto) Reference (objeto)
Índice de almacén de columnas agrupado Montón
índice no clúster de almacén de columnas Índice no clúster (incluidas las columnas contenidas en el índice de almacén de columnas no agrupado como columnas de clave y la columna de partición de la tabla, si existe, como columna incluida)

Nota:

Al calcular la compresión del almacén de filas (NONE, ROW o PAGE) desde un objeto de origen de almacén de columnas, asegúrese de que el índice de origen no contiene más de 32 columnas de clave, ya que este es el límite admitido en un índice de almacén de filas (no agrupado).

Ejemplos

A Estimación del ahorro con compresión ROW

En el ejemplo siguiente se calcula el tamaño de la Production.WorkOrderRouting tabla si se comprime mediante ROW compresión.

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

B. Estimación del ahorro con compresión PAGE y XML

Se aplica a: SQL Server 2022 (16.x)

En el ejemplo siguiente se calcula el tamaño de la Production.ProductModel tabla si se comprime mediante PAGE compresión y el valor de xml_compression está habilitado.

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