Atenç�o: esse artigo tem uma palestra complementar. Clique e assista!

De que trata o artigo:

Neste artigo veremos as formas de compactaç�o de dados existente no Microsoft SQL Server 2008. Em seguida, demonstraremos como utilizar cada uma destas formas, com base em duas tabelas contendo dados fict�cios.

Para que serve:

A compactaç�o de dados tem como objetivo proporcionar um melhor dimensionamento de espaço em disco necess�rio para alocar dados existentes em tabelas do Microsoft SQL Server 2008. Procurando evitar qualquer tipo de aumento no tempo de processamento necess�rio para armazenar ou consultar estes dados compactados.

Em que situaç�o o tema �til:

A compactaç�o de dados � uma t�cnica muito �til para ambientes com falta de espaço em disco, mas que possuem uma grande necessidade de armazenamento de dados.

Sua utilizaç�o reflete diretamente na perda de tempo e esforço necess�rio para alocar os dados armazenados nas tabelas ou �ndices que utilizam compactaç�o em linha de linha ou p�ginas de dados.

Al�m disso, a compactaç�o de dados pode trazer alguns benef�cios em relaç�o � diminuiç�o da fragmentaç�o de dados armazenados em uma tabela que esteja utilizando o n�vel de compactaç�o em linha.

Quando falamos em armazenamento de dados, sempre pensamos na necessidade que temos em guardar uma informaç�o em local seguro, confi�vel e �ntegro. A evoluç�o da capacidade de armazenamento de dados ocorrido nos �ltimos anos ofereceu �s empresas recursos que permitem armazenar e gerenciar grandes volumes de informaç�o, independente da sua origem. Acompanhando este crescimento e evoluç�o, as empresas desenvolvedoras de Sistemas Gerenciadores de Bancos de Dados identificaram como pr�-requisito para seus produtos a capacidade de armazenar qualquer tipo de informaç�o, sendo elas arquivos de �udio, v�deo, apresentaç�es, ou simplesmente um dado.

Mas o aumento da capacidade de armazenamento tamb�m obrigou estas empresas a se preocuparem com o gerenciamento deste volume de informaç�es, e, ainda mais, a buscarem uma melhor forma para alocar informaç�es evitando desperd�cios da capacidade de armazenamento, sem ocasionar aumento no tempo de processamento.

Com base no atual momento tecnol�gico e procurando manter seus produtos atualizados, a Microsoft decidiu fazer algumas mudanças no formato de compactaç�o de dados realizada pelo SQL Server 2008, oferecendo suporte nativo a esta funcionalidade.

Utilizando as funcionalidades de compactaç�o de dados existentes no SQL Server 2008, torna-se poss�vel realizar esta tarefa economizando espaço de armazenamento, mas, em algumas situaç�es, ocasionando um pequeno aumento de processamento e tempo de execuç�o.

Neste artigo, iremos apresentar esta nova funcionalidade, provida a partir das vers�es Standard e Enterprise do SQL Server 2008.

Conhecendo a compactaç�o de dados

A possibilidade de compactaç�o de dados no SQL Server surgiu no lançamento do Service Pack 2 para o SQL Server 2005, com base no formato de armazenamento vardecimal (sendo um formato de armazenamento, n�o um tipo de dados).

Anteriormente o Microsoft SQL Server n�o apresentava recursos relacionados a compactaç�o de dados. Analisar a melhor forma para se alocar um dado em uma tabela sem gerar fragmentaç�o ou desperd�cio de espaço em disco era de total responsabilidade e dever do administrador de banco de dados (DBA) ou administrador de dados (DA).

O SQL Server 2008 oferece suporte a compactaç�o de linha e de p�gina para tabelas e �ndices. A compactaç�o de dados pode ser configurada para os seguintes objetos do banco de dados:

� Uma tabela inteira que � armazenada como um heap;

� Uma tabela inteira que � armazenada como um �ndice clusterizado;

� Um �ndice n�o clusterizado inteiro;

� Uma view indexada inteira.

A partir SQL Server 2005 Service Pack 2 e vers�es posteriores, tipos de dados como decimal e numeric tornaram-se mais vers�teis e compat�veis com o formato de armazenamento vardecimal. Este formato de dados possibilita a reduç�o do tamanho ocupado pelos dados, podendo ocasionar um pequeno aumento no tempo de processamento.

Quando utilizamos vardecimal o SQL Server dever� verificar inicialmente o tamanho da informaç�o que ser� armazenada e, logo ap�s, estabelecer o quanto de espaço ser� necess�rio para sua alocaç�o. Caso o dado que ser� armazenado esteja compactado em n�vel de p�gina, o SQL Server ter� a miss�o de identificar a melhor posiç�o de armazenamento dentro da p�gina de dados, evitando a alocaç�o desnecess�ria em outra p�gina, sem gerar desperd�cio de espaço ou aumentando o tempo de processamento.

Entendendo a compactaç�o de dados

Compactar um dado parece ser uma tarefa f�cil, tendo em vista as diversas ferramentas ou aplicaç�es compactadoras de arquivos existentes no mercado. Al�m disso, atualmente a grande maioria dos sistemas operacionais apresenta este tipo de recurso.

Em um Sistema Gerenciador de Banco de Dados o recurso de compactaç�o � um pouco diferente em relaç�o a estas ferramentas. O Microsoft SQL Server 2008 apresenta este recurso de forma nativa, sem necessitar de ferramentas externas ou de terceiros para trabalhar sobre as informaç�es armazenadas em tabelas ou �ndices. Realizando uma an�lise de acordo com os dados que se encontram armazenados nestes objetos e possibilitando aplicar a melhor forma de compactaç�o.

