sp_estimate_data_compression_savings (Transact-SQL)

Se aplica a: SQL Server (todas las versiones admitidas) Azure SQL Database Azure 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 indizadas y particiones de tabla e índice. Los objetos se pueden comprimir mediante la compresión 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 versión preliminar 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, vea Características compatibles con las ediciones de SQL Server 2019.

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

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

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 se encuentra 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 no particionado.

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 versión preliminar de 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) en versión preliminar

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 es NULL.

xml_compression no puede ser NULL si data_compression es NULL.

Valores de código de retorno

0 (correcto) o 1 (error)

Conjuntos 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.
index_id 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 utiliza el valor de compresión solicitado y, si es aplicable, factor de relleno existente, suponiendo que no hay 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 a una sola fila en 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 raro, 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 no está comprimida.

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

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 usará el factor de relleno existente para los índices del 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 y restricciones

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 Managed Instance, 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 se habilita Memory-Optimized metadatos de TempDB.

SQL Server 2022 (16.x) Preview Release Candidate (RC) 0 no calcula el ahorro de los í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 Managed Instance, sp_estimate_compression_savings admite la estimación de la compresión del almacén de columnas y del archivo de 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 utilizados 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 agrupado 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.

De forma similar, 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 utilizados.

Source (objeto) Reference (objeto)
Índice de almacén de columnas agrupado Montón
índice no clúster de almacén de columnas Índice no agrupado (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 de 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 tabla Production.WorkOrderRouting si se comprime mediante la compresión ROW.

USE AdventureWorks2016;
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 versión preliminar

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 AdventureWorks2016;
GO
EXEC sys.sp_estimate_data_compression_savings
     'Production', 'ProductModel', NULL, NULL, 'PAGE', 1;
GO

Pasos siguientes