¡Hola, chicos!
En esta publicación compartiré contigo un script muy práctico para identificar tablas e índices sin compresión de datos y aplicar compresión de datos de tipo página a las tablas devueltas.

Usando la consulta que compartiré en este artículo, identificaremos las tablas e índices en modo RowStore que no usan compresión de datos.

Vale recordar que hasta la versión 2016, esta función de compresión de datos era exclusiva de la versión Enterprise (además de Trial y Developer), por lo que si tu versión es inferior a 2016 y estás en la edición Standard, no podrás utilizar la compresión de datos que describo en este artículo.

¿Por qué utilizar la compresión de datos?

La compresión de datos ofrece varios beneficios. Ahorra espacio en disco y puede ayudar a mejorar el rendimiento de determinadas cargas de trabajo.

Los beneficios de la compresión de datos tienen el costo de un mayor uso de la CPU para comprimir y descomprimir datos, por lo que es importante comprender las características de la carga de trabajo en una tabla antes de decidir una estrategia de compresión.

La compresión de datos proporciona flexibilidad en términos de niveles de compresión (fila o página) y los objetos que puede comprimir (tabla, índice, partición). Esto le permite ajustar la compresión en función de las características de los datos y la carga de trabajo.

Otra ventaja importante de la compresión de datos es que funciona de forma transparente para las aplicaciones y funciona bien con otras funciones de SQL Server, como TDE y compresión de copias de seguridad.

Las páginas comprimidas se mantienen comprimidas en el disco y permanecen comprimidas cuando se leen en la memoria. Los datos se descomprimen (no toda la página, sino solo los valores de datos de interés) cuando cumplen una de las siguientes condiciones:

  • Se lee para filtrar, ordenar, unir, como parte de una respuesta a una consulta.
  • Se actualiza mediante una aplicación.

No hay ninguna copia en memoria sin comprimir de la página comprimida. La descompresión de datos consume CPU.

Sin embargo, debido a que los datos comprimidos utilizan menos páginas de datos, también ahorran:

  • E/S física: debido a que la E/S física es costosa desde la perspectiva de la carga de trabajo, la E/S física reducida generalmente genera mayores ahorros que el costo adicional de CPU que supone comprimir y descomprimir los datos. Tenga en cuenta que la E/S física se guarda porque se leen o escriben menos datos en el disco y porque pueden permanecer más datos almacenados en caché en la memoria del grupo de búfer.
  • E/S lógica (si los datos están en la memoria): debido a que la E/S lógica consume CPU, la E/S lógica reducida a veces puede compensar el costo de la CPU para comprimir y descomprimir los datos.
Para comprender mejor qué es la compresión de datos, te sugiero leer el artículo. Comprimir todas las tablas de una base de datos en SQL Server.

¿Qué es el nivel de compresión de filas?

Esta característica de compresión tiene en cuenta el tipo de estructuras de datos variables que definen una columna. El nivel de compresión de fila es un nivel de compresión que no utiliza ningún algoritmo de compresión.

El objetivo principal del nivel de compresión de filas es reducir el almacenamiento de datos de tipo fijo, es decir, cuando habilita la compresión de filas, solo está cambiando el formato de almacenamiento físico de los datos asociados con un tipo de datos.

La compresión de nivel de fila amplía el formato de almacenamiento vardecimal (entero, decimal, flotante, fecha y hora, dinero, etc.) al almacenar datos de todos los tipos de longitud fija en un formato de almacenamiento de longitud variable. Este tipo de compresión eliminará los bytes adicionales en el tipo de datos fijo.

Los valores NULL y 0, en todos los tipos de datos, están optimizados para no ocupar ningún byte.

Por ejemplo, tenemos una columna CHAR(100), que utiliza el nivel de compresión de filas. Esta columna solo utilizará la cantidad de almacenamiento definida por los datos. ¿Como esto? Guardemos la frase "SQL Server 2008" en la columna. Esta frase contiene sólo 15 caracteres y sólo se almacenan estos 15 caracteres, a diferencia de los 100 que definió la columna, por lo que tienes un ahorro del 85% en espacio de almacenamiento.

¿Qué es el nivel de compresión de la página?

En versiones anteriores de SQL Server, cada valor se almacenaba en la página, independientemente de si el mismo valor ya había aparecido en la misma columna para otras filas de una página. En SQL Server 2008, el valor redundante o duplicado se almacenará sólo una vez dentro de la página y se hará referencia a él en todas las demás apariciones, por lo que tenemos el nivel de compresión de página.

Básicamente, el nivel de compresión de página es un superconjunto de compresión de FILAS y tiene en cuenta los datos redundantes en una o más filas de una página determinada. También utiliza compresión de prefijos y diccionarios.

El método Nivel de compresión de página es más inteligente porque permite compartir datos comunes entre filas de una página determinada.
Este tipo de compresión utiliza las siguientes técnicas:

  • COMPRESIÓN DE FILAS: Ya visto arriba.
  • COMPRESIÓN DE PREFIJOS: Para cada columna de una página se identifican prefijos duplicados. Estos prefijos se almacenan en los encabezados de Compresión de información (CI), que se encuentran después del encabezado de la página. Se asigna un número de referencia a estos prefijos y este número de referencia se utiliza siempre que se utilicen estos prefijos, lo que disminuye la cantidad de bytes utilizados.
  • COMPRESIÓN DEL DICCIONARIO: Busca valores duplicados fuera de la página y los almacena en el CI. La principal diferencia entre la compresión de prefijo y la compresión de diccionario es que el prefijo está restringido a una sola columna, mientras que el diccionario se aplica a toda la página.
    Una vez completada la compresión de prefijos, se aplica la compresión de diccionario, busca valores repetidos en cualquier lugar de la página y los almacena en el área CI. A diferencia de la compresión de prefijos, la compresión de diccionarios no se limita a una columna y puede reemplazar valores repetidos que aparecen en cualquier lugar de una página.

