sp_estimate_data_compression_savings (Transact-SQL)
Se aplica a: SQL Server
Azure SQL Database
Azure SQL Instancia administrada
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, vea 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ón1 = Í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 NONE
en , ROW
o 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
Comentaris
https://aka.ms/ContentUserFeedback.
Properament: al llarg del 2024 eliminarem gradualment GitHub Issues com a mecanisme de retroalimentació del contingut i el substituirem per un nou sistema de retroalimentació. Per obtenir més informació, consulteu:Envieu i consulteu els comentaris de