O processo de compactaç�o necessita de uma identificaç�o pr�via da forma que o dado se encontra ou ser� armazenado. Na vers�o atual, o SQL Server 2008 estabelece duas formas b�sicas de compactaç�o, chamadas: Compactaç�o por linha de dados (registros) e Compactaç�o por p�gina de dados.

N�o podemos dizer que existe a melhor forma de compactaç�o ou a forma mais correta para realizar este processo. O que existe � a necessidade de compactar um dado mediante o seu estado atual.

Na compactaç�o em n�vel de linha de dados, o SQL Server dever� procurar dimensionar cada linha de registros armazenadas em uma tabela ou �ndice da forma a evitar fragmentaç�o de dados, seja em uma nova linha ou a necessidade de criar mais uma p�gina de dados.

Na compactaç�o em n�vel de p�gina de dados, a tarefa do SQL Server � um pouco mais complicada. O processo de dimensionamento da informaç�o n�o consiste simplesmente em identificar o tamanho do dado ou da linha, mas sim em estabelecer em qual p�gina de dados aquele conjunto de informaç�es poder� ser alocada, respeitando inicialmente os dados j� armazenados na p�gina como tamb�m a informaç�o que poder� ser repassada para outra p�gina ou a criaç�o de uma nova p�gina.

Durante a leitura deste artigo voc� poder� identificar as diversas caracter�sticas e peculiaridades existentes nos dois tipos de compactaç�o. Estabelecer qual ser� a mais indicada para sua necessidade n�o � tarefa deste artigo, nosso objetivo � apresentar e demonstrar como utilizar este recurso muito �til e de extrema importante.

Conhecendo a compactaç�o em n�vel de linha de dados

Como destacado anteriormente, a compactaç�o em n�vel de linha de dados representa um recurso para dimensionamento e alocaç�o de informaç�es para cada linha de informaç�es (registros), armazenadas em uma tabela ou �ndice. Sua utilizaç�o est� diretamente relacionada com cada informaç�o manipulada sobre a tabela configurada para trabalhar com este tipo de compactaç�o.

Antes de utilizar a compactaç�o de linhas de dados, torna-se necess�rio conhecer algumas caracter�sticas e consideraç�es importantes desta forma de compactaç�o, entre elas:

� A compactaç�o pode permitir que mais linhas sejam armazenadas em uma p�gina devido � diminuiç�o do tamanho do dado que ser� alocado em cada linha. Isso � alcançado sem ultrapassar o tamanho por linha e evitando gerar qualquer tipo de fragmentaç�o dos dados;

� Somente as ediç�es Enterprise e Developer do SQL Server 2008 possuem a capacidade de trabalhar com compactaç�o de linhas e p�ginas;

� Uma tabela n�o pode ser habilitada para compactaç�o quando o tamanho m�ximo da linha mais a sobrecarga de compactaç�o exceder o tamanho m�ximo de linha de 8060 bytes. Por exemplo, uma tabela que tem as colunas col1 char (8000) e col2 char (53) n�o pode ser compactada por causa da sobrecarga de compactaç�o adicional;

� Para a compactaç�o de linha e de p�gina, a verificaç�o do tamanho da linha � executada quando o objeto � inicialmente compactado e, depois, verificado � medida que cada linha � inserida ou modificada. A compactaç�o imp�e as seguintes regras:

o Uma atualizaç�o para um tipo de comprimento fixo sempre deve ter �xito, por exemplo, se utilizamos uma coluna do tipo varchar (10) e alterarmos para um campo char (10);

o A desabilitaç�o da compactaç�o de dados sempre deve ter �xito. Mesmo que a linha compactada caiba em uma p�gina (o que significa que ela � menor do que 8060 bytes). Em alguns casos, a linha descompactada poder� sofrer atualizaç�es que possam gerar a necessidade de armazenar estas alteraç�es em outra p�gina de dados, mesmo que a atual p�gina possua um pequeno espaço livre.

� Quando uma lista de partiç�es � especificada, o tipo de compactaç�o deve ser definido como ROW, PAGE ou NONE em partiç�es individuais, possibilitando uma melhor alocaç�o de espaço;

� Quando a estrutura de uma tabela � modificada, a compactaç�o existente � preservada, a menos que especificada de outra maneira, atrav�s do n�mero da partiç�o ou da lista de partiç�es. Esta lista de partiç�es corresponde � quantidade de partiç�es existentes em uma Tabela. Caso seja especificado um valor ou uma faixa de valores fora do n�mero de partiç�es existentes o SQL Server ser� forçado a emitir uma mensagem de erro;

� �ndices n�o clusterizados n�o herdam a propriedade de compactaç�o da tabela. Para compactar �ndices � preciso definir explicitamente a sua propriedade de compactaç�o. Por padr�o, a configuraç�o de compactaç�o de �ndices ser� definida como NONE quando o �ndice for criado;

� Quando um �ndice clusterizado � criado em um heap, ele herda o estado de compactaç�o do heap, a menos que um estado de compactaç�o alternativo seja especificado.

Partiç�es individuais: O particionamento pode ser atingido sem dividir tabelas, colocando-se as tabelas fisicamente em unidades individuais de disco. Colocar uma tabela em uma unidade f�sica e as tabelas relacionadas em uma unidade separada pode vir a melhorar o desempenho das consultas, pois, quando as consultas que envolvem junç�es entre as tabelas forem executadas, diversos cabeçotes de discos ler�o os dados ao mesmo tempo. Grupos de arquivos do SQL Server podem ser usados para especificar em quais discos colocar as tabelas.

A Tabela 1 apresenta um exemplo de como a compactaç�o de dados em n�vel de linha possibilita a diminuiç�o do consumo do armazenamento de dados.

