Como ya se comentó en el anterior post, en SQL Server 2008 no solo tenemos compresión de datos a nivel de backup, sino también a nivel datos con compresión de página y compresión de fila. En esta ocasión voy a hablar sobre la compresión a nivel de datos.La idea de poder comprimir la información almacenada en la base de datos, evidentemente produce tanto un ahorro de espacio en disco como una mejora de rendimiento del servidor que trataremos mas adelante. Por otro lado, el mero hecho de poder comprimir tipos de datos antes considerados como estáticos, nos permite mitigar malas decisiones de diseño en nuestras bases de datos; pensemos por ejemplo en la típica situación de una mala elección de un tipo de datos ( char(255) ) por desconocimiento, que no se puede modificar por problemas de compatibilidad de las herramientas que las explotan.
Algo que debemos tener presente es que SQL Server garantiza que la descompresión de un dato siempre sea posible; esto quiere decir que el tamaño de una fila + sobrecarga por compresión no puede ser superior a 8060 bytes y eso lo garantizará el propio motor. Dicho de otro modo, la compresión de datos permite almacenar mas información por página, pero no por fila.
En este post no voy a hablar simplemente de lo que podemos ahorrarnos usando compresión, sino mas bien lo encamino a demostraros el por qué debemos pensarnos seriamente si nos conviene activarlo de una forma u otra en función de nuestros datos almacenados.
Además, conviene que en nuestro escenario, si tenemos volúmenes comprimidos donde almacenar la información de backups por ejemplo, las deshabilitemos y midamos el rendimiento ya que quizás ahora no sea necesario que se trate de comprimir algo que ya lo está. Para más información http://msdn.microsoft.com/en-us/library/ms190954.aspx apartado “Data Compression”
Compresión a nivel de fila
La compresión a nivel de fila se puede aplicar a:
- Tablas almacenadas como HEAP (sin índices clustered)
- Tablas almacenadas como índices agrupados
- Índices no agrupados
- Vistas indexadas
- Tablas e índices particionados (inclusive de forma independiente cada partición)
Algo que debemos conocer es que la compresión no se activa en los índices no agrupados de forma automática. Por ello, si queremos que el índice no agrupado se encuentre comprimido deberemos especificarlo. Por otro lado, si tenemos una tabla almacenada como un HEAP comprimida y le creamos un índice agrupado, la compresión en este caso si que se conserva.
Y por si alguien está pensando en que esto tuviera que ver con la fragmentación…no es así, por lo que no se te ocurra eliminar los planes de mantenimiento de re indexación y reorganización de índices 😉
 |
Existe una tabla que indica a qué tipos de datos se aplica esta compresión (varchar, por si lo estás pensando, no obtiene mejoria con este tipo de compresión).
Para más información sobre datos beneficiados por compresión de fila: http://msdn.microsoft.com/en-us/library/cc280576.aspx
Compresión a nivel de página
SQL Server 2008 nos permite ir mas allá en la compresión de datos gracias a la compresión de páginas. Se trata de un paso mas en el proceso de compresión que nos permite exprimir todavía mas el ratio de compresión conseguido. Pero ojo porque no todo es oro lo que reluce en este caso ya que el coste de CPU extra para conseguirlo puede no ser justificado si lo comparamos con la compresión de datos a nivel de fila. Este nivel de compresión solo está justificado para comprimir tablas con un alto índice de repetición de datos por página que puedan aplicar la compresión de prefijos y de diccionario.
Internamente, SQL Server realiza la compresión en estas 3 fases:
-
Compresión de fila (visto anteriormente)
-
Compresión mediante prefijos
-
Compresión mediante diccionario
Enseguida veremos en qué consisten los pasos 2 y 3, pero antes de nada me gustaría recalcar de nuevo que cuanta mas frecuencia de aparición, mayor eficiencia de almacenamiento y que se trata de una compresión a nivel de página por lo que únicamente cuando la página se encuentra llena, se produce compresión a nivel de página, sino únicamente se quedará comprimida mediante row compression.
Compresión mediante prefijos
Los prefijos se almacenan en un área de la página llamada anchor record y , cada columna posee su propia lista de prefijos lo cual quiere decir (y recalco) que no se expande a otras columnas. En la siguiente imagen se puede ver el paso de compresión mediante prefijos.
|
|
Quisiera recalcar la palabra prefijos, puesto que puede dar lugar a que pensemos que la compresión de página no funciona si no entendemos bien lo que realmente hace para conseguirla. En la demo de mas abajo entenderéis por qué digo esto.
Compresión mediante diccionario
Una vez aplicada la compresión mediante prefijos, se realiza una pasada para aplicar la compresión mediante diccionario, que reserva en la página, un diccionario donde almacenar los prefijos comunes y substituirlos por tokens. Esto es a nivel de página, por lo que cada página posee su propio diccionario y no es extensible a otras páginas.
 |
