Using data compression for the product library workspace in SQL Server (Production Mapping)

La compresión de filas y páginas en tablas e índices permiten ahorrar espacio de almacenamiento reduciendo el tamaño de la base de datos. No obstante, la compresión de datos tiene el inconveniente de incrementar el uso de la CPU, ya que los datos deben ser comprimidos o descomprimidos al acceder a los mismos. No es posible utilizar la compresión de datos en tablas del sistema, y solamente las ediciones Enterprise y Developer de SQL Server 2012 admiten la compresión de datos.

Es posible configurar la compresión de datos en:

• Tablas agrupadas
• Tablas dinámicas (tablas sin un índice agrupado)
• Índices no agrupados
• Vistas indexadas
• Particiones individuales de una tabla o de un índice particionados

Existen tres formas de compresión de datos que se pueden utilizar con SQL Server 2012: compresión de filas, compresión de unicode y compresión de páginas.

Consulte información más detallada en Tablas dinámicas (tablas sin índices agrupados), en la biblioteca de MSDN.

Row-level compression

La compresión a nivel de fila funciona utilizando formatos de almacenamiento más eficientes para datos de longitud fija.

La compresión a nivel de fila utiliza las siguientes estrategias para ahorrar espacio:

• Almacenamiento de datos numéricos de longitud fija y tipos de datos CHAR como si fuesen tipos de datos de longitud variable
• No almacenamiento de valores NULL o 0 (cero)
• Reducción de los metadatos necesarios para almacenar datos

Aunque reduce la cantidad de espacio que utilizan los datos, la compresión a nivel de fila no ofrece las mejoras de almacenamiento de la compresión a nivel de página. La ventaja de la compresión a nivel de fila es que requiere menos uso de la CPU que la compresión a nivel de página. Para comprimir una tabla con compresión a nivel de fila se emplea la siguiente sintaxis:

ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)

Por ejemplo, para volver a crear todas las particiones de la tabla prodlib.TableA de la base de datos prodlibdb con la compresión a nivel de filas, utilice la siguiente consulta:

USE [prodlibdb]
ALTER TABLE [prodlib].[TableA] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW)

Para configurar un índice con compresión a nivel de fila se emplea la siguiente sintaxis:

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)

Consulte más información acerca de la compresión a nivel de fila, consulte Implementación de la compresión a nivel de fila en la biblioteca de MSDN.

Unicode compression

La compresión Unicode permite que el motor de la base de datos comprima valores Unicode almacenados en objetos comprimidos a nivel de página o de fila. La compresión Unicode puede utilizarse con los tipos de datos nchar(n) y nvarchar(n) de longitud fija. La compresión Unicode será utilizada automáticamente cuando sea adecuado si está activada la compresión a nivel de fila y de página.

Consulte información más detallada acerca de la compresión Unicode en Implementación de la compresión Unicode, en la biblioteca de MSDN.

Page-level compression

La compresión a nivel de página comprime los daos almacenando una sola vez los valores repetidos y los prefijos comunes y, a continuación, haciendo referencias a aquellos valores procedentes de otras ubicaciones dentro de la tabla. Cuando se aplica la compresión a nivel de página a una tabla, también se aplican las técnicas de compresión a nivel de fila. La compresión a nivel de página utiliza las siguientes estrategias:

• La compresión a nivel de fila se aplica para maximizar el número de filas guardadas en una página.
• La compresión de prefijos de columna se aplica sustituyendo patrones de datos repetidos por referencias. Estos datos se almacenan en el encabezado de la página.
• La compresión de diccionario detecta valores repetidos y guarda esta información en el encabezado de la página.

Las ventajas de la compresión de página dependerán del tipo de datos comprimidos. Los datos que incorporan numerosos valores repetidos serán más comprimidos que los que incorporan más valores únicos. Para aplicar la compresión a nivel de página se utiliza la siguiente sintaxis general:

ALTER TABLE name REBUILD WITH (DATA_COMPRESSION=PAGE)

Por ejemplo, para volver a crear todas las particiones de la tabla prodlib.TableA de la base de datos prodlibdb con la compresión de página, utilice la siguiente consulta:

USE [prodlibdb]
ALTER TABLE [prodlib].[TableA] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE)

Para configurar un índice con compresión a nivel de página se emplea la siguiente sintaxis:

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=PAGE)

Consulte más información acerca de la compresión a nivel de página, consulte Implementación de la compresión a nivel de página en la biblioteca de MSDN.

Si las tablas o índices están particionados, podrá configurar la compresión para cada partición. Si divide una partición utilizando la instrucción ALTER PARTITION, las nuevas particiones heredarán el atributo de compresión de datos de la partición original. Si se combinan dos particiones, la partición resultante tendrá el atributo de compresión de la partición de destino. Aunque la compresión permite almacenar más filas en una página, no modifica el tamaño máximo de fila de una tabla o índice. No será posible habilitan una tabla para compresión si el tamaño máximo de fila y la sobrecarga de compresión exceden de 8.060 bytes. El ajuste predeterminado de compresión de los índices es NONE; la propiedad de compresión de los índices debe especificarse al crearlos. Los índices no agrupados no heredan la propiedad de compresión de la tabla, aunque los agrupados creados en una tabla dinámica heredan el estado de compresión de la misma. La compresión de datos se aplica solamente al origen, por lo que al exportar datos desde un origen, la salida de datos de SQL Server será en formato de fila no comprimida. La importación de datos no comprimidos a una tabla de destino habilitada para compresión comprimirá los datos.