Value [Datatype]	Antes da Compress�o	Depois da Compress�o
34[int]	4 bytes	1 byte
32,767 [smallint]	2 bytes	2 bytes
Redmond [char(50)]	50 bytes	7 bytes
WA [char(2)]	2 bytes	2 bytes

Tabela 1. Compactaç�o de dados aplicada em n�vel de linha.

Notas do DevMan

Row: Altera somente o formato de armazenamento f�sico dos dados associados a um tipo de dados, mas n�o sua sintaxe ou sem�ntica. N�o s�o exigidas alteraç�es de aplicativo quando uma ou mais tabelas s�o habilitadas para compactaç�o.

Page: A compactaç�o de p�gina � semelhante para tabelas, partiç�es de tabela, �ndices e partiç�es de �ndice.

None: Representa que a tabela selecionada n�o utilizar� compactaç�o de dados.

Como a compactaç�o de linha afeta o armazenamento

A Tabela 2 descreve como a compactaç�o de linha afeta os tipos existentes no SQL Server. Ela n�o destaca o poss�vel aumento do tamanho f�sico de uma tabela caso a compactaç�o utilizada esteja definida no n�vel de p�gina de dados. Em algumas situaç�es, o n�vel de compactaç�o de p�gina de dados poder� ocasionar o armazenamento de dados em novas p�ginas. Desta forma, o SQL Server ser� obrigado a utilizar mais espaço f�sico do disco r�gido para armazenamento destas informaç�es.

Tipo de dado	Afeta no armazenamento	Descriç�o
tinyint	N�o	1 byte � o armazenamento m�nimo necess�rio.
smallint	Sim	Se o valor couber em 1 byte, apenas 1 byte ser� usado.
Int	Sim	Usa apenas os bytes necess�rios. Por exemplo, se um valor puder ser armazenado em 1 byte, o armazenamento ocupar� apenas 1 byte.
bigint	Sim	Usa apenas os bytes necess�rios, semelhante ao Int.
decimal	Sim	Esse armazenamento � exatamente igual ao do formato de armazenamento vardecimal.
numeric	Sim	Esse armazenamento � exatamente igual ao do formato de armazenamento vardecimal.
Bit	Sim	A sobrecarga dos metadados atinge 4 bits.
smallmoney	Sim	Utiliza a representaç�o de dados de n�meros inteiros para valores num�ricos de 4 bytes. Os valores monet�rios s�o multiplicados por 10000 e o valor inteiro resultante � armazenado removendo os d�gitos ap�s a casa decimal. Esse tipo tem uma otimizaç�o de armazenamento semelhante � empregada para tipos de n�mero inteiro.
money	Sim	Utiliza a representaç�o de dados de n�meros inteiros para valores num�ricos de 8 bytes. Os valores monet�rios s�o multiplicados por 10000 e o valor inteiro resultante � armazenado removendo os d�gitos ap�s a casa decimal. Esse tipo tem um intervalo maior quesmallmoney. Ele tem uma otimizaç�o de armazenamento semelhante � empregada para tipos de n�mero inteiro.
float	Sim	Os bytes menos significativos com zeros n�o s�o armazenados. A compactaç�ofloat� aplic�vel principalmente para obter valores n�o fracion�rios em mantissa.
real	Sim	Os bytes menos significativos com zeros n�o s�o armazenados. A compactaç�oreal� aplic�vel principalmente para obter valores n�o fracion�rios em mantissa.
smalldatetime	N�o	Utiliza representaç�o de dados de n�meros inteiros com base em n�meros inteiros de 2 bytes. Ser�o necess�rios mais 2 bytes para dados partir de 1902. Portanto, n�o h� aumento a partir desse ponto. A hora � representada atrav�s de um n�mero em minutos a partir da meia-noite. Para valores de hora representados ap�s 4hs, ser� utilizado um segundo byte para o armazenamento deste valor. Se umsmalldatetimefor usado apenas para representar uma data (o caso comum), a hora ser� 0.0. A compactaç�o salva 2 bytes armazenando a hora em um formato de byte mais significativo para compactaç�o de linha.
datetime	Sim	Usa a representaç�o de dados de n�mero inteiro atrav�s n�meros inteiros de 4 bytes. O valor de inteiro representa o n�mero de dias com data base de 1/1/1900. Os primeiros 2 bytes podem representar at� o ano 2079, sendo assim, a compactaç�o ocupar� 2 bytes at� esse ponto. Cada valor de inteiro representa 3,33 milissegundos. A compactaç�o esvazia os primeiros 2 bytes nos primeiros cinco minutos e precisa do quarto byte ap�s as 16h. Portanto, a compactaç�o pode salvar apenas 1 byte depois das 16h. Quandodatetime� compactado como qualquer outro inteiro, a compactaç�o salva 2 bytes na data.
date	N�o	Usa a representaç�o de dados de inteiro usando 3 bytes. Representa a data a partir de 1/1/0001. Para datas contempor�neas, a compactaç�o de linha usa todos os 3 bytes. N�o gera nenhum aumento.
time	N�o	Usa a representaç�o de dados de inteiro usando de 3 a 6 bytes. H� v�rias precis�es que começam com 0 a 9 que podem ocupar de 3 a 6 bytes. O espaço compactado � usado como segue: Precis�o = 0. Bytes = 3. Cada valor de inteiro representa um segundo. A compactaç�o pode representar a hora at� 16h usando 2 bytes, salvando potencialmente 1 byte. Precis�o = 1. Bytes = 3. Cada valor de inteiro representa 1/10 segundos. A compactaç�o usa o terceiro byte antes das 2h. Resulta em um pequeno aumento. Precis�o = 2. Bytes = 3. Como no caso anterior, � improv�vel gerar aumento. Precis�o = 3. Bytes = 4. A compactaç�o pode representar a hora at� 5h, sendo utilizado os primeiros 3 bytes, gerando pouco aumento. Precis�o = 4. Bytes = 4. Os primeiros 3 bytes s�o ocupados nos primeiros 27 segundos. Nenhum aumento � esperado. Precis�o = 5, Bytes = 5. O quinto byte ser� usado depois do meio-dia. Precis�o = 6 e 7, Bytes = 5. N�o gera nenhum aumento. Precis�o = 8, Bytes = 6. O sexto byte ser� usado depois das 3h. N�o h� nenhuma alteraç�o no armazenamento para compactaç�o de linha. De modo geral, n�o se pode esperar muito aumento da compactaç�o do tipo de dadostime.
datetime2	Sim	Usa a representaç�o de dados de inteiro usando de 6 a 9 bytes. Os primeiros 4 bytes representam a data. Os bytes ocupados pela hora dependem da precis�o da hora que � especificada.
		O valor de inteiro representa o n�mero de dias desde 1/1/0001 com um limite superior de 31/12/9999. Para representar uma data no ano 2005, a compactaç�o utiliza 3 bytes.
		N�o h� aumento de hora porque � permitido de 2 a 4 bytes para v�rias precis�es de hora. Portanto, para precis�o de um segundo, a compactaç�o usa 2 bytes para a hora, que ocupa o segundo byte depois de 255 segundos.