Demostraciones
Vamos a ensuciarnos las manos ya, una vez vista la parte teórica. Para ello, voy a utilizar un caso que me viene al pelo para demostraros que no siempre la compresión de página es la mejor y que siempre depende de la distribución de datos que tengamos en nuestras tablas. El script de la demostración lo tenéis mas abajo, voy a exponer sus resultados de entrada para no marearos:
Partiendo de una tabla con 1.000.000 de filas cuyas columnas son de tipos de datos bigint y varchar(200) y datos:
Vamos a ver su distribución de datos por páginas sin comprimir:
Si activamos la compresión a nivel de fila:
Si activamos la compresión a nivel de página:
Viendo la columna page_count, que nos indica el nº de páginas que posee el índice clústered (la tabla, vamos) nos damos cuenta que hay un descenso grande de páginas entre no tener la tabla comprimida y tenerla comprimida por fila, pero que únicamente hay una página de diferencia en el nivel hoja de aplicar compresión de página a solo aplicar compresión de fila.
Evidentemente estamos viendo que el sobrecoste del procesamiento de compresión de prefijos y de diccionario no está sirviendo prácticamente para nada (una página en un millon de filas…).
¿por qué la compresión de página no obtiene prácticamente ningún beneficio, comparado con la compresión a nivel de fila?
Pues ni mas ni menos que porque casi no hay prefijos comunes. Cuando veáis el script os daréis cuenta que la columna de tipo varchar posee un comienzo con muy poca repetición ( usa NEWID() ) y que luego nosotros rellenamos con caracteres repetidos al final. Puesto que no hay prefijos repetidos, no va a comprimir los carácteres finales y de poco nos sirve comprimir a nivel de página.
Por lo tanto, la única compresión que está realizándose es la compresión de fila de la columna id. Es más, solo está realizándose la compresión de la columna id, porque las columnas de tipo varchar no són comprimibles a nivel de fila (de nuevo os refiero a la url http://msdn.microsoft.com/en-us/library/cc280576.aspx)
¿Qué ocurre por tanto si existen prefijos comunes?
En este caso voy a hacer un poco el caso extremo en el que todas las filas tengan en una columna con un prefijo idéntico (ni que decir tiene que en mas de un sitio he visto eso y se suele llamar bug de aplicación cliente 😉
La tabla de antes, pero con prefijos comunes quedará así:
Sin comprimir:
Compresión a nivel de fila:
Compresión a nivel de página:
Nótese la grandísima diferencia en este caso de la compresión por página. Eso es debido como ya hemos comentado, a que ahora si que hay prefijos comunes y se pueden aplicar los algoritmos de compresión sobre el texto. En cualquier caso, quiero recalcar que la compresión a nivel de fila es exáctamente igual que en el caso anterior, solo ha aplicado sobre bigint, no sobre varchar.
Código con valores sin prefijos similares:
-- ECB:
--
use Northwind
go
if exists (select * from sys.tables where name = 'varchar_variable_dcha')
drop table dbo.varchar_variable_dcha
go
CREATE TABLE [dbo].varchar_variable_dcha(
id bigint identity primary key,
c varchar(200) NULL
)
go
declare @i int
set @i = 1
while @i<=10 -- 1.000.000 filas
begin
INSERT INTO dbo.varchar_variable_dcha
SELECT top (100000)
replace(cast(NEWID() as varchar(100)), '-','')
+ REPLICATE('a', 200-32)
FROM [Northwind].[dbo].[Orders]
CROSS JOIN [Northwind].[dbo].[Order Details]
print cast (@i as varchar(100))
set @i=@i+1
end
go
select top(10) * from dbo.varchar_variable_dcha
go
SELECT so.name, si.name, index_level, index_type_desc,
page_count, record_count,
avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM
sys.objects so
join sys.indexes si
on so.object_id = si.object_id
join sys.dm_db_index_physical_stats (
db_id (),
object_id('dbo.varchar_variable_dcha'),
NULL, NULL, 'DETAILED') v
on v.object_id = si.object_id
and v.index_id = si.index_id
order by index_level
go
ALTER TABLE dbo.varchar_variable_dcha
REBUILD WITH (DATA_COMPRESSION = ROW);
go
SELECT so.name, si.name, index_level, index_type_desc,
page_count, record_count,
avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM
sys.objects so
join sys.indexes si
on so.object_id = si.object_id
join sys.dm_db_index_physical_stats (
db_id (),
object_id('dbo.varchar_variable_dcha'),
NULL, NULL, 'DETAILED') v
on v.object_id = si.object_id
and v.index_id = si.index_id
order by index_level
go
ALTER TABLE dbo.varchar_variable_dcha
REBUILD WITH (DATA_COMPRESSION = PAGE);
go
SELECT so.name, si.name, index_level, index_type_desc,
page_count, record_count,
avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM
sys.objects so
join sys.indexes si
on so.object_id = si.object_id
join sys.dm_db_index_physical_stats (
db_id (),
object_id('dbo.varchar_variable_dcha'),
NULL, NULL, 'DETAILED') v
on v.object_id = si.object_id
and v.index_id = si.index_id
order by index_level
go
Código con valores de prefijos similares:
-- ECB:
--
use Northwind
go
if exists (select * from sys.tables where name = 'varchar_variable_dcha2')
drop table dbo.varchar_variable_dcha2
go
CREATE TABLE [dbo].varchar_variable_dcha2(
id bigint identity primary key,
c varchar(200) NULL
)
go
declare @i int
set @i = 1
while @i<=10 -- 1.000.000 filas
begin
INSERT INTO dbo.varchar_variable_dcha2
SELECT top (100000)
REPLICATE('a', 200-32)
FROM [Northwind].[dbo].[Orders]
CROSS JOIN [Northwind].[dbo].[Order Details]
print cast (@i as varchar(100))
set @i=@i+1
end
go
select top(10) * from dbo.varchar_variable_dcha2
go
SELECT so.name, si.name, index_level, index_type_desc,
page_count, record_count,
avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM
sys.objects so
join sys.indexes si
on so.object_id = si.object_id
join sys.dm_db_index_physical_stats (
db_id (),
object_id('dbo.varchar_variable_dcha2'),
NULL, NULL, 'DETAILED') v
on v.object_id = si.object_id
and v.index_id = si.index_id
order by index_level
go
--SELECT cast(table_name as varchar(30)) table_name
-- , index_level, cast(index_type_desc as varchar(30)) index_type_desc
-- , page_count, record_count
-- , avg_fragmentation_in_percent avg_frag
-- , avg_page_space_used_in_percent avg_pused
--FROM dbo.espacio_usado
--ORDER BY index_level, table_name
--go
ALTER TABLE dbo.varchar_variable_dcha2
REBUILD WITH (DATA_COMPRESSION = ROW);
go
SELECT so.name, si.name, index_level, index_type_desc,
page_count, record_count,
avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM
sys.objects so
join sys.indexes si
on so.object_id = si.object_id
join sys.dm_db_index_physical_stats (
db_id (),
object_id('dbo.varchar_variable_dcha2'),
NULL, NULL, 'DETAILED') v
on v.object_id = si.object_id
and v.index_id = si.index_id
order by index_level
go
ALTER TABLE dbo.varchar_variable_dcha2
REBUILD WITH (DATA_COMPRESSION = PAGE);
go
SELECT so.name, si.name, index_level, index_type_desc,
page_count, record_count,
avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM
sys.objects so
join sys.indexes si
on so.object_id = si.object_id
join sys.dm_db_index_physical_stats (
db_id (),
object_id('dbo.varchar_variable_dcha2'),
NULL, NULL, 'DETAILED') v
on v.object_id = si.object_id
and v.index_id = si.index_id
order by index_level
go
Resumen
La compresión de datos a nivel de fila no afecta a los siguientes tipos de datos:
- Varchar,nvarchar,image,text,ntext
- XML, FILESTREAM, varbinary y sql_variant
- Date, time (ya extremadamente compactos)
El beneficio se obtiene por tanto en otros tipos de datos:
- datetime, datetime2, datetimeoffset: ahorra 2 bytes si no almacena segundos
- char: solo ocupa lo necesario (como varchar)
- int, bigint,float, real,…: solo usa lo necesario
- binary: no almacena los ceros que puede evitar
- …
Además, en todos los tipos de datos, NULL y 0 no ocupan ningún byte
|
|
Por último y no menos importante, la compresión de datos solo implica tareas administrativas de SQL Server, nuestras aplicaciones son completamente agnósticas a nuestros cambios 😉
1 comment
Excelente post, me sirvió para entender los tipos de compresión.