Consulte información más detallada acerca de la compresión de datos en Compresión de datos, en la biblioteca de MSDN.

Para cambiar los ajustes de compresión de tablas e índices con el Asistente para la Compresión de datos, efectúe el siguiente procedimiento:

Pasos:

1. Inicie SQL Server Management Studio.
2. Haga clic con el secundario en la tabla o índice que desee comprimir, haga clic en Almacenamiento y, por último, haga clic en Administrar compresión.

   De este modo se abrirá el cuadro de diálogo Bienvenido al Asistente para la Compresión de datos.

3. Haga clic en Siguiente.

   Seguidamente se abrirá el cuadro de diálogo Seleccione el tipo de compresión.

4. Seleccione el tipo de compresión.

   Podrá optar por utilizar el mismo tipo de compresión en todas las particiones, o bien elegir la compresión a nivel de fila, de página, por partición o ninguno.

5. Haga clic en Calcular para determinar la diferencia entre el uso del espacio actual y el uso comprimido.

   Seguidamente se abrirá el cuadro de diálogo Seleccione una opción de salida.

6. Selecciones si desea crear una secuencia de comandos, ejecutar la operación de inmediato o ejecutarla siguiendo un programa.
7. Haga clic en Siguiente.
8. Haga clic en Finalizar.

Consulte información más detallada acerca de la compresión de datos en la Ayuda del Asistente para la Compresión de datos, en la biblioteca de MSDN.

Estimating compression

La mejor manera de determinar las ventajas de la compresión en un objeto es utilizar el procedimiento sp_estimate_data_compression_savings stored. Las ventajas de la compresión dependerán de factores tales como la singularidad de los datos. El procedimiento sp_estimate_data_compression_savings stored está disponible solamente en la edición Enterprise de SQL Server 2012.

La sintaxis del procedimiento de almacenamiento es la siguiente:

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'

Por ejemplo, para configurar un cálculo de las ventajas de la compresión a nivel de filas en la tabla prodlib.TableA de la base de datos prodlibdb, ejecute la siguiente instrucción de Transact-SQL:

USE prodlibdb;
GO
EXEC sp_estimate_data_compression_savings 'prodlib', 'TableA', NULL, NULL,
'ROW';
GO

Para configurar un cálculo de las ventajas de compresión que comporta la compresión de páginas en la misma tabla, ejecute la siguiente declaración Transact-SQL:

USE prodlibdb;
GO
EXEC sp_estimate_data_compression_savings 'prodlib', 'TableA', NULL, NULL,
'PAGE';
GO

Consulte información sobre cómo estimar el ahorro que supondrá la compresión en sp_estimate_data_compression_savings (Transact-SQL), en la biblioteca de MSDN.

Puede usar la misma secuencia de comandos SQL que figura a continuación para las tablas y los índices de usuario prodlib, así como para generar las instrucciones SQL para definir la compresión de páginas de las tablas e índices.

--Verify PRODLIB Schema Storage
USE [prodlibdb]
GO
--List all tables
SELECT USER_NAME(o.uid) [owner], o.name,o.id,o.type,o.status
FROM sysobjects o
WHERE USER_NAME(o.uid) = 'prodlib'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO
--List all indexes
SELECT USER_NAME(o.uid) [owner], OBJECT_NAME(i.id) [table], i.name [index],o.type [type]
FROM sysindexes i inner join sysobjects o ON i.id = o.id
WHERE USER_NAME(o.uid) = 'prodlib'
AND o.type <> 'S' and o.type = 'U' and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name;
GO
--Table page compression
--Example:
ALTER TABLE PRODLIB.PL_NEWCLASS_AOI 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
--Generate script to set table page compression:
SELECT 'ALTER TABLE ' + USER_NAME(o.uid) + '.' + o.name + ' REBUILD WITH (DATA_COMPRESSION = PAGE);' [TXTSQL]
FROM sysobjects o
WHERE USER_NAME(o.uid) = 'prodlib'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO
--Index page compression
--Example:
ALTER INDEX R222_pk 
ON PRODLIB.PL_NEWCLASS_AOI
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO
--Generate script to set index page compression:
SELECT 'ALTER INDEX ' + i.name + ' ON ' + USER_NAME(o.uid) + '.' + OBJECT_NAME(i.id) +
       ' REBUILD WITH ( DATA_COMPRESSION = PAGE );' [TXTSQL]
FROM sysindexes i inner join sysobjects o ON i.id = o.id
WHERE USER_NAME(o.uid) = 'prodlib'
AND o.type <> 'S' and o.type = 'U' and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name;
GO

Compression and TDE

El cifrado del archivo de base de datos se realiza en el nivel de página. Las páginas de una base de datos cifrada se cifran antes de que se escriban en el disco y se descifran cuando se leen en la memoria. TDE no aumenta el tamaño de la base de datos cifrada.

Consulte información más detallada en SQL Server 2012 - Cifrado transparente de datos (TDE), en la biblioteca de MSDN.

Los datos cifrados se comprimen significativamente menos que los no cifrados equivalentes. Si se utiliza TDE para cifrar una base de datos, la compresión de la copia de seguridad no podrá comprimir significativamente el almacenamiento de la copia de seguridad. Por consiguiente, no se recomienda utilizar conjuntamente TDE y la compresión de copia de seguridad.

Consulte información más detallada en SQL Server 2012 – Compresión de datos, en la biblioteca de MSDN.