datetimeoffset	Sim	Semelhante adatetime2, exceto pelo fato de que h� 2 bytes de fuso hor�rio do formato (HH:MM).
		Comodatetime2, a compactaç�o pode salvar 2 bytes.
		Para valores de fuso hor�rio, o valor MM pode ser 0 na maioria dos casos. Portanto, a compactaç�o pode salvar possivelmente 1 byte.
		N�o h� alteraç�o alguma no armazenamento para compactaç�o de linha.
char	Sim	Caracteres de preenchimento � direita s�o removidos. Observe que o Mecanismo de Banco de Dados insere o mesmo caractere de preenchimento, independentemente do agrupamento usado.
varchar	N�o	Nenhum efeito.
text	N�o	Nenhum efeito.
nchar	Sim	Caracteres de preenchimento � direita s�o removidos. Semelhante ao char.
nvarchar	N�o	Nenhum efeito.
ntext	N�o	Nenhum efeito.
binary	Sim	Zeros � direita s�o removidos.
varbinary	N�o	Nenhum efeito.
image	N�o	Nenhum efeito.
cursor	N�o	Nenhum efeito.
timestamp/rowversion	Sim	Usa a representaç�o de dados de inteiro usando 8 bytes. H� um contador de carimbo de data/hora mantido para cada banco de dados e seu valor começa em 0. Ele pode ser compactado como qualquer outro valor de inteiro.
sql_variant	N�o	Nenhum efeito.
uniqueidentifier	N�o	Nenhum efeito.
table	N�o	Nenhum efeito.
xml	N�o	Nenhum efeito.
Tipos definidos pelo usu�rio	N�o	� representado internamente comovarbinary.
FILESTREAM	N�o	� representado internamente comovarbinary.Bottom of Form
Value [Datatype]	Antes da Compress�o	Depois da Compress�o
34[int]	4 bytes	1 byte
32,767 [smallint]	2 bytes	2 bytes
Redmond [char(50)]	50 bytes	7 bytes
WA [char(2)]	2 bytes	2 bytes

Tabela 2. Como a compactaç�o em n�vel de linha afeta cada tipo de dados.

A compactaç�o em n�vel de linha reduz a quantidade de metadados usado para armazenar a linha, ou seja, de acordo com tamanho informado para este tipo de dado, o SQL Server dever� reservar e dimensionar o espaço de alocaç�o para o dado independente do tamanho real que o dado for ocupar.

A partir do momento em que utilizamos a compactaç�o de dados sobre tipos de dados de tamanho fixo, Char, Nchar, entre outros. O SQL Server ir� realizar o mesmo procedimento para dados de formato vari�vel, ou seja, se o dado CHAR (100) utilizar apenas 10 caracteres, os espaços em branco n�o utilizados ser�o descartados, podendo assim reduzir o espaço necess�rio para seu armazenamento.

Por outro lado, n�o ser�o compactados valores em campos de tamanho fixo ou vari�vel, caso a infomaç�o passada apresentar valores nulos (NULL) ou for simplesmente um n�mero 0 (zero), para a compactaç�o em n�vel de linha. Neste caso, n�o ocorrer� nenhum ganho de armazenamento se comparado com o tamanho a original ocupado sem a compactaç�o. A seguir destacaremos a forma de compactaç�o em n�vel de p�gina de dados, suas caracter�sticas e consideraç�es.

Conhecendo a compactaç�o em nivel de p�ginas de dados

Como destacado anteriomente, a compactaç�o em n�vel de p�gina de dados est� relacionada diretamente com as informaç�es armazenadas em cada p�gina de dados que comp�em uma tabela. Esse recurso � uma tarefa um pouco mais complicada em relaç�o � compactaç�o em n�vel de linha de dados.

O processo de dimensionamento da informaç�o n�o consiste simplesmente em identificar o tamanho do dado ou da linha, mas sim em estabelecer em qual p�gina de dados aquele conjunto de informaç�es poder� ser alocada, respeitando inicialmente os dados j� armazenados na p�gina como tamb�m a informaç�o que poder� ser repassada para outra p�gina ou a criaç�o de uma nova p�gina.

Quando uma tabela � criada e seu n�vel de compactaç�o foi definido como p�gina, o SQL Server n�o realizar� qualquer tipo de compactaç�o.

