PAGE COMPRESSION SQL SERVER 2008



A Microsoft introduziu a compressão de tabelas e índices a partir do SQL SERVER 2008 para as edições enterprise e developer. A mesma  pode ser implementada em dois níveis: linha e página. Hoje irei comentar sobre a compressão de dados por página a qual utiliza-se de duas técnicas, a Prefix Compression e Dictionary Compression.

Vamos criar algumas tabelas e testar a compressão das páginas em seus variados aspectos:

Vamos criar uma tabela sem a opção de compressão e adicionar alguns dados a mesma:

/****** Object:  Table [dbo].[NoNCompressed Table]   
        Script Date: 06/12/2009 02:24:23 ******/
IF  EXISTS (SELECT * FROM sys.objects
        WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table]')
        AND type in (N'U'))
DROP TABLE [dbo].[NoNCompressed Table]
GO
CREATE TABLE [NoNCompressed Table]
(id int, FName char(100), LName char(100))

-- Add 10,000 rows

declare @n int
set @n=0
while @n<=10000
begin
insert into [NoNCompressed Table] values
(1,'Adam','Smith'),(2,'Maria','carter'),
(3,'Walter','zenegger'),(4,'Marianne','smithsonian')
set @n=@n+1
end
GO

Agora vamos verificar o tamanho da mesma com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table]

Resultado:

name,rows,reserved,data,index_size,unused

NoNCompressed Table,40004,8456 KB,8424 KB,8 KB,24 KB

Vamos criar agora uma tabela usando a compressão e adicionar o mesmo volume de dados da tabela anterior:

/****** Object:  Table [dbo].[Compressed Table]   
        Script Date: 06/12/2009 02:24:57 ******/
IF  EXISTS (SELECT * FROM sys.objects
        WHERE object_id = OBJECT_ID(N'[dbo].[Compressed Table]')
        AND type in (N'U'))
DROP TABLE [dbo].[Compressed Table]
GO
CREATE TABLE [Compressed Table]
(id int, FName char(100), LName char(100)) with
(Data_compression = PAGE)
declare @n int
set @n=0

-- Add 10,000 rows

while @n<=10000
begin
insert into [Compressed Table] values
(1,'Adam','Smith'),(2,'Maria','carter'),
(3,'Walter','zenegger'),(4,'Marianne','smithsonian')
set @n=@n+1
end
GO

Vamos verificar seu tamanho com o comando abaixo:

EXEC sp_spaceused [Compressed Table]

Resultado:

name,rows,reserved,data,index_size,unused
Compressed Table,40004,        904 KB,896 KB,8 KB,0 KB

O resultado mostra que o tamanho do reservado e das colunas de dados são muito menores na tabela com compressão do que na tabela sem compressão. Se deseja estimar o quanto foi efetiva a compressão em sua tabela, utilize o comando abaixo:
 
Exec sp_estimate_data_compression_savings 'dbo','NoNCompressed Table',NULL,NULL,'PAGE'

Exec sp_estimate_data_compression_savings 'dbo','NoNCompressed Table',NULL,NULL,'ROW'

Quando esta procedure é executada,a mesma pega uma amostragem dos dados e carrega na tempdb, então, ela realiza a compressão no que foi carregado na tempdb e gera uma estimativa.

Você também pode comprimir uma tabela que já existe através do comando ALTER TABLE. Vamos simular isto criando outra tabela sem compressão. Vamos adicionar dados similares aos acima na mesma:

/****** Object:  Table [dbo].[NoNCompressed Table]    Script Date: 06/12/2009 02:24:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table2]') AND type in (N'U'))
DROP TABLE [dbo].[NoNCompressed Table2]
GO
CREATE TABLE [NoNCompressed Table2]
(id int, FName char(100), LName char(100))


declare @n int
set @n=0
while @n<=10000
begin
insert into [NoNCompressed Table2] values
(1,'Adam','Smith'),(2,'Maria','carter'),
(3,'Walter','zenegger'),(4,'Marianne','smithsonian')
set @n=@n+1
end
GO

 Vamos verificar seu tamanho com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table2]

 Resultado:

name,rows,reserved,data,index_size,unused
NoNCompressed Table2,40004,           8456 KB,8424 KB,8 KB,24 KB

 Vamos adicionar a compressão com o comando ALTER TABLE conforme abaixo:

ALTER TABLE [NoNCompressed Table2]
REBUILD WITH (DATA_COMPRESSION = PAGE );

Agora vamos verificar seu tamanho pós-compressão:

EXEC sp_spaceused [NONCompressed Table2]

Resultado:

name,rows,reserved,data,index_size,unused

NoNCompressed Table2,40004,           592 KB,472 KB,8 KB,112 KB

 A compressão também pode ser utilizada em tabelas com índices clustered e nonclustered, vamos exemplificar:
 Crie uma tabela sem compressão com uma grande quantidade de dados através do script abaixo:

/****** Object:  Table [dbo].[NoNCompressed Table3]    Script Date: 06/13/2009 02:24:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table3]') AND type in (N'U'))
DROP TABLE [dbo].[NoNCompressed Table3]
GO
CREATE TABLE [NoNCompressed Table3]
(id int, FName char(100), LName char(100))


declare @n int
set @n=0
while @n<=10000
begin
insert into [NoNCompressed Table3] values
(1,'Adam','Smith'),(2,'Maria','carter'),(3,'Walter','zenegger')
set @n=@n+1
end
GO

 Vamos verificar seu tamanho com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table3]

 Resultado:

name,rows,reserved,data,index_size,unused

NoNCompressed Table3,30003,           6472 KB,6408 KB,8 KB,56 KB

Adicione um índice clustered na mesma através do comando abaixo:

create clustered index [NoNCompressed Table3_Cl_Idx] on
[NoNCompressed Table3](ID)

Vamos verificar seu tamanho com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table3]

Resultado:

name,rows,reserved,data,index_size,unused

NoNCompressed Table3,30003,           6784 KB,6672 KB,64 KB48 KB

Adicione um índice Nonclustered na tabela através do comando abaixo:

create Nonclustered index [NoNCompressed Table3_NonCl_Idx] on
[NoNCompressed Table3](Fname) 

Vamos verificar agora seu tamanho com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table3]

Resultado:

name,rows,reserved,data,index_size,unused
NoNCompressed Table3,30003,10656 KB,  6672 KB,3752 KB,232 KB

Vamos adicionar a compressão na tabela através dos comandos abaixo:

ALTER TABLE [NoNCompressed Table3]
REBUILD WITH (DATA_COMPRESSION = PAGE );

ALTER INDEX [NoNCompressed Table3_Cl_Idx] on [NoNCompressed Table3]
REBUILD WITH (DATA_COMPRESSION = PAGE );


ALTER INDEX [NoNCompressed Table3_NonCl_Idx] on [NoNCompressed Table3]
REBUILD WITH (DATA_COMPRESSION = PAGE );

Vamos verificar agora seu tamanho pós-compressão com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table3]

Resultado:

name,rows,reserved,data,index_size,unused
NoNCompressed Table3,30003,           808 KB,336 KB,320 KB,152 KB

Com os resultados obtidos é possível verificar a eficácia da compressão nas tabelas! Para a implementação desta nova funcionalidade existem algumas restrições, no entanto não irei entrar detalhes neste post, posteriormente irei comentar sobre as mesmas e irei falar também da outra forma de compressão, a compressão de linhas (“row compression”).

 Espero que tenham gostado do post!

 Um abraço!

Nenhum comentário:

Postar um comentário