Compressão de Dados – SQL Server 2008 v05
Propaganda
Compactação de dados com o Microsoft SQL Server 2008 Pedro Antonio Galvão Junior [email protected] 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. http://juniorgalvao-mvp2007.spaces.live.com 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. 1 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ário em outra página, sem gerar disperdí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 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 2 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: 3 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 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 34 [int] 32,767 [smallint] Redmond [char(50)] 4 bytes 2 bytes 50 bytes 1 byte 2 bytes 7 bytes WA [char(2)] 2 bytes 2 bytes Tabela 1. Compactação de dados aplicada em nível de linha. 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. 4 Afeta no Tipo de dado 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 Bit Sim Esse armazenamento é exatamente igual ao do formato de armazenamento vardecimal. 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 que smallmoney. Ele tem uma otimização de armazenamento semelhante à empregada para tipos de número inteiro. float Sim real Sim Os 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. Os 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. 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 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. 5 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. Quando datetime é 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 dados time. 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. 6 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 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. 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 nvarchar Não Caracteres de preenchimento à direita são removidos. Semelhante ao char. 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 FILESTREAM Não É representado internamente como varbinary. Não É representado internamente como varbinary. 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 7 (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 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 8 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 9 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. 10 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. 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 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. 11 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 12 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. 13 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 14 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 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. 15 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, 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: 16 [ @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 schema_name index_id sysname sysname int partition_number int size_with_current_com pression_setting (KB) size_with_requested_co mpression_setting (KB) bigint sample_size_with_curre nt_compression_setting (KB) bigint sample_size_with_requ ested_compression_setti bigint Nome da tabela ou exibição indexada. Esquema da tabela ou exibição indexada. ID de um índice. 0 = Heap 1 = Índice clusterizado >1 = Índice não clusterizado Número da partição. Retorna 1 para uma tabela ou índice não particionado. Retorna o tamanho atual da tabela, índice ou partição solicitada. 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. 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. Tamanho do exemplo criado usando a configuração da compactação solicitada e, se bigint 17 ng (KB) 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 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. 18 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. 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. 19 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. 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=enUS&EventID=1032342047&CountryCode=USCompressão de dados no SQL Server 2008 http://www.microsoft.com/sql/experience/ITPros.aspx?loc=pt&v=http%3a%2f%2fmschnlnine.vo.lln wd.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/ 20