A partir do momento em que os dados começarem a ser adicionados, os mesmos ser�o alocados na primeira p�gina de dados, mas utilizando a compactaç�o por linha. Este procedimento � necess�rio para que o SQL Server consiga identificar a p�gina que o dado ser� alocado posteriormente. A compactaç�o por p�gina ser� realizada conforme a inserç�o de novos dados.

Durante o processo de inserç�o de dados, o SQL Server dever� dimensionar o tamanho de alocaç�o destes dados para cada linha, n�o permitindo que o conjunto de dados ultrapasse o tamanho de 8060 bytes.

Quando este valor � ultrapassado, o SQL Server identificar� esta linha de registro como uma linha cheia e inicia o processo de alocaç�o do dado para uma pr�xima linha. Esta alocaç�o ser� realizada utilizando a compactaç�o em n�vel de p�gina. Por outro lado, se o espaço obtido pela compactaç�o de p�gina for menor que o espaço exigido para o armazenamento dos dados, a compactaç�o de p�gina n�o ser� utilizada para p�gina.

Caso a compactaç�o de p�gina tenha criado espaço suficiente na p�gina para uma linha adicional, esta linha ser� adicionada e os dados ser�o compactados por linha e p�gina. O armazenamento da informaç�o nesta p�gina ser� realizada ap�s uma revis�o em cada coluna que comp�em a tabela avaliada.

Para realizar esta avaliaç�o e validaç�o o SQL Server utiliza por padr�o a chamada compactaç�o de prefixo. Em seguida o SQL Server definir� se utiliza a compactaç�o de prefixo ou compactaç�o por dicion�rio. Tanto a compactaç�o por prefixo e dicion�rio ser�o destacadas posteriormente.

As linhas futuras ser�o ajustadas � nova p�gina se n�o couberem na p�gina atual. O SQL Server dever� adicionar � tabela uma nova p�gina de dados semelhante � primeira p�gina. Esta nova p�gina n�o ser� compactada imediatamente, ou seja, esta p�gina dever� ser dimensionada a partir do momento em que uma das linhas de dados ultrapassar o seu tamanho m�ximo.

Assim, devemos destacar que a compactaç�o de p�ginas de dados tamb�m necessita de uma an�lise sobre algumas caract�ristas e consideraç�es importantes antes da sua aplicaç�o, entre elas:

� Quando um heap � configurado para compactaç�o em n�vel de p�gina, as p�ginas s� recebem compactaç�o em n�vel de p�gina nos seguintes modos:

o Os dados s�o inseridos usando a sintaxe BULK INSERT;

o Os dados s�o inseridos usando INSERT INTO ... Sintaxe WITH (TABLOCK);

o Uma tabela � recriada executando ALTER TABLE ... Instruç�o REBUILD com a opç�o de compactaç�o PAGE.

� As novas p�ginas alocadas em um heap como parte de operaç�es DML n�o usar�o a compactaç�o PAGE at� o heap ser recompilado;

� A alteraç�o da configuraç�o de compactaç�o de um heap exige que todos os �ndices n�o clusterizados na tabela sejam recriados, para que tenham ponteiros para os novos locais de linha no heap;

� Os requisitos de espaço em disco para habilitar ou desabilitar a compactaç�o de p�gina ou de linha s�o os mesmos que para criar ou recriar um �ndice. Para dados particionados voc� pode reduzir o espaço exigido para habilitar ou desabilitar a compactaç�o para uma partiç�o de cada vez;

� Para determinar o estado de compactaç�o das partiç�es em uma tabela particionada, consulte a coluna data_compression existente no cat�logo de vis�es (view catalog), chamada sys.partitions;

� Quando voc� estiver compactando �ndices, as p�ginas de n�vel folha poder�o ser compactadas com a compactaç�o de linha e de p�gina. As p�ginas que n�o s�o de n�vel folha n�o recebem a compactaç�o de p�gina;

� A compactaç�o de dados n�o est� dispon�vel para os dados armazenados separadamente.

A compactaç�o de p�gina � semelhante para tabelas, partiç�es de tabela, �ndices e partiç�es de �ndice. A compactaç�o do n�vel folha de tabelas e �ndices usando a compactaç�o de p�gina consiste em tr�s operaç�es, nesta ordem:

1. Compactaç�o de linha;

2. Compactaç�o de prefixo;

3. Compactaç�o de dicion�rio.

Este tipo compactaç�o � mais eficiente pois oferece um ganho a mais na compress�o, entretanto, proporciona um aumento na utilizaç�o da CPU. Quando voc� usa a compactaç�o de p�gina, as p�ginas do n�vel n�o-folha dos �ndices s�o compactadas usando apenas a compactaç�o de linha.

Compactaç�o em n�vel de p�gina utilizando a compactaç�o por prefixo

Nesta forma de compactaç�o o SQL Server utiliza um caractere identificador chamado prefixo para procurar dados que possam apresentar caracter�sticas compat�veis para esta t�cnica de compactaç�o. Este caractere dever� identificar em cada informaç�o armazenada sobre as colunas analisadas, os dados que podem ser compactados. Para cada p�gina que est� sendo compactada, a compactaç�o de prefixo usa tr�s etapas para estabelecer a melhor forma de compactaç�o:

1. Para cada coluna avaliada � identificada qual informaç�o poder� ser compactada. Isto � feito com o objetivo de reduzir o espaço de armazenamento para os valores de cada coluna;

2. Uma linha que representa os valores de prefixo de cada coluna � criada e armazenada em uma estrutura CI (informaç�es de compactaç�o) que segue imediatamente o cabeçalho da p�gina;

