Crear tablas e índices comprimidos
SQL Server 2008 admite la compresión de filas y páginas tanto para tablas como para índices. La compresión de datos se puede configurar para los objetos de base de datos siguientes:
Una tabla entera que está almacenada como un montón.
Una tabla entera que está almacenada como un índice clúster.
Un índice no clúster entero.
Una vista indizada entera.
Para tablas e índices con particiones, la opción de compresión se puede configurar para cada partición y las diferentes particiones de un objeto no tienen por qué tener la misma configuración de compresión.
La configuración de compresión de una tabla no se aplica automáticamente a sus índices no clúster. Cada índice se debe establecer individualmente. La compresión no está disponible para las tablas del sistema. Las tablas y los índices se pueden comprimir cuando se crean utilizando las instrucciones CREATE TABLE y CREATE INDEX. Para cambiar el estado de compresión de una tabla, índice o partición, utilice las instrucciones ALTER TABLE o ALTER INDEX.
Nota
Si se fragmentan los datos existentes, es posible que pueda reducir el tamaño del índice volviéndolo a generar en lugar de utilizar la compresión. El factor de relleno de un índice se aplicará durante el proceso de volver a generarlo; esto puede dar lugar a un aumento de su tamaño. Para obtener más información, vea Factor de relleno.
Consideraciones sobre el uso de la compresión de filas y páginas
Cuando utilice la compresión de filas y páginas, tenga en cuenta las consideraciones siguientes:
La compresión solo está disponible en las ediciones Enterprise y Developer de SQL Server 2008.
La compresión puede permitir que se almacenen más filas en una página, pero no cambia el tamaño máximo de filas de una tabla o índice.
Una tabla no se puede habilitar para su compresión cuando el tamaño máximo de filas más la sobrecarga de compresión supera el tamaño máximo de filas de 8060 bytes. Por ejemplo, una tabla que tiene las columnas c1 y char(8000) y c2 char(53) no se puede comprimir debido a la sobrecarga adicional de la compresión. Cuando se utiliza el formato de almacenamiento vardecimal, la comprobación del tamaño de filas se realiza cuando se habilita el formato. Para la compresión de filas y páginas, la comprobación del tamaño de filas se realiza cuando el objeto se comprime inicialmente, y después se comprueba cuando se inserta o modifica cada fila. La compresión exige las dos reglas siguientes:
Una actualización a un tipo de longitud fija siempre debe tener éxito.
La deshabilitación de la compresión de datos siempre debe tener éxito. Aunque la fila comprimida quepa en la página, lo que significa que tiene menos de 8060 bytes, SQL Server evita actualizaciones que no cabrían en la fila al descomprimirla.
Cuando se especifica una lista de particiones, el tipo de compresión se puede establecer en ROW, PAGE o NONE en particiones individuales. Si no se especifica la lista de particiones, todas las particiones se establecen con la propiedad de compresión de datos que se especifica en la instrucción. Cuando se crea una tabla o índice, la compresión de datos se establece en NONE, a menos que se especifique lo contrario. Cuando se modifica una tabla, se conserva la compresión existente, a menos que se especifique lo contrario.
Si se especifica una lista de particiones o una partición que están fuera del intervalo, se generará un error.
Los índices no clúster no heredan la propiedad de compresión de la tabla. Para comprimir índices, se debe establecer explícitamente la propiedad de compresión de los índices. De forma predeterminada, el valor de compresión de índices se establecerá en NONE cuando se crea el índice.
Cuando se crea un índice clúster en un montón, el índice clúster hereda el estado de compresión del montón, a menos que se especifique otro estado de compresión.
Cuando un montón se configura para la compresión de nivel de página, las páginas solo reciben la compresión de nivel de página de las formas siguientes:
Los datos se importan de forma masiva con las optimizaciones masivas habilitadas.
Los datos se insertan utilizando la sintaxis INSERT INTO ... WITH (TABLOCK).
Una tabla se vuelve a generar ejecutando la instrucción ALTER TABLE... REBUILD con la opción de compresión PAGE.
Las nuevas páginas asignadas en un montón como parte de las operaciones DML no usarán la compresión PAGE hasta que se vuelva a generar el montón. Para volver a generar el montón, quite y vuelva a aplicar la compresión, o cree y quite un índice clúster.
El cambio del valor de compresión de un montón requiere que todos los índices no clúster de la tabla se vuelvan a generar de modo que tengan punteros a las nuevas ubicaciones de fila en el montón.
Puede habilitar o deshabilitar la compresión ROW o PAGE conectado o sin conexión. La habilitación de la compresión en un montón es de un solo subproceso para una operación en línea.
Los requisitos de espacio en disco para habilitar o deshabilitar la compresión de filas o páginas son los mismos que los necesarios para crear o volver a generar un índice. Para los datos con particiones, puede reducir el espacio que se requiere habilitando o deshabilitando la compresión para una partición cada vez.
Para determinar el estado de compresión de las particiones en una tabla con particiones, vea la columna data_compression de la vista de catálogo sys.partitions.
Cuando se comprimen índices, las páginas de nivel de hoja se pueden comprimir tanto con la compresión de filas como con la compresión de páginas. Las páginas que no están en el nivel hoja no reciben la compresión de páginas.
Debido a su tamaño, los tipos de datos de valores grandes suelen almacenarse independientemente de los datos de fila normales en páginas con un fin específico. La compresión de datos no está disponible para los datos que se almacenan independientemente.
Las tablas que implementaron el formato de almacenamiento vardecimal en SQL Server 2005 conservarán ese valor cuando se actualicen. Se puede aplicar la compresión de filas a una tabla que tenga el formato de almacenamiento vardecimal. Sin embargo, como la compresión de filas es un superconjunto del formato de almacenamiento vardecimal, no hay ninguna razón para conservar el formato de almacenamiento vardecimal. Los valores decimales no obtienen una compresión adicional cuando se combina el formato de almacenamiento vardecimal con la compresión de filas. Puede aplicar la compresión de páginas a una tabla que tenga el formato de almacenamiento vardecimal; sin embargo, es probable que las columnas con formato de almacenamiento vardecimal no consigan una compresión adicional.
Nota
SQL Server 2008 admite el formato de almacenamiento vardecimal; sin embargo, como la compresión de nivel de fila consigue los mismos objetivos, el formato de almacenamiento vardecimal ha quedado desusado. Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.
Implementación de la compresión
Para obtener un resumen de la implementación de la compresión de datos, vea Implementación de la compresión de fila, Implementación de la compresión de página y Implementación de la compresión Unicode.
Evaluar los ahorros de la compresión
Para determinar cómo afecta el cambio del estado de compresión a una tabla o a un índice, utilice el procedimiento almacenado sp_estimate_data_compression_savings. El procedimiento almacenado sp_estimate_data_compression_savings solo está disponible en las ediciones de SQL Server que son compatibles con la compresión de datos.
Cómo afecta la compresión a tablas e índices con particiones
Cuando utilice la compresión de datos con tablas e índices con particiones, tenga en cuenta las consideraciones siguientes:
Dividir un intervalo
Cuando se divide una partición utilizando la instrucción ALTER PARTITION, ambas particiones heredan el atributo de compresión de datos de la partición original.
Combinar un intervalo
Cuando se combinan dos particiones, la partición resultante hereda el atributo de compresión de datos de la partición de destino.
Cambiar particiones
Para cambiar una partición, la propiedad de compresión de datos de la partición debe coincidir con la propiedad de compresión de la tabla.
Volver a generar una partición o todas las particiones
Hay dos variaciones de sintaxis que se pueden utilizar para modificar la compresión de una tabla o índice con particiones:
La sintaxis siguiente vuelve a generar solo la partición a la que se hace referencia:
ALTER TABLE <table_name> REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = <option>)La sintaxis siguiente vuelve a generar la tabla completa utilizando el valor de compresión existente para las particiones a las que no se haga referencia:
ALTER TABLE <table_name> REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>), ... )
Los índices con particiones siguen el mismo principio mediante el uso de ALTER INDEX.
Quitar un índice clúster con particiones
Cuando se quita un índice clúster, las particiones del montón correspondientes retienen su valor de compresión de datos, a menos que se modifique el esquema de partición. Si se cambia el esquema de partición, todas las particiones se vuelven a generar en un estado sin comprimir. Para quitar un índice clúster y cambiar el esquema de partición es necesario realizar los pasos siguientes:
1. Quitar el índice clúster.
2. Modificar la tabla utilizando la opción ALTER TABLE ... REBUILD ... que especifica la opción de compresión.
La operación de quitar un índice clúster OFFLINE es muy rápida porque solo se quitan los niveles superiores de índices clústeres. Cuando se quita un índice clúster ONLINE, SQL Server debe volver a generar el montón dos veces, una vez para el paso 1 y otra para el paso 2.
Cómo afecta la compresión a la replicación
Cuando utilice la compresión de datos con replicación, tenga en cuenta las consideraciones siguientes:
Cuando el Agente de instantáneas genera el script de esquema inicial, el nuevo esquema utilizará los mismos valores de compresión para la tabla y sus índices. La compresión no se puede habilitar solo en la tabla y no en el índice.
Para la replicación transaccional, la opción de esquema de artículo determina para qué propiedades y objetos dependientes se van a generar scripts. Para obtener más información, vea sp_addarticle.
El Agente de distribución no comprueba los suscriptores de nivel inferior cuando aplica scripts. Si se selecciona la replicación de compresión, se producirá un error en la creación de la tabla en los suscriptores de nivel inferior. En el caso de una topología mixta, no se debe habilitar la replicación de compresión.
Para la replicación de mezcla, el nivel de compatibilidad de la publicación invalida las opciones de esquema y determina los objetos de esquema para los que se crearán scripts. Para obtener más información acerca del nivel de compatibilidad, vea Usar varias versiones de SQL Server en una topología de replicación.
En el caso de una topología mixta, si no se requiere la admisión de las nuevas opciones de compresión, el nivel de compatibilidad de la publicación debe establecerse en la versión de suscriptor de nivel inferior. En caso de que se requiera, se deben comprimir las tablas en el suscriptor una vez creadas.
La tabla siguiente muestra los valores de replicación que controlan la compresión durante la replicación.
Intento del usuario |
Replicar el esquema de partición para una tabla o índice |
Replicar los valores de compresión |
Comportamiento de creación de script |
|---|---|---|---|
Replicar el esquema de partición y habilitar la compresión en el suscriptor en la partición. |
True |
True |
Se crean scripts tanto para el esquema de partición como para los valores de compresión. |
Replicar el esquema de partición pero no comprimir los datos en el suscriptor. |
True |
False |
Se crean scripts para el esquema de partición pero no para los valores de compresión para la partición. |
No replicar el esquema de partición y no comprimir los datos en el suscriptor. |
False |
False |
No se crean scripts para la partición o para los valores de compresión. |
Comprimir la tabla en el suscriptor si todas las particiones se comprimen en el publicador, pero no replicar el esquema de partición. |
False |
True |
Comprueba si todas las particiones están habilitadas para la compresión. Se crean scripts para la compresión en el nivel de tabla. |
Cómo afecta la compresión a los demás componentes de SQL Server
La compresión se produce en el motor de almacenamiento y los datos se presentan a la mayoría de los demás componentes de SQL Server en un estado sin comprimir. Esto limita los efectos de la compresión en los demás componentes para lo siguiente:
Operaciones de exportación e importación masivas
Cuando se exportan datos, incluso en formato nativo, los datos se envían en formato de fila sin comprimir. Esto puede hacer que el tamaño del archivo de datos exportados sea significativamente mayor que el de los datos de origen.
Cuando se importan datos, si la tabla de destino se ha habilitado para la compresión, el motor de almacenamiento convierte los datos a formato de fila comprimida. Esto puede implicar un mayor uso de la CPU si se compara con la importación de datos en una tabla sin comprimir.
Cuando los datos se importan de forma masiva a un montón con compresión de página, la operación de importación masiva intentará comprimir los datos con compresión de página cuando se inserten los datos.
La compresión no afecta a las acciones de copias de seguridad y restauración.
La compresión no afecta al trasvase de registros.
La compresión de datos es incompatible con las columnas dispersas. Por consiguiente, las tablas que contienen columnas dispersas no pueden comprimirse y las columnas dispersas no se pueden agregar a una tabla comprimida.
Al habilitar la compresión, se puede hacer que los planes de consulta cambien porque los datos se almacenan utilizando un número diferente de páginas y de filas por cada página.
La compresión de datos se admite en SQL Server Management Studio mediante el Asistente para compresión de datos.
Para iniciar el Asistente para compresión de datos
- En el Explorador de objetos, haga clic con el botón secundario en una tabla, un índice o una vista indizada, seleccione Almacenamiento y, a continuación, haga clic en Comprimir.
Supervisar la compresión
Para supervisar la compresión de toda la instancia de SQL Server, utilice los contadores Page compression attempts/sec y Pages compressed/sec del SQL Server, Access Methods Object.
Para obtener estadísticas de compresión de páginas de particiones individuales, vea la función de administración dinámica sys.dm_db_index_operational_stats.
Ejemplos
Algunos de los ejemplos siguientes usan tablas con particiones y requieren una base de datos que tenga grupos de archivos. Para crear una base de datos que tenga grupos de archivos, ejecute la instrucción siguiente:
CREATE DATABASE TestDatabase
ON PRIMARY
( NAME = TestDatabase,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDB.mdf'),
FILEGROUP test1fg
( NAME = TestDBFile1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile1.mdf'),
FILEGROUP test2fg
( NAME = TestDBFile2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile2.ndf'),
FILEGROUP test3fg
( NAME = TestDBFile3,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile3.ndf'),
FILEGROUP test4fg
( NAME = TestDBFile4,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile4.ndf') ;
GO
Para cambiar a la nueva base de datos:
USE TestDatabase
GO
A. Crear una tabla que utiliza la compresión de fila
En el ejemplo siguiente se crea una tabla y se establece la compresión en ROW.
CREATE TABLE T1
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = ROW);
GO
B. Crear una tabla que utiliza la compresión de página
En el ejemplo siguiente se crea una tabla y se establece la compresión en PAGE.
CREATE TABLE T2
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = PAGE);
GO
C. Establecer la opción DATA_COMPRESSION en una tabla con particiones
En el ejemplo siguiente se usa la tabla TestDatabase que se crea utilizando el código proporcionado anteriormente en esta sección. En el ejemplo se crean un esquema de partición y una función de partición y, a continuación, se crea una tabla con particiones y se especifican las opciones de compresión para las particiones de la tabla. En este ejemplo, la partición 1 se configura para compresión ROW, y las particiones restantes se configuran para compresión PAGE.
Para crear una función de partición:
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO
Para crear un esquema de partición:
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO
Para crear una tabla con particiones que estén comprimidas:
CREATE TABLE PartitionTable1
(col1 int, col2 varchar(max))
ON myRangePS1 (col1)
WITH
(
DATA_COMPRESSION = ROW ON PARTITIONS (1),
DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)
);
GO
D. Establecer la opción DATA_COMPRESSION en una tabla con particiones
En el ejemplo siguiente se usa la base de datos del ejemplo C y se crea una tabla utilizando la sintaxis para particiones no contiguas.
CREATE TABLE PartitionTable2
(col1 int, col2 varchar(max))
ON myRangePS1 (col1)
WITH
(
DATA_COMPRESSION = ROW ON PARTITIONS (1,3),
DATA_COMPRESSION = NONE ON PARTITIONS (2,4)
);
GO
E. Modificar una tabla para cambiar la compresión
En el ejemplo siguiente se cambia la compresión de la tabla sin particiones creada en el ejemplo A.
ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
F. Modificar la compresión de una partición en una tabla con particiones
En el ejemplo siguiente se cambia la compresión de la tabla con particiones creada en el ejemplo C. La sintaxis REBUILD PARTITION = 1 hace que solo se vuelva a generar la partición número 1.
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) ;
GO
La misma operación que utiliza la sintaxis alternativa siguiente hace que se vuelvan a generar todas las particiones de la tabla.
ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO
G. Modificar la compresión de varias particiones en una tabla con particiones
La sintaxis REBUILD PARTITION = ... puede volver a generar solo una partición. Para volver a generar más de una partición se deben ejecutar varias instrucciones, o ejecutar el ejemplo siguiente para volver a generar todas las particiones, utilizando los valores de compresión actuales para las particiones no especificadas.
ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4)
) ;
GO
H. Modificar la compresión en un índice
En el ejemplo siguiente se utiliza la tabla creada en el ejemplo A y se crea un índice en la columna C2.
CREATE NONCLUSTERED INDEX IX_INDEX_1
ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ;
GO
Ejecute el código siguiente para cambiar el índice en la compresión de página:
ALTER INDEX IX_INDEX_1
ON T1
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO
I. Modificar la compresión de una sola partición en un índice con particiones
En el ejemplo siguiente se crea un índice en una tabla con particiones que utiliza la compresión de fila en todas las particiones del índice.
CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO
Para crear el índice de modo que use valores de compresión diferentes para particiones diferentes, utilice la sintaxis ON PARTITIONS. En el ejemplo siguiente se crea un índice en una tabla con particiones que utiliza la compresión de fila en la partición 1 del índice y la compresión de página en las particiones 2 a 4 del índice.
CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1),
DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4 ) ) ;
GO
En el ejemplo siguiente se cambia la compresión del índice con particiones.
ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL
WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO
J. Modificar la compresión de varias particiones en un índice con particiones
La sintaxis REBUILD PARTITION = ... puede volver a generar solo una partición. Para volver a generar más de una partición se deben ejecutar varias instrucciones, o ejecutar el ejemplo siguiente para volver a generar todas las particiones, utilizando los valores de compresión actuales para las particiones no especificadas.
ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4)
) ;
GO