Informações do Artigo – v1



Compacta??o de dados com o Microsoft SQL Server 2008Pedro Antonio Galv?o Junior pedrogalvaojunior@Atua no mercado de TI/SI há 14 anos, graduado no curso superior de Tecnologia em Sistemas da Informa??o Faculdade Uninove de S?o Roque), Pós-Graduado (Gest?o e Engenharia de Processos para Desenvolvimento de Software com RUP) FIAP, forma??o MCDBA (SQL Server 2000), MVP 2007 e 2008, 2008 e 2009, 2009 e 2010 (Most Valuable Profissional) Windows Server System – SQL Server. Trabalha atualmente como Engenheiro de Processos e Administrador de Bancos de Dados SQL Server 2000 e 2005, na Fábrica de Artefatos de Látex S?o Roque. Professor Universitário nas Faculdades Academia de Ensino Superior de Sorocaba e S?o Roque, palestrante de Eventos Microsoft TechEd 2007e 2008. 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 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 dadosA 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ário em outra página, sem gerar disperdício de espa?o ou aumentando o tempo de processamento.Entendendo a compacta??o de dadosCompactar 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 apresentam 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 dadosComo 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: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);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 a 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.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[int] 4 bytes 1 byte32,767 [smallint]2 bytes2 bytes Redmond [char(50)]50 bytes 7 bytes WA [char(2)]2 bytes2 bytes Tabela 1. Compacta??o de dados aplicada em nível de o 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 dadoAfeta no armazenamentoDescri??otinyintN?o1 byte é o armazenamento mínimo necessário.smallintSimSe o valor couber em 1 byte, apenas 1 byte será usado.IntSimUsa apenas os bytes necessários. Por exemplo, se um valor puder ser armazenado em 1 byte, o armazenamento ocupará apenas 1 byte.bigintSimUsa apenas os bytes necessários, semelhante ao Int.decimalSimEsse armazenamento é exatamente igual ao do formato de armazenamento vardecimal. numericSimEsse armazenamento é exatamente igual ao do formato de armazenamento vardecimal.BitSimA sobrecarga dos metadados atinge 4 bits.smallmoneySimUtiliza 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.moneySimUtiliza 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 que smallmoney. Ele tem uma otimiza??o de armazenamento semelhante à empregada para tipos de número inteiro.floatSimOs bytes menos significativos com zeros n?o s?o armazenados. A compacta??o float é aplicável principalmente para obter valores n?o fracionários em mantissa.realSimOs bytes menos significativos com zeros n?o s?o armazenados. A compacta??o real é aplicável principalmente para obter valores n?o fracionários em mantissa.smalldatetimeN?oUtiliza 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 um smalldatetime for 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.????datetimeSimUsa 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. Quando datetime é compactado como qualquer outro inteiro, a compacta??o salva 2 bytes na data.dateN?oUsa 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.timeN?oUsa 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 dados time.??????????????????datetime2SimUsa 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.datetimeoffsetSimSemelhante a datetime2, exceto pelo fato de que há 2 bytes de fuso horário do formato (HH:MM).??Como datetime2, 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.charSimCaracteres de preenchimento à direita s?o removidos. Observe que o Mecanismo de Banco de Dados insere o mesmo caractere de preenchimento, independentemente do agrupamento usado.varcharN?oNenhum efeito.textN?oNenhum efeito.ncharSimCaracteres de preenchimento à direita s?o removidos. Semelhante ao char.nvarcharN?oNenhum efeito.ntextN?oNenhum efeito.binarySimZeros à direita s?o removidos.varbinaryN?oNenhum efeito.imageN?oNenhum efeito.cursorN?oNenhum efeito.timestamp / rowversionSimUsa 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_variantN?oNenhum efeito.uniqueidentifierN?oNenhum efeito.tableN?oNenhum efeito.xmlN?oNenhum efeito.Tipos definidos pelo usuárioN?o? representado internamente como varbinary.FILESTREAMN?o? representado internamente como varbinary.Bottom of FormTabela 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 dadosComo 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 página. Por outro lado, se o espa?o obtido pela compacta??o de página for menor ao 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:Os dados s?o inseridos usando a sintaxe BULK INSERT;Os dados s?o inseridos usando INSERT INTO ... Sintaxe WITH (TABLOCK);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:Compacta??o de linha;Compacta??o de prefixo;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 pacta??o em nível de página utilizando a compacta??o por prefixoNesta 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: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;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;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árioApó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. A Figura 3 mostra a mesma página após a compacta??o por 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 dadosA 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 SQLMagazineGoUse SQLMagazineGoPosteriormente 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) GoAgora 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 IntSet @Cont=1While (@Cont <= 10000) Begin Insert Into Revistas Values ('SQL Magazine',@Cont,2009) Set @Cont +=1; EndGo-- Bloco 2 --Declare @Cont IntSet @Cont=1While (@Cont <= 10000) Begin Insert Into RevistasCompactadas Values ('SQL Magazine',@Cont,2009) Set @Cont +=1; EndGoAgora, 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'GoFigura 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 RevistasCompactadasRebuild With (DATA_COMPRESSION=PAGE)GoApó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'GoFigura 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 ao dados armazenados em uma tabela. Neste caso, observamos claramente e 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??oDepois de vários testes realizados, e a certeza 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_namesysname Nome da tabela ou exibi??o indexada.schema_namesysname Esquema da tabela ou exibi??o indexada.index_idint ID de um índice.0 = Heap1 = ?ndice clusterizado>1 = ?ndice n?o clusterizadopartition_numberint 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 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 7 e 8. O resultado é apresentado nas Figuras 8 e 9.Listagem 7. 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 8. 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?oAtravé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.De que se 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.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.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.Box Resumo:Devman: Veja o que você vai aprender adicionalmente neste artigo:Conceito sobre compacta??o em nível de linha(Row);Conceito sobre compacta??o em nível de página de dados(Page);Conceito sobre Parti??es inviduais.LinksLive Meeting Presentation on Data and Backup Compression. Meeting Presentation on Data and Backup Compression Wrapper.: SQL Server 2008 Data Management Overview (Level 100). de dados no SQL Server 2008 Online do SQL Server 2008. Server MSDN Experience. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download