3. Os valores de prefixo repetidos da coluna s�o substitu�dos por uma refer�ncia ao prefixo correspondente. Se o valor de uma linha n�o corresponder exatamente ao valor do prefixo selecionado, dever� ser indicada uma correspond�ncia parcial.

A Figura 1 a mostra um exemplo de p�gina de uma tabela antes da compactaç�o de prefixo.

Figura 1. Exemplo da p�gina de dados antes da compactaç�o do prefixo.

A Figura 2 mostra a mesma p�gina ap�s a compactaç�o de prefixo. O prefixo � movido para o cabeçalho e os valores da coluna s�o alterados para refer�ncias ao prefixo. Na primeira linha da primeira coluna o valor 4b indica que os primeiros quatro caracteres do prefixo (aaab) est�o presentes para essa linha e, tamb�m, o caractere b na �rea de cabeçalho da p�gina. Isso gera o valor resultante aaabb, que � o valor original.

Figura 2. Exemplo da p�gina de dados ap�s a compactaç�o do prefixo.

Compactaç�o em n�vel de p�gina utilizando a compactaç�o por dicion�rio

Ap�s entendermos como � realizada a compactaç�o de prefixo, podemos agora conhecer a compactaç�o de dicion�rio. A compactaç�o de dicion�rio procura valores repetidos em qualquer lugar da p�gina e os armazena na �rea de informaç�es de compactaç�o.

Diferentemente da compactaç�o de prefixo, a compactaç�o de dicion�rio n�o � restrita a uma coluna. A compactaç�o de dicion�rio pode substituir valores repetidos que ocorrem em qualquer lugar de uma p�gina. A Figura 3 mostra o mesmo exemplo da Figura 1 ap�s a compactaç�o de dicion�rio.

Figura 3. Exemplo p�gina de dados ap�s a compactaç�o do dicion�rio.

O SQL Server realizou uma busca para identificar todos os dados repetidos, deslocando os mesmos para a �rea de compactaç�o no cabeçalho da p�gina de dados. Observe que os valores [0bbbb] que se encontravam repetidos em duas colunas distintas agora o est� armazenado no cabeçalho e possui um valor de identificaç�o. Neste caso, o n�mero 1 � o n�mero identificador dos dados que estavam armazenados nestas colunas.

Agora que j� conhecemos um pouco mais sobre as duas formas de compactaç�o, suas principais caracter�sticas e particularidades, o que nos resta � por a m�o na massa e utilizar estes recursos. Para isso criaremos um ambiente de demonstraç�o trabalhando com um conjunto de informaç�es fict�cias para auxiliar e melhorar nosso entendimento sobre o assunto.

A seguir veremos como aplicar a compactaç�o de dados utilizando o n�vel de compactaç�o por linha de dados e posteriormente a compactaç�o de p�gina de dados ser� abordada.

Aplicando a compactaç�o de dados

A forma de aplicaç�o da compactaç�o de dados consiste na utilizaç�o das funcionalidades dispon�veis no Microsoft SQL Server 2008 sobre as tabelas e �ndices dispon�veis.

Iniciaremos o processo de demonstraç�o do uso destes recursos em n�vel de linhas, atrav�s da criaç�o do banco de dados SQLMagazine, conforme a Listagem 1.

Listagem 1. Criaç�o do Banco de dados

-- Bloco 1 --
 Create Database SQLMagazine
 Go
  
 Use SQLMagazine
 Go

Posteriormente criaremos duas tabelas chamadas Revistas e RevistasCompactadas, onde a tabela Revistas n�o sofrer� nenhum tipo de compactaç�o de dados. O c�digo para criaç�o das tabelas pode ser visto na Listagem 2.

O processo de compactaç�o de dados pode ser definido no momento da criaç�o de uma nova tabela ou �ndice, fazendo uso das instruç�es CREATE TABLE, de acordo com o Bloco 2 da Listagem 2.

Listagem 2. Criaç�o das tabelas Revistas e RevistasCompactadas

-- Bloco 1 --
 Create Table Revistas
  (Codigo SmallInt Identity(1,1) Primary Key,
   Descricao Varchar(50),
   Edicao Int Default(1),
   AnoPublicacao Int Default(2009))
  On [Primary]
 Go
  
 -- Bloco 2 --
 Create Table RevistasCompactadas
  (Codigo SmallInt Identity(1,1) Primary Key,
   Descricao Varchar(50),
   Edicao Int Default(1),
   AnoPublicacao Int Default(2009))
  On [Primary] 
 WITH (DATA_COMPRESSION = ROW) 
 Go

Agora que j� temos o Banco e as tabelas criadas, vamos povoar estas tabelas com informaç�es fict�cias para ilustrar nosso exemplo. Acompanhando a Listagem 3, encontramos as instruç�es para colocar informaç�es nas tabelas Revistas e RevistasCompactadas.

Listagem 3. Inserindo dados nas tabelas Revistas e RevistasCompactadas

-- Bloco 1 --
 Declare @Cont Int
  
 Set @Cont=1
  
 While (@Cont <= 10000)
   Begin
     Insert Into Revistas Values ('SQL Magazine',@Cont,2009)
     Set @Cont +=1;
   End
 Go
  
 -- Bloco 2 --
 Declare @Cont Int
  
 Set @Cont=1
  
 While (@Cont <= 10000)
   Begin
     Insert Into RevistasCompactadas Values ('SQL Magazine',@Cont,2009)
     Set @Cont +=1;
   End
 Go

Agora, ambas as tabelas possuem informaç�es simulando tabelas verdadeiras. Se consultarmos os dados armazenados em cada tabela, poderemos observar que a inserç�o de dados ocorreu normalmente. A seguir, a Figura 4 apresenta uma pequena relaç�o de registros armazenados nas tabelas Revistas e RevistasCompactadas.