Script para identificar tablas e índices sin compresión de datos.

Para identificar tablas e índices sin compresión de datos y aplicar compresión de datos, puede utilizar el siguiente script:

SELECT DISTINCT 
    C.[name] AS [Schema],
    A.[name] AS Tabela,
    NULL AS Indice,
    'ALTER TABLE [' + C.[name] + '].[' + A.[name] + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' AS Comando
FROM 
    sys.tables                   A
    INNER JOIN sys.partitions    B   ON A.[object_id] = B.[object_id]
    INNER JOIN sys.schemas       C   ON A.[schema_id] = C.[schema_id]
WHERE 
    B.data_compression_desc = 'NONE'
    AND B.index_id = 0 -- HEAP
    AND A.[type] = 'U'
    
UNION
 
SELECT DISTINCT 
    C.[name] AS [Schema],
    B.[name] AS Tabela,
    A.[name] AS Indice,
    'ALTER INDEX [' + A.[name] + '] ON [' + C.[name] + '].[' + B.[name] + '] REBUILD PARTITION = ALL WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE)'
FROM 
    sys.indexes                  A
    INNER JOIN sys.tables        B   ON A.[object_id] = B.[object_id]
    INNER JOIN sys.schemas       C   ON B.[schema_id] = C.[schema_id]
    INNER JOIN sys.partitions    D   ON A.[object_id] = D.[object_id] AND A.index_id = D.index_id
WHERE
    D.data_compression_desc =  'NONE'
    AND D.index_id <> 0
    AND A.[type] IN (1, 2) -- CLUSTERED e NONCLUSTERED (Rowstore)
    AND B.[type] = 'U'
ORDER BY
    Tabela,
    Indice

Resultado:

¿Compresión de datos de página o fila?

Documentación de Microsoft (enlace aquí) nos da algunos consejos para intentar identificar si el índice debe crearse como compresión de página o compresión de filas.

Ejecute el siguiente script:

SELECT
    [o].[name] AS [Table_Name],
    [x].[name] AS [Index_Name],
    [i].[partition_number] AS [Partition],
    [i].[index_id] AS [Index_ID],
    [x].[type_desc] AS [Index_Type],
    [i].[leaf_update_count] * 100.0 / ([i].[range_scan_count] + [i].[leaf_insert_count] + [i].[leaf_delete_count] + [i].[leaf_update_count] + [i].[leaf_page_merge_count] + [i].[singleton_lookup_count]) AS [Percent_Update],
    [i].[range_scan_count] * 100.0 / ([i].range_scan_count + [i].leaf_insert_count + [i].leaf_delete_count + [i].leaf_update_count + [i].leaf_page_merge_count + [i].singleton_lookup_count) AS [Percent_Scan]
FROM
    [sys].dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS [i]
    JOIN [sys].[objects] AS [o] ON [o].[object_id] = [i].[object_id]
    JOIN [sys].[indexes] AS [x] ON [x].[object_id] = [i].[object_id] AND [x].[index_id] = [i].[index_id]
WHERE
    ([i].[range_scan_count] + [i].[leaf_insert_count] + [i].[leaf_delete_count] + [i].[leaf_update_count] + [i].[leaf_page_merge_count] + [i].[singleton_lookup_count]) <> 0
    AND OBJECTPROPERTY([i].[object_id], 'IsUserTable') = 1
ORDER BY
    [Percent_Update] ASC;

Cuanto menor sea el valor de la columna "Percent_Update" o mayor sea el valor de la columna "Percent_Scan", mejor candidato será este índice para utilizar la compresión de páginas.

Alguna información para ayudarte a decidir:

  • La compresión de páginas busca patrones repetidos, como vimos en su funcionamiento. Entonces, si sus datos no tienen patrones repetidos, no obtendrá muchos beneficios adicionales de la compresión de páginas. Verá una utilización adicional de la CPU, pero probablemente no verá una gran ganancia de rendimiento para que valga la pena.
  • La sobrecarga de CPU de la compresión de filas es generalmente mínima (generalmente menor o igual al 10%). Si la compresión de filas genera ahorros de espacio y el sistema puede soportar un aumento del 10 % en el uso de la CPU, todos los datos se deben comprimir en filas.
  • En promedio, la compresión de filas requiere 1,5 veces el tiempo de CPU utilizado para reconstruir un índice, mientras que la compresión de páginas requiere de 2 a 5 veces el tiempo de CPU utilizado para reconstruir un índice.
  • Ejemplos de tablas que son buenas candidatas para la compresión de páginas son las tablas de registro o de auditoría, que se escriben una vez y rara vez se leen.
  • Utilice la compresión de páginas cuando el objetivo sea reducir el espacio en disco, ya que la compresión de páginas generalmente comprimirá más que la compresión de líneas.
  • Para obtener la máxima compresión disponible, utilice la compresión COLUMNSTORE_ARCHIVE, pero no tiene un buen rendimiento de lectura.

¡Eso es todo, amigos!
Espero que te haya gustado este consejo y ¡hasta la próxima!