sp_estimate_data_compression_savings (Transact-SQL)

  • Artículo

Se aplica a: SQL Server 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 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.

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

A partir de SQL Server 2022 (16.x), puede comprimir datos XML fuera de fila en columnas mediante el tipo de datos xml , lo que reduce los requisitos de almacenamiento y memoria. Para obtener más información, consulte CREATE TABLE y CREATE INDEX. 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.

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

Argumentos

[ @schema_name = ] N'schema_name'

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

[ @object_name = ] N'object_name'

Nombre de la tabla o vista indizada en la que está el índice. @object_name es sysname, sin ningún valor predeterminado.

[ @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
  • 0 si object_id es un montón

Para devolver información de todos los índices de una tabla o vista base, 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
  • 1 para un índice o montón no particionado

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

[ @data_compression = ] N'data_compression'

Especifica el tipo de compresión que se va a evaluar. @data_compression es nvarchar(60) y puede ser uno de los siguientes valores:

  • NONE
  • ROW
  • PAGE
  • COLUMNSTORE
  • 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 Instancia administrada

Especifica si se deben calcular los ahorros para la compresión XML. @xml_compression es bit y puede ser uno de los siguientes valores:

  • NULL (valor predeterminado)
  • 0
  • 1

@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.
index_id int Identificador de índice de un índice:

0 = Montón
1 = Índice agrupado
>1 = Índice no clúster
partition_number int Número de partición. Devuelve 1 para una tabla o índice no particionados.
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. Este tamaño 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 bloquea 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 devuelve el tamaño estimado sin fragmentación de datos, utilizando el 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

En SQL Server 2017 (14.x) y versiones anteriores, este procedimiento no se aplicaba a los índices de almacén de columnas y, por tanto, no aceptaba los parámetros COLUMNSTORE de compresión de datos y COLUMNSTORE_ARCHIVE. En SQL Server 2019 (15.x) y versiones posteriores, 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 los parámetros de COLUMNSTORE compresión de datos y COLUMNSTORE_ARCHIVE cuando los metadatos tempDB optimizados para memoria están habilitados.

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, al usar las COLUMNSTORE opciones 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 parámetro @data_compression se establece COLUMNSTORE en o COLUMNSTORE_ARCHIVE.

Objeto de origen 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 de almacén de columnas no agrupado Í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 parámetro @data_compression 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.

Objeto de origen Reference (objeto)
Índice de almacén de columnas agrupado Montón
Índice de almacén de columnas no agrupado Í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

Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.

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.

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) y versiones posteriores.

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.

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

Contenido relacionado