Figura 4. Dados armazenados nas tabelas Revistas e RevistasCompactadas.

Na Figura 4 podemos observar visualmente que a estrutura das tabelas e os dados existentes em cada uma n�o apresentam diferenças, sendo que, a tabela RevistasCompactadas est� neste momento configurada para trabalhar com compactaç�o de dados em n�vel de linhas.

Agora vamos comparar o espaço f�sico ocupado por cada tabela fazendo uso da system stored procedure sp_spaceused definida na Listagem 4. O resultado da execuç�o desta stored procedure � exibido na Figura 5.

Listagem 4. Consultando o espaço f�sico ocupado por cada tabela


-- Bloco 1 --
 sp_spaceused 'Revistas'
 Go
  
 -- Bloco 2 --
 sp_spaceused 'RevistasCompactadas'
 Go

Figura 5. Comparativo entre a tabela Revistas e RevistasCompactadas.

Analisando os resultados gerados atrav�s da system stored procedure sp_spaceused, podemos observar a diferença de tamanho no espaço ocupado ploes dados na tabela RevistasCompactadas em relaç�o a tabela Revistas. O pr�ximo passo � realizar algumas alteraç�es na forma de compactaç�o dos dados, iniciando pela mudança do n�vel de compactaç�o de linha para p�gina, de acordo com a Listagem 5.

Listagem 5. Alterando o n�vel de compactaç�o da tabela RevistasCompactadas

-- Bloco 1 --
 Alter Table RevistasCompactadas
 Rebuild With (DATA_COMPRESSION=PAGE)
 Go

Ap�s a alteraç�o na forma de compactaç�o realizada na tabela RevistasCompactas, devemos verificar se o espaço ocupado fisicamente por esta tabela sofreu alguma mudança. Para isso, executaremos o c�digo apresentado na Listagem 6. Voc� poder� observar alguma semelhança entre os resultados apresentados na Figura 6.

Listagem 6. Consultando o espaço f�sico ocupado por cada tabela em n�vel de pagina

-- Bloco 1 --
 sp_spaceused 'Revistas'
 Go
  
 -- Bloco 2 --
 sp_spaceused 'RevistasCompactadas'
 Go

Figura 6. Comparativo entre a tabela Revistas e RevistasCompactadas com compactaç�o em n�vel de p�gina.

Mais uma vez a compactaç�o de dados nos apresenta algumas mudanças em relaç�o aos dados armazenados em uma tabela. Neste caso, observamos de forma clara que a compactaç�o em n�vel de p�gina de dados dimensionou consideravelmente a alocaç�o de dados, como tamb�m diminuiu o espaço n�o alocado para o armazenamento dos dados compactados.

Agora devemos verificar se esta alteraç�o ocasionou alguma mudança nos dados armazenados na tabela RevistasCompactadas. Podemos consultar alguns registros, conforme demonstrado na Figura 7.

Figura 7. Dados armazenados nas tabela RevistasCompactadas.

Estimando o tamanho da tabela de acordo com sua compactaç�o

Depois de v�rios testes realizados, temos a certeza de que a compactaç�o de dados em n�vel de linhas ou p�ginas de dados pode apresentar diferenças no armazenamento f�sico dos dados. Agora vamos conhecer como podemos realizar uma estimativa do tamanho de uma tabela de acordo com sua compactaç�o.

A compactaç�o pode ser avaliada para tabelas inteiras ou partes de tabelas. Isso inclui heaps, �ndices clusterizados, �ndices n�o clusterizados, exibiç�es indexadas e partiç�es de tabelas e �ndices. Estruturas de tabela podem ser compactadas usando compactaç�o de linha ou de p�gina.

Se a tabela, �ndice ou partiç�o j� estiverem compactadas, � poss�vel usar esse procedimento para estimar o tamanho da tabela, do �ndice ou da partiç�o se eles forem descompactados. Para realizar esta estimativa do tamanho de uma tabela devemos utilizar a system stored procedure sp_estimate_data_compression_savings, conforme a sintaxe apresentada na Listagem 7 e descrita na Tabela 3.

Listagem 7. Sintaxe da sp_estimate_data_compression_savings

-- Bloco 1 --
 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' 
 [;]

No c�digo apresentado na Listagem 7:

� [ @schema_name = ] 'schema_name': � o nome do esquema de banco de dados que cont�m a tabela ou vis�o indexada. Se schema_name n�o for informado, ou seja, considerado NULL, o esquema padr�o do usu�rio atual ser� usado, pois o SQL Server n�o considera um schema_name definido como NULL;

� [ @object_name = ] 'object_name': � o nome da tabela ou vis�o indexada onde �ndice est�;

� [ @index_id = ] 'index_id': � o ID do �ndice. O index_id � int e pode ter um dos seguintes valores: o n�mero do ID de um �ndice, NULL ou 0 se object_id for um heap. Para retornar informaç�es de todos os �ndices de uma tabela base ou vis�o, especifique NULL. Se voc� especificar NULL, tamb�m dever� especificar NULL para partition_number, com isso, o SQL Server tentar� estimar o espaço de compactaç�o de dados para tabelas desconsiderando a exist�ncia ou n�o de particionamento;

� [ @partition_number = ] 'partition_number': � o n�mero da partiç�o no objeto. partition_number � int e pode ter um dos seguintes valores: o n�mero da partiç�o de um �ndice ou heap, NULL ou 1 para um heap ou �ndice n�o particionado. Para especificar a partiç�o, tamb�m � poss�vel especificar a funç�o $partition. Para retornar informaç�es de todas as partiç�es do objeto propriet�rio, especifique NULL;

� [ @data_compression = ] 'data_compression': � o tipo de compactaç�o a ser avaliada. data_compression pode ser um dos seguintes valores: NONE, ROW ou PAGE.

Nome da coluna	Tipo de dados	Descriç�o
object_name	sysname	Nome da tabela ou exibiç�o indexada.
schema_name	sysname	Esquema da tabela ou exibiç�o indexada.
index_id	int	ID de um �ndice. 0 = Heap 1 = �ndice clusterizado >1 = �ndice n�o clusterizado
partition_number	int	N�mero da partiç�o. Retorna 1 para uma tabela ou �ndice n�o particionado.
size_with_current_compression_setting (KB)	bigint	Retorna o tamanho atual da tabela, �ndice ou partiç�o solicitada.
size_with_requested_compression_setting (KB)	bigint	Tamanho estimado da tabela, �ndice ou partiç�o que usa a configuraç�o da compactaç�o solicitada e, se aplic�vel, o fator de preenchimento existente, supondo que n�o h� nenhuma fragmentaç�o.
sample_size_with_current_compression_setting (KB)	bigint	Tamanho do exemplo criado usando a configuraç�o da compactaç�o existente e, se aplic�vel, o fator de preenchimento existente e nenhuma fragmentaç�o. Como esse conjunto de linhas � criado do zero, n�o h� nenhuma fragmentaç�o.
sample_size_with_requested_compression_setting (KB)	bigint	Tamanho do exemplo criado usando a configuraç�o da compactaç�o solicitada e, se aplic�vel, o fator de preenchimento existente e nenhuma fragmentaç�o.

Tabela 3. Conjunto de resultados retornados para fornecer o tamanho atual e estimado da tabela, �ndice ou partiç�o.

Como j� conhecemos a finalidade da sp_estimate_data_compression_savings, agora temos a possibilidade de realizar o c�lculo da estimativa do tamanho da tabela, como pode ser visto nas Listagens 8 e 9. O resultado � apresentado nas Figuras 8 e 9.

Listagem 8. Obtendo os resultados da estimativa de compactaç�o em n�vel de linha

-- Bloco 1 �
  
 EXEC sp_estimate_data_compression_savings 'dbo', 
 'RevistasCompactadas', NULL, 
 NULL, 
 'ROW'

Figura 8. Estimativa do tamanho da compactaç�o em n�vel de linha.

A Figura 8 apresenta os resultados de estimativa do tamanho da tabela com base na compactaç�o em n�vel de linha para a tabela RevistasCompactadas. Com base neste resultado, podemos observar uma poss�vel mudança no tamanho f�sico da tabela RevistasCompactadas, representando um ganho na alocaç�o do espaço em disco.

Listagem 9. Obtendo os resultados da estimativa de compactaç�o em n�vel de p�gina

-- Bloco 1 �
  
 EXEC sp_estimate_data_compression_savings 'dbo',
 'RevistasCompactadas', NULL, 
 NULL, 
 'PAGE'

Figura 9. Estimativa do tamanho da compactaç�o em n�vel de p�gina de dados.

A Figura 9 apresenta os resultados da estimativa de compactaç�o para a tabela RevistasCompactadas utilizando o n�vel de compactaç�o p�gina de dados. Se compararmos estes valores com base nos valores apresentados na Figura 8, podemos observar uma pequena variaç�o entre a forma de compactaç�o em linha e p�gina de dados.

Neste caso, a compactaç�o em n�vel de p�gina de dados apresenta uma pequena vantagem em relaç�o � compactaç�o em n�vel de linha. Essa vantagem est� relacionada � necessidade de utilizaç�o do espaço em disco necess�rio para armazenar esta tabela.

Conclus�o

Atrav�s da compactaç�o de dados presente no SQL Server 2008, � poss�vel melhorar a alocaç�o de dados armazenados fisicamente, como tamb�m evitar poss�veis desperd�cios de espaço em disco sem gerar perda de performance.

O artigo demonstrou o conceito e a pr�tica deste recurso presente no SQL Server 2005 SP 2 e melhorado no SQL Server 2008. Aprendemos com os exemplos a utilizar a compactaç�o de dados em n�vel de linha e p�gina de dados, suas principais consideraç�es e impactos em relaç�o aos dados armazenados em uma tabela tanto no momento da sua criaç�o, como tamb�m ap�s os dados j� estarem armazenados.

Links

Live Meeting Presentation on Data and Backup Compression.
https://connect.microsoft.com/SQLServer/Downloads/DownloadDetails.aspx?DownloadID=9080

Live Meeting Presentation on Data and Backup Compression Wrapper.
https://connect.microsoft.com/SQLServer/Downloads/DownloadDetails.aspx?DownloadID=9083

WebCast: SQL Server 2008 Data Management Overview (Level 100).
http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-US&EventID=1032342047&CountryCode=US

Compress�o de dados no SQL Server 2008
http://www.microsoft.com/sql/experience/ITPros.aspx?loc=pt&v=http%3a%2f%2fmschnlnine.vo.llnwd.
net%2fd1%2fedge%2f7%2f1%2f7%2f1%2fCOMPRESSAO

Manuais Online do SQL Server 2008.
http://msdn.microsoft.com/pt-br/sqlserver/cc514207.aspx

SQL Server MSDN Experience.
http://www.msdnbrasil.com.br/experience/sqlserver/

Artigo SQL Magazine 68 - Compacta��o de Dados com o SQL Server 2008

Neste artigo veremos as formas de compactação de dados existente no Microsoft SQL Server 2008. Em seguida, demonstraremos como utilizar cada uma destas formas, com base em duas tabelas contendo dados fictícios.

Atenç�o: esse artigo tem uma palestra complementar. Clique e assista!

Artigos relacionados