Estudo sobre o Armazenamento de Dados com Particionamento de Tabelas em Ambientes de Banco de Dados Relacionais

-0-

UNIVERSIDADE CRUZEIRO DO SUL

PROGRAMA DE PÓS-GRADUAÇÃO ESPECIALIZAÇÃO EM ENGENHARIA DE WEBSITES (Ênfase em .Net)

DIEGO CANDILE DALLE NOGARE

Estudo sobre o Armazenamento de Dados com Particionamento de Tabelas em Ambientes de Banco de Dados Relacionais

SÃO PAULO 2015

-1-

DIEGO CANDILE DALLE NOGARE

Estudo sobre o Armazenamento de Dados com Particionamento de Tabelas em Ambientes de Banco de Dados Relacionais

Monografia apresentada como quesito parcial para obtenção do título de especialista em Engenharia de Websites (Ênfase em .Net), da Universidade Cruzeiro do Sul, sob orientação do Prof. Dr. Juliano Schimiguel.

Orientador: Prof. Dr. Juliano Schimiguel

SÃO PAULO 2015

-2-

À Minha Família

-3-

Resumo O presente estudo tem como objetivo evidenciar uma técnica de performance com banco de dados chamada Particionamento de Tabelas, cujo benefício é realizar a separação física dos dados em sub-tabelas menores, facilitando a manutenção do administrador de banco de dados mas permanecendo transparente para o usuário final. Para realizar o comparativo, será criada uma base de dados no sistema gerenciador de banco de dados Microsoft SQL Server, e nesta base de dados serão criadas tabelas que receberão os dados. Serão comparados os metadados relacionados à tempo e utilização de CPU no trabalho diário de tabelas contendo dezenas de milhões de registros. A comparação de performance será feita com e sem o uso do objeto de estudo, que é o particionamento de tabelas. As tabelas serão praticamente idênticas, a única diferença entre elas será a utilização de particionamento em uma tabela e na outra não. Com base em conhecimento prévio do assunto pesquisado, existe a hipótese que o tempo de resposta e utilização de CPU serão menores com o uso do particionamento de tabelas. A precisão deste resultado, sendo favorável ou não ao que é estimado, será discutido na conclusão do trabalho. Palavras-chave: Banco de Dados, Particionamento de Tabela, Performance

-4-

Abstract This current work reach shows a database performance technique called partitioning table, who did the physical split of data into smaller sub-tables, enhancing and helping the maintenance for the database administrator but keeping clear to end user. To check and validate the comparison, a database in Microsoft SQL Server database management system will be created, and this tables created will receive the data. Will be compared metadata related to time and CPU utilization in the daily work tables containing tens of millions records. The performance of comparison will be made with and without the use of the subject matter, which is partitioning table. The tables are almost identical, the only difference among them is the use of partitioning on a table and the other not. Based on previous knowledge of the subject studied, there is the hypothesis that the response time and CPU utilization will be lower with the use of partitioning table. The precision of the result, and acceptable or not to that estimate will be fully discussed on conclusion.

Keywords: Database, Partition Table, Performance

-5-

LISTA DE FIGURAS Figura 1 - Ordem de atividades do Particionamento de Tabela ............................................................22 Figura 2 - Movimentação dos dados no Particionamento de Tabelas...................................................22 Figura 3 - Tabelas e dados particionados ............................................................................................24 Figura 4 - Páginas e Extents................................................................................................................25 Figura 5 - Propriedades do Banco de Dados ........................................................................................28 Figura 6 - Arquivos físicos dos Bancos de Dados ..................................................................................31 Figura 7 - Partição dos dados pela esquerda .......................................................................................32 Figura 8 - Partição dos dados pela direita ...........................................................................................32 Figura 9 - Páginas de dados convencionais .........................................................................................38 Figura 10 - Páginas de dados colunares ..............................................................................................39 Figura 11 - Comparação de tamanho dos índices ................................................................................41 Figura 12 - Ambiente do Servidor........................................................................................................44 Figura 13 - Arquitetura de Discos do Servidor......................................................................................45 Figura 14 - Unidades de discos lógicas ................................................................................................46 Figura 15 - Memória RAM das máquinas virtuais ................................................................................46 Figura 16 - Performance de discos diferentes ......................................................................................47 Figura 17 - Performance do disco de 2TB ............................................................................................48 Figura 18 - Comparativo de Páginas Lidas...........................................................................................59 Figura 19 - Comparativo de Tempo de Execução .................................................................................59 Figura 20 - Comparativo de Tempo de Processamento ........................................................................60

-6-

LISTA DE TABELAS Tabela 1 - Tabela inicial com os dados hipotéticos brutos. ...................................................................19 Tabela 2 - Agregação vertical de somatória ........................................................................................20 Tabela 3 - Agregação horizontal de somatória ....................................................................................20 Tabela 4 - Valores de intervalos para as partições...............................................................................33

-7-

SUMARIO 1 - Introdução ............................................................................................................. 9 1.1 - Contextualização do tema .............................................................................. 9 1.2 - Justificativa ..................................................................................................... 9 1.3 - Revisão inicial da literatura .......................................................................... 10 1.4 - Objetivo geral ............................................................................................... 13 1.5 - Objetivos específicos .................................................................................... 13 2 - Elementos sobre Particionamento em Banco de Dados ...................................... 15 2.1 - Considerações Iniciais ................................................................................... 15 2.2 - Cenários e Benefícios .................................................................................... 16 2.3 – Definição macro do Particionamento .......................................................... 21 2.4 – Aplicando particionamento de Tabela ......................................................... 22 2.5 – Elementos para o destino dos dados – File Group ....................................... 25 2.6 – As fronteiras do particionamento – Partition Function ............................... 31 2.7 – Estrutura interna do armazenamento – Partition Scheme .......................... 34 2.8 – Entendendo o armazenamento físico dos dados – Tabela Particionada ...... 35 2.9 – Melhoras na performance de recuperação do dado – Índice Particionado . 36 2.10 – Possibilidades de operações nas Partições ................................................ 37 2.11 – Técnicas efetivas para aumento de performance – ColumnStore Index .... 38 3 - Metodologia e Estudo de Caso ............................................................................ 43 3.1 - O Experimento .............................................................................................. 43 3.2 - Ambiente analisado ...................................................................................... 44 3.2.1 - O Servidor .......................................................................................................................44 3.2.2 - IOPS dos discos ...............................................................................................................46 3.2.3 - O SQL Server ...................................................................................................................48

3.3 - Criando o ambiente ...................................................................................... 49

-8-

3.4 - Prova da Hipótese ........................................................................................ 53 3.4.1 - Fase 1 - Reconstruindo o Índice .......................................................................................53 3.4.2 - Fase 2 - Excluindo o Índice ...............................................................................................54 3.4.3 - Fase 3 - Particionando os Dados da Tabela.......................................................................55

3.5 - Analisando os resultados .............................................................................. 58 3.6 Resultados ...................................................................................................... 60 4 – Considerações Finais .......................................................................................... 61 4.1 - Conclusão ..................................................................................................... 61 4.2 – Trabalhos Futuros ........................................................................................ 61 5 - Bibliografia ......................................................................................................... 62

-9-

1 - Introdução 1.1 - Contextualização do tema O particionamento de tabela consiste na divisão horizontal dos dados de uma tabela, baseado em um parâmetro pré-definido e que será responsável por fazer o corte, também conhecido como particionamento, dos dados desta tabela resultando em sub-tabelas. Para o usuário final este corte é transparente e não é necessário se preocupar em gerenciar as diversas sub-tabelas que são criadas com dados separados pelo parâmetro informado.

A técnica de particionamento de dados será aplicada em um ambiente relacional. Mas esta prática não se aplica somente à este tipo de ambiente, pode-se usar particionamento de tabelas também em ambientes analíticos através do SSAS SQL Server Analysis Services - que é a ferramenta responsável por criar e gerenciar ambientes de Business Intelligence na plataforma de dados da Microsoft. Também são cenários aplicáveis para particionamento de tabelas ambientes que trabalham com Hadoop, onde o MapReduce faz um processamento por trás do ambiente replicando os dados em diversos nós do conjunto de servidores que estão trabalhando e distribui a carga de dados para cada um destes servidores processar, isso também é conhecido como arquitetura MPP – Massive Parallel Processing - ou Processamento Paralelo Massivo. Estes são só alguns exemplos que também utilizam a teoria do particionamento de dados para benefício de performance em seu processamento. A utilização de clusters – aglomerados de servidores – para distribuição e processamento de carga pode ser interpretado por um viés que melhora a performance do ambiente relacional de banco de dados, porém esta abordagem não é o foco deste estudo.

1.2 - Justificativa Partindo do problema de gerenciar grande volume de dados em tabelas de estruturas únicas, este trabalho visa apresentar e provar a eficiência da técnica de particionamento de dados em diversas sub-tabelas facilitando o gerenciamento do administrador de banco de dados e permanecendo completamente transparente para o usuário final. Seja no desenvolvimento de códigos para interagir com esta tabela ou no uso de software para interação com os dados. Outro ponto de atenção explorado

- 10 -

por pesquisadores da área de banco de dados é a utilização de discos com velocidades mais altas para garantir o retorno dos dados mais importantes com uma velocidade acima do retorno de dados menos consumidos, considerados dados de histórico, que por sua vez são armazenados em discos menos nobres.

1.3 - Revisão inicial da literatura O tema central desta pesquisa não é recente, existem muitos trabalhos de outros pesquisadores que abordaram o assunto há alguns anos. Para provar cientificamente este estudo foram analisados os resultados encontrados em artigos científicos de pesquisadores nacionais e internacionais sobre o tema de particionamento de dados em bancos de dados relacionais, como também publicações em livros de autores renomados, fossem estes brasileiros ou estrangeiros, da área de plataforma de dados. No artigo publicado por RAO et al. (2002) é apresentado um estudo sobre automatização de seleção de particionamento utilizando recursos de paralelismo não explorados por grandes fabricantes e fornecedores de Bancos de Dados Relacionais. Foram utilizadas diversas técnicas de particionamento e paralelismo para aumentar a performance do ambiente testado no estudo. Esta automatização para definir a partição ótima, ou a mais próxima da ótima, leva em consideração principalmente o custo estimado de processamento. Quando MONTEIRO et al (2008) publicou seu artigo sobre o estado da arte para fazer auto-sintonia em projetos físicos de bancos de dados, seu foco principal foi a criação de índices sem intervenção humana. Apesar de comentar sobre o particionamento de tabelas e também de views indexadas, no qual chamou de visualizações materializadas, seu trabalho de tuning para o modelo físico dos bancos de dados apresenta diversas evidências que a performance pode melhorar sem a intervenção humana, através de índices. Ou então KUMAR et al. (2012) que publicou um artigo sobre agregações horizontais para ambientes de mineração de dados, uma vez que não é recomendado utilizar diretamente a base de dados de produção para atividades de Data Mining. Isso acontece porque muitas vezes os dados a serem analisados devem receber algumas agregações e isso não pode ser feito no ambiente de produção para não impactar o trabalho diário deste ambiente. É relatado em sua pesquisa diversas técnicas de agregações que resultam no que é conhecido como Redução de Dimensão.

- 11 -

Em seu artigo AGRAWAL et al. (2012) apresenta uma solução de automação para particionamento físico de dados. No referido estudo é explicado que particionamento horizontal normalmente é feito fisicamente no banco, mas também pode ser realizado logicamente. Esta estrutura lógica pode ser aplicada também para um particionamento vertical. Em ambas situações do particionamento ser logico, o resultado é obtido através de views indexadas, também conhecidas como views materializadas. Esta separação lógica ocorre porque ao indexar uma views as colunas de dados obrigatoriamente serão imutáveis, e uma replicação ordenada dos dados será escrita baseada no índice. Através de métricas coletadas com o Query Optimizer, foi possível integrar as melhores soluções para ambas situações de partição possíveis. Em seu artigo para o congresso MATUCK et al. (2012) apresentaram um estudo de análise propondo uma melhora no desempenho de processamento de dados em um banco de dados empresarial, no qual foi utilizado particionamento de tabelas como sendo o principal item a se avaliar no experimento. Nos resultados obtidos no estudo ficou comprovado que o particionamento de tabelas melhorou significantemente o resultado do retorno dos dados. Houveram situações no qual os dados retornaram em segundos, quando em outros momentos de retornos destes dados era necessário aguardar algumas horas para que a consulta fosse concluída. Como HERODOTOU, et al. (2013) relatou em seu artigo apresentando técnicas de performance para as soluções de tabelas particionadas horizontalmente no qual um grupo de registros é separado por intervalos pré-definidos. Mas comenta também que existe técnica de particionamento vertical onde o conjunto de dados são separados por colunas, também cita uma estrutura hierárquica que se beneficia de ambas técnicas. O foco de seu estudo foi o particionamento horizontal. LARSON (2000) patenteou pra Microsoft o conceito de particionamento de tabelas em memória volátil com uso de Hash nos dados. O uso de Hash em memória foi necessário para realizar as agregações por causa do baixo volume de memória volátil (RAM) disponível na época, então o recurso do particionamento transferia intervalos de dados para memória volátil, processava a agregação e retornava o valor agregado para o disco. No final do processamento, os valores agregados que foram particionados eram calculados entre si e retornavam o resultado consolidado para quem o solicitou.

- 12 -

Ainda neste campo de patentes da Microsoft, VOLODARSKY & NG (2007) criaram uma solução de particionamento de dados entre servidores, no qual um servidor de conexões recebe uma solicitação de dados e encaminha esta solicitação para o servidor correto dentro do grupo de servidores que estão neste aglomerado, ao término do processamento requisitado, o servidor de dados devolve para o servidor de conexão que por sua vez entrega a informação solicitada para o requisitante. Seguindo para outra patente da Microsoft, WEINERT et al (2010) inventaram como se deve usar o particionamento virtual de recursos escaláveis para melhorar o gerenciamento e processamento de requisições para os processamentos internos do servidor através da rede de computadores. Na defesa da sua dissertação para obtenção do título de mestre, ROCHA (2000), apresentou uma proposta para o Meta-Esquema Temporal em Estrela (MET*) que se beneficia para o armazenamento de dados do passado através de modelos temporais do banco de dados e também de técnicas de incremento de registros em Data Warehouses com SCD (Slowly Change Dimension) tipo 2. A técnica proposta consiste basicamente em manter os dados armazenados historicamente e fazer o controle através de registros chaves que garantirão o retorno solicitado como se fosse uma fotografia do dado naquele momento do tempo que a aplicação cliente necessita. Esta técnica possibilita uma análise detalhada sobre toda a vida daquele registro. RABELO (2008) utilizou da artimanha de desenvolver um protótipo de software que realiza o processo de distribuição de banco de dados de forma transparente para suportar a defesa para obtenção do seu título de mestre. Sua dissertação trabalha especificamente em realizar a distribuição de banco de dados através da rede de computadores de forma transparente, garantindo a integridade dos dados e tolerância a falhas. O trabalho proposto e defendido apresenta resultados valiosos para movimentação de dados através de XML. Fazendo um paralelo a este estudo, uma implantação de trabalhos futuros poderia seguir este formato de distribuição transparente e avaliar o quão positivo é esta técnica para o particionamento de tabelas de um banco de dados único. Uma abordagem multidimensional foi defendida por CARVALHO (2013) quando elaborou sua dissertação de mestrado em Recife. Sua defesa foi baseada na elaboração de um software que permite ao usuário final realizar mudanças estruturais no Data Warehouse sem a necessidade de um profissional de TI. Diversas técnicas foram implementadas na modelagem física dos dados, das quais o particionamento

- 13 -

de tabelas foi incluído. O resultado obtido com sua ferramenta permitiu que os usuários finais pudessem manipular os fatos que precisavam, com direcionamentos balizados pelo software, independente da necessidade de software e de como foi desenhado o Data Warehouse. Em sua dissertação de mestrado, COSTA (2013), utiliza um ORM (ObjectRelational Mapping) feito para Ruby chamado Active Record que auxiliou a execução do experimento para uma melhora na performance das aplicações particionando as tabelas de uso do sistema e armazenando os dados de histórico em sub-tabelas oriundas da tabela original. Um dos grandes desafios que enfrentou na defesa de sua dissertação foi utilizar o ORM Active Record que não suporta o particionamento de tabelas, e a solução foi modelar e desenvolver a biblioteca que estendeu o ORM e escrevia os dados nas partições necessárias através da chave definida pelo desenvolvedor do software. FILHO (2008) defendeu sua tese para obtenção do título de doutor, fazendo uma abordagem não intrusiva na estrutura física dos bancos de dados mas aplicando diversas técnicas automatizadas para melhoria do gerenciamento e das atividades internas do banco. Não se limitando exclusivamente à particionamento de tabelas, mas também explicando e pontuando sobre indexação e sintonia (tuning) de base de dados. Sua proposta foi realizar uma implementação genérica, podendo ser testado e aplicado sobre qualquer SGBD (Sistema Gerenciador de Banco de Dados).

A lista completa dos trabalhos avaliados, analisados e estudados pode ser encontrada em detalhes na seção Bibliografia, no final desta publicação.

1.4 - Objetivo geral O objetivo geral deste trabalho é comprovar através de experimentos que é possível melhorar a performance do SGBD e que o particionamento de tabelas tem o resultado mais favorável que o uso convencional do armazenamento e trabalho com os dados.

1.5 - Objetivos específicos Avançando mais detalhadamente na profundidade do assunto, os resultados específicos que serão encontrados passam a servir de base científica para comprovar

- 14 -

a eficiência desta técnica. Serão analisados e comparados os resultados, utilizando as tabelas convencionais e as tabelas particionadas, das seguintes métricas: 1) Quantificar a quantidade de páginas de dados lidas; 2) Medir o tempo total de Execução; 3) Analisar o tempo total de processamento (CPU).

- 15 -

2 - Elementos sobre Particionamento em Banco de Dados Apesar do tema ser amplamente estudado e pesquisado, existe um grupo de elementos que devem ser especificados e criados em uma ordem cronológica, para que o particionamento de tabelas seja efetivo. Neste capítulo são abordados os elementos necessários, alguns exemplos de aplicações reais e também códigos em T-SQL a serem seguidos para reprodução da pesquisa por quem se interessar.

2.1 - Considerações Iniciais Na década de 80 já existia o conceito de particionamento de dados, mas este era desenvolvido através de funções de Hash que faziam a quebra dos dados em Buckets, nos ensina ULLMAN (1988), ele também mostra como era possível informar qual o tamanho e quantos Buckets seriam criados para gerenciar os dados daquele determinado segmento que estavam trabalhando.

[...] Partitions allow a table (and its indexes) to be physically divided into mini-tables for administrative purpose and to improve query performance. The ultimate benefit of partitioning a table is that a query that requires a month of data from a table that has ten years of data can go directly to the partition of the table that contains data for the month without scanning other data [...] (KIMBALL & CASERTA, 2004 p. 224)

[...] Particionamento permite uma tabela (e seus índices) serem

divididos

fisicamente

em

mini-tabelas

para

fins

administrativos e melhora de performance na consulta. O maior benefício de particionar uma tabela é que uma consulta que precise de dados de um mês de uma tabela que possui 10 anos de dados pode ir diretamente na partição da tabela que contém os dados daquele mês e não precisa acessar a tabela inteira [...] (KIMBALL & CASERTA, 2004 p. 224)

- 16 -

Este caso que, apresentado por KIMBALL & CASERTA (2004), remete o leitor ao princípio do conceito do particionamento de dados e melhor utilização dos recursos computacionais para retorno de informação específica, evitando consumir páginas de dados desnecessárias e processamento de dados que não terão real utilidade para o usuário.

O particionamento de dados no SQL Server como é conhecido hoje foi implementado na versão 2005 do produto, que sofreu uma reestruturação de arquitetura total comparado com a versão 2000 do produto. Segundo HOTEK (2009) os dados anteriormente ficavam armazenados em um mesmo filegroup que era determinado e gerenciado pelo próprio SQL Server. Ainda segundo HOTEK (2009) o particionamento não se aplica somente à tabelas, mas pode ser estendido também a índices e views, permitindo que todos estes objetos sejam separados em diversos filegroups especificados pelo administrador.

STANEK (2009) apresenta uma definição de fácil entendimento sobre o particionamento de objetos. Segundo STANEK (2009) é o particionamento que faz uma separação horizontal dos dados existentes naquele objeto, permitindo que menos dados sejam gerenciados pelo objeto em questão. A criação de diversos objetos remete à um processamento distribuído onde cada objeto processa e retorna o dado que lhe foi solicitado. Ele ainda completa com uma lista de etapas a serem seguidas:

1) Criar uma função de partição (Partition Function) para especificar o objeto que será particionado; 2) Criar o Partition Scheme e informar o posicionamento das partições dentro dos Filegroups; 3) Criar ou modificar o objeto para usar o Partition Scheme criado.

2.2 - Cenários e Benefícios HOTEK (2009) relata que um cliente estava com sérios problemas de concorrência em seus servidores, impactando negativamente a experiência dos usuários na operação de atividades diárias. Os administradores de banco de dados

- 17 -

tentavam resolver o problema da rotina diária que demorava entre 3 e 4 horas, e a rotina semanal que chegava a picos de 22 horas de processamento. Ao implementar o particionamento de tabelas e tarefas de Split, Merge e Switch entre as tabelas de produção e tabelas temporárias, os processamentos ficaram abaixo de 5 segundos cada.

Imagine hipoteticamente o caso de uma tabela de Produtos de um e-Commerce que fica 2 horas por dia lenta porque está reorganizando ou reconstruindo o índice, ou fica travada quando estão executando algum relatório. Esse tipo de tabela pode ser particionada para melhorar o tempo de resposta das consultas e garantir que o eCommerce continue fazendo suas vendas online.

Ainda pensando em relatórios, a quantidade de Locks e Waits pode causar uma lentidão grande no seu sistema de gerenciamento de banco de dados nos mostra DAVE & MORELAN (2011). Em um cenário sem particionamento, quando uma tabela com dezenas de milhões de registros anuais precisa agregar as vendas por mês, o processamento irá realizar um Table Scan com todas as linhas da tabela e fará o somatório, nos ensina AMORIM (2011). Imaginando este mesmo cenário em um ambiente particionado, cada partição faz suas agregações e no final do processamento você terá uma resposta mais rápida e seu ambiente continuou respondendo com rapidez, sendo que cada partição processou somente o que era devido ao seu filegroup. Retomando o foco aos discos, STANEK (2008) nos ensina que em uma SAN – Storage Area Network – os discos de dentro de um RAID devem ser idênticos e estar alinhados para garantir uma melhor performance. Em cenários onde esta arquitetura não se aplica, é possível deixar os discos mais nobres, como SSD ou SAS de 10 ou 15 mil rotações para os dados mais acessados, e os dados de histórico podem ficar armazenados em discos menos nobres como SATA ou eSATA de 7200 rotações.

Para ambientes de Business Intelligence que trabalham com SQL Server Analysis Services, seja na modelagem tabular ou multidimensional também se aplicam o conceito de particionamento de dados. Porém RUSSO, FERRARI & WEBB (2012) nos mostram que na modelagem tabular usando o modelo em memória a

- 18 -

estratégia

de

particionamento

é

focada

exclusivamente

para

melhorar

o

processamento dos dados uma vez que você faz a manutenção em parte dos dados e não na tabela como um todo.

HARINATH ET AL. (2012) explica o mesmo princípio aplicado à modelagem tabular comentado acima, porém para a modelagem multidimensional, onde a melhoria da performance em um cenário onde a tabela fato é particionada com seus grupos de medidas – measure groups – possibilitando o uso de paralelismo no Analysis Services. Este particionamento impacta diretamente na atualização dos dados diferenciais, evitando que todo o cenário existente seja processado, uma vez que os dados que estão em outras partições já foram processados e não tiveram alterações.

KIMBALL & CASERTA (2004) apresentam o cenário de particionamento para ambientes de Data Warehouses, e mostram como o benefício de utilizar esta técnica para acessar blocos de dados específicos separados pelos particionamentos, e evitar consumo desnecessário de recursos computacionais.

HERODOTOU et al. (2011) mantém a linha para bancos de dados relacionais e apresenta técnicas genéricas de retorno da informação particionada que funcionam para os mais diversos bancos de dados fornecidos por grandes empresas como Sybase, IBM, Oracle e Microsoft. A abordagem de sua pesquisa é comparativa e explicativa de quando se deve retornar dados fazendo a união (UNION) das minitabelas através de código explicito, e quando se deve fazer consolidação (JOIN) das mesmas. A abordagem genérica é baseada no padrão ANSI, que desde a década de 70 quando foi criado pela IBM, serve de base para a grande maioria dos sistemas gerenciadores de banco de dados, permitindo uma interoperabilidade e similaridade de código entre todos os sistemas dos fabricantes supracitados, desconsiderando a extensibilidade criada por cada empresa como diferencial competitivo.

KUMAR & KRISHNAIAH (2012) mudam completamente o foco dos estudos já apresentados nesta pesquisa, e apresentam resultados sobre algumas técnicas conhecidas em bancos de dados como o PIVOT, que é a artimanha para transpassar dados que estão em linhas para colunas, e também o método de CASE WHEN que

- 19 -

faz comparações de valores específicos e substitui seus resultados. Mas o principal ponto do referido estudo é a questão de agregação horizontal, que pode aplicar diversos métodos agregadores como SUM (somatório), AVG (média), MIN (mínimo valor), MAX (máximo valor) ou COUNT (contagem de registros). As agregações passam a diminuir a quantidade de registros na tabela consolidando os valores a partir da agregação. Para exemplificar a técnica, os pesquisadores apresentam uma agregação de SUM para simples entendimento, como representado nas três tabelas a seguir.

K

D1

D2

A

1

3

X

9

2

2

Y

6

3

1

Y

10

4

1

Y

0

5

2

X

1

6

1

X

NULL

7

3

X

8

8

2

X

7

Tabela 1 - Tabela inicial com os dados hipotéticos brutos.

Fonte: Artigo: "Horizontal Aggregations in SQL to Prepare Data Sets for Data Mining Analysis" de Kumar & Krishnaiah

Acompanhe na Tabela 2 que os valores das colunas D1 e D2 são representados em conjunto, cobrindo todas as possibilidades existentes na Tabela 1 que possui os valores inicial. O valor da coluna A é o somatório dos valores encontrados na combinação da coluna D1 e D2. Atente-se ao valor da coluna D1 = 3 e D2 = X, sendo a coluna A = 17. Esta é a somatória do valor da coluna A da tabela inicial, dos registros existentes na linha 1 e 7, que somados totalizam o valor 17.

- 20 -

D1

D2

A

1

X

NULL

1

Y

10

2

X

8

2

Y

6

3

X

17

Tabela 2 - Agregação vertical de somatória

Fonte: Artigo: "Horizontal Aggregations in SQL to Prepare Data Sets for Data Mining Analysis" de Kumar & Krishnaiah

A agregação horizontal é o passo seguinte, veja o resultado na Tabela 3 quando a coluna A deixa de existir e seus valores passam a ser representados por outras colunas criadas a partir de técnicas de PIVOT, passando a ser D2X e D2Y, e a coluna D1 mantendo sua representatividade. O resultado é uma matriz que engloba a agregação de soma de todo o cenário apresentado na Tabela 1 inicial. Veja como é esta terceira representação dos dados.

D1

D2X

D2Y

1

NULL

10

2

8

6

3

17

NULL

Tabela 3 - Agregação horizontal de somatória

Fonte: Artigo: "Horizontal Aggregations in SQL to Prepare Data Sets for Data Mining Analysis" de Kumar & Krishnaiah

Neste exemplo de KUMAR & KRISHNAIAH (2012), fica simples de entender o processo pois se tratam de poucas variáveis (colunas) e poucas observações (linhas ou registros). Porém, imagine ter que trabalhar com um Data Warehouse que possui centenas de variáveis e milhões de observações. Como pode-se trabalhar com este mesmo conceito para deixar o cenário mais simples de se trabalhar? Uma técnica amplamente aceita para grande volume de dados é o uso de redução de dimensão, e LAROSE & LAROSE (2015) apresentam uma pesquisa com uma definição interessante sobre o tema, acompanhe:

- 21 -

“[...] For example, several predictors might fall naturally into a single group (a factor or a component), which addresses a single aspect of the data. For example, the variable saving account balance, checking account balance, home equity, stock portfolio value, and 401k balance might all fall together under the single component, assets.” (LAROSE & LAROSE. 2015 p. 93) “[...] Por exemplo, muitos preditores podem se enquadrar em um mesmo grupo (um fato ou um componente), que aborda um aspecto único do dado. Por exemplo, o saldo da poupança variável, saldo da conta corrente, equidade da casa, valor de ações da carteira, e um balanço de 401k podem todos fazer parte de um mesmo componente, ativos.” (LAROSE & LAROSE. 2015 p. 93)

Estas são formas e técnicas que permitem uma redução de valores de duas formas diferentes, mas que impactam diretamente na quantidade de retorno existente em sua consulta por causa da agregação.

2.3 – Definição macro do Particionamento Analisando o processo do início ao fim, os passos necessários para criar a arquitetura de particionamento fazem a atividade ficar estruturada e de fácil entendimento.

O primeiro passo é criar o banco de dados e a tabela, tabela esta que pode ser populada com os registros e sofrer o particionamento depois de já existirem registros dentro dela, ou pode sofrer o particionamento antes de ser populada. O passo seguinte é criar os Filegroups para cada particionamento, informando o caminho físico que cada dado será armazenado.

O próximo passo é criar o Partition Function, que armazena o algoritmo responsável por controlar o Key Partition e encaminhar o dado para a partição correta.

- 22 -

A penúltima tarefa é criar o Partition Scheme que liga um Partition Function à um Filegroup.

Figura 1 - Ordem de atividades do Particionamento de Tabela

Por fim, as atividades de movimentação de dados entre as partições podem acontecer, causando um Split, Merge ou Switch dos dados.

Figura 2 - Movimentação dos dados no Particionamento de Tabelas

Nas sessões seguintes serão apresentadas estas tarefas em detalhes.

2.4 – Aplicando particionamento de Tabela Criar uma tabela particionada é parecido com criar uma tabela não particionada, a informação que define o local onde o objeto será armazenado fisicamente é informado na clausula ON da tabela, que quando não é informado, aponta por padrão para o Filegroup primário. Quando se tem a tabela criada, e quer fazer o particionamento dela, é necessário executar uma alteração estrutural de DDL – Data Definition Language – para alterar o local padrão para o Partition Scheme, que é responsável por apontar o local físico correto.

[...] A chave está na cláusula ON. Em vez de especificar um filegroup, você especifica um esquema de partição. O esquema de partição já está definido com um mapeamento para a função

- 23 -

de partição. Portanto, você precisa especificar a coluna da tabela, a chave de particionamento à qual a função de partição será aplicada. (HOTEK. 2008 p.134)

Para exemplificar o presente estudo, um banco de dados será criado no filegroup padrão, e em seguida uma tabela será criada para receber dados e simular um ambiente legado sem particionamento.

Criar o banco de dados informando qual será o caminho de cada arquivo CREATE DATABASE dbMuseu ON PRIMARY ( NAME = N'dbMuseu', FILENAME = N'M:\dbMuseu.mdf') LOG ON ( NAME = N'dbMuseu_log', FILENAME = N'L:\dbMuseu_log.ldf') GO

Alterar o banco de dados para diminuir a quantidade de LOG pelo recovery model ALTER DATABASE dbMuseu SET RECOVERY SIMPLE GO

Criar a tabela que recebe os dados aleatórios USE dbMuseu GO CREATE TABLE tbVisitas (id INT IDENTITY(1,1) PRIMARY KEY ,nome VARCHAR(50) ,data DATE ,valor DECIMAL(5,2)) GO

Inserir 1 milhão de registros aleatórios INSERT INTO tbVisitas (nome, data, valor) VALUES (newid(), CONVERT(date, convert(varchar(15),'2014-' + CONVERT(varchar(5),(convert(int,rand()*12))+1) + '-' + CONVERT(varchar(5),(convert(int,rand()*27))+1) )), CONVERT(decimal(5,2),rand()*20) ) GO 1000000

- 24 -

Para elucidar o entendimento pleno do experimento deste estudo, será criada uma modelagem para armazenamento de visitantes à um museu. A separação dos dados particionados será realizada para cada mês de visitação.

Para exemplificar este armazenamento particionado horizontalmente, repare como os dados devem ficar armazenados na Figura 3. Esta figura representa a ideia dos dados que estavam na tabela Visitas foram divididos em 12 mini-tabelas, cada uma armazenando os dados de um mês específico em um filegroup diferente. A tabela azul armazena visitas de Janeiro, a tabela verde armazena de Fevereiro e a tabela laranja armazena visitas de Dezembro. Porém, a tabela preta representa a consulta de todas as visitas do ano inteiro.

Figura 3 - Tabelas e dados particionados

Para o usuário que está consumindo os dados desta tabela Visita, ele não sabe, necessariamente, que existe o particionamento e não precisa implementar nenhuma busca específica para retornar os dados que estão em cada separação.

- 25 -

Para o usuário que necessita dos dados de meses diferentes o próprio sistema gerenciador de banco de dados cuida do processo internamente e entrega os dados solicitados para o usuário.

2.5 – Elementos para o destino dos dados – File Group Mesmo o Filegroups sendo o destino final do processo de criar a Partition Table pois é onde os dados serão armazenados de fato, é necessário criá-los primeiro. Após a criação dos filegroups pode-se voltar no processo lógico e então criar o Partition Function e o Partition Scheme.

Antes de criar o filegroup, vale relembrar um pouco de conceito do sistema gerenciador de banco de dados da Microsoft, o SQL Server. Todos os dados de uma tabela são armazenados em um agrupador lógico interno chamado Página que possui, sem exceção, 8Kb de tamanho. Independente de uma página estar com apenas 1 registro, ou totalmente preenchida com dados, ela ocupa 8Kb de tamanho. Outra característica é que dentro de uma página são armazenados somente dados de uma mesma tabela. Se neste exemplo abaixo, na Figura 4, existisse somente 1 funcionário e 1 produto, existiriam 2 páginas criadas. O conjunto de 8 páginas é chamado de Extent, ocupando 64Kb de tamanho (8Kb de cada uma das 8 páginas).

Figura 4 - Páginas e Extents

Os Databases são criados no filegroup Default se não for especificado em qual filegroup deve ser criado. Por padrão, no SQL Server, os filegroups são armazenados

- 26 -

dentro

da

pasta \Program

Files\Microsoft

SQL

Server\MSSQL.\MSSQL\DATA com o nome do Database que você criou. Se você quiser especificar o filegroup na hora de criar seu Database, deve adicionar o código T-SQL junto à criação do seu Database. Veja abaixo a criação do dbMuseu_2 informando o filegroup via código T-SQL.

Criação do segundo banco de dados CREATE DATABASE dbMuseu_2 ON ( NAME = dbMuseu_2, FILENAME = 'M:\dbMuseu_2.mdf') LOG ON ( NAME = dbMuseu_log, FILENAME = 'M:\dbMuseu_2_log.ldf') GO

Esta criação do dbMuseu_2 é só para exemplificar como especificar o filegroup na hora da criação do Database, e ele não será usado para a conclusão dos testes de performance usados para provar a melhoria no ambiente.

Quando se cria mais de um filegroup por Database, os novos arquivos de filegroup possuem a extensão .NDF ao invés de .MDF. Também é criado o arquivo .LDF que são os arquivos de LOG das transações que o Database processa. Os Databases já criados, tanto o dbMuseu (criado anteriormente) quanto o dbMuseu_2 criado agora, possuem apenas 1 Filegroup, o Primary.

Para a criação dos novos filegroups para o banco de dados que serão usados no teste e prova da hipótese, o dbMuseu deve ser modificado para separar os dados da tabela tbVisitas. No primeiro momento deve-se definir e criar os nomes lógicos dos Filegroups ao Database. No exemplo deste trabalho já foi criado o dbMuseu, então deve-se alterar o banco de dados ao invés de criar um novo. Veja o código T-SQL abaixo:

- 27 -

USE MASTER GO ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER GO

DATABASE DATABASE DATABASE DATABASE DATABASE DATABASE DATABASE DATABASE DATABASE DATABASE DATABASE DATABASE

dbMuseu dbMuseu dbMuseu dbMuseu dbMuseu dbMuseu dbMuseu dbMuseu dbMuseu dbMuseu dbMuseu dbMuseu

ADD ADD ADD ADD ADD ADD ADD ADD ADD ADD ADD ADD

FILEGROUP FILEGROUP FILEGROUP FILEGROUP FILEGROUP FILEGROUP FILEGROUP FILEGROUP FILEGROUP FILEGROUP FILEGROUP FILEGROUP

[FG2014_01_JAN] [FG2014_02_FEV] [FG2014_03_MAR] [FG2014_04_ABR] [FG2014_05_MAI] [FG2014_06_JUN] [FG2014_07_JUL] [FG2014_08_AGO] [FG2014_09_SET] [FG2014_10_OUT] [FG2014_11_NOV] [FG2014_12_DEZ]

Para confirmar os nomes lógicos adicionados ao Database, uma opção é ir até as propriedades do banco, clicando com o botão direito em dbMuseu e apontando para Properties, e então nas opções da esquerda, vá até Filegroups através do SQL Server Management Studio como na Figura 5, ou então através do código T-SQL abaixo: USE dbMuseu GO sp_helpfilegroup GO

- 28 -

Figura 5 - Propriedades do Banco de Dados

Após a confirmação de criação dos nomes lógicos, o passo seguinte é o momento de criar os arquivos físicos e associá-los à esses nomes lógicos dos filegroups. Para isso, é necessário realizar outro código DDL – Data Definition Language – de Alter Database, veja esse código abaixo a associação de um arquivo físico a cada nome lógico:

- 29 -

Adicionar os Filegroups secundários ao banco original USE MASTER GO ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_01_JAN',FILENAME = 'M:\FG2014_01_JAN.ndf') TO FILEGROUP [FG2014_01_JAN] GO ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_02_FEV',FILENAME = 'M:\FG2014_02_FEV.ndf') TO FILEGROUP [FG2014_02_FEV] GO ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_03_MAR',FILENAME = 'M:\FG2014_03_MAR.ndf') TO FILEGROUP [FG2014_03_MAR] GO ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_04_ABR',FILENAME = 'M:\FG2014_04_ABR.ndf') TO FILEGROUP [FG2014_04_ABR] GO ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_05_MAI',FILENAME = 'M:\FG2014_05_MAI.ndf') TO FILEGROUP [FG2014_05_MAI] GO ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_06_JUN',FILENAME = 'M:\FG2014_06_JUN.ndf') TO FILEGROUP [FG2014_06_JUN] GO ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_07_JAN',FILENAME = 'M:\FG2014_07_JUL.ndf') TO FILEGROUP [FG2014_07_JUL] GO ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_08_AGO',FILENAME = 'M:\FG2014_08_AGO.ndf') TO FILEGROUP [FG2014_08_AGO] GO

- 30 -

ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_09_SET',FILENAME = 'M:\FG2014_09_SET.ndf') TO FILEGROUP [FG2014_09_SET] GO ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_10_OUT',FILENAME = 'M:\FG2014_10_OUT.ndf') TO FILEGROUP [FG2014_10_OUT] GO ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_11_NOV',FILENAME = 'M:\FG2014_11_NOV.ndf') TO FILEGROUP [FG2014_11_NOV] GO ALTER DATABASE dbMuseu ADD FILE (NAME = 'FG2014_12_DEZ',FILENAME = 'M:\FG2014_12_DEZ.ndf') TO FILEGROUP [FG2014_12_DEZ] GO

Neste

código

acima,

é

criado

fisicamente

o

filegroup FG2014_01_JAN.NDF baseado no nome lógico criado anteriormente, e que receberá somente os dados de Janeiro/2014 quando particionarmos os dados.

Acompanhe na Figura 6 a pasta padrão de armazenamento de dados do SQL Server com os arquivos físicos já criados:

- 31 -

Figura 6 - Arquivos físicos dos Bancos de Dados

Uma alternativa para verificar a criação dos arquivos, é executar o código TSQL abaixo: USE dbMuseu GO sp_helpfile GO

2.6 – As fronteiras do particionamento – Partition Function Segundo Hotek (2008) uma Partition Function define os pontos de corte, ou fronteiras – bordas, que serão utilizados para dividir os dados em um Partition Scheme. A borda pode ser pela esquerda ou pela direita, esta definição faz com que o valor de corte seja mantido na borda escolhida.

Exemplificando o cenário com a tabela de visitas criada no banco de dados do museu, se fosse criado o particionamento dos dados pelo campo data, poderia ser

- 32 -

configurada a borda pela esquerda (Figura 7, em azul) ou pela direita (Figura 8, em verde). O particionamento pela esquerda força que o valor informado para a borda seja colocado como primeiro valor da partição criada. Já o particionamento pela direita força que o valor informado para a borda seja fixado como último valor da partição anterior. Exemplificando com alguns dados, veja o onde ocorre o particionamento dos dados entre a partição 3 e 4 e o valor do dia 01/06/2014.

Figura 7 - Partição dos dados pela esquerda

Figura 8 - Partição dos dados pela direita

- 33 -

Para facilitar o entendimento e a criação das bordas, será utilizado o corte pela direita, mantendo os dias dos meses completos em cada partição. Veja o código abaixo: USE dbMuseu GO CREATE PARTITION FUNCTION MuseuPorMes(date) AS RANGE RIGHT FOR VALUES ('20140101', '20140201', '20140301' ,'20140401', '20140501', '20140601' ,'20140701', '20140801', '20140901' ,'20141001', '20141101', '20141201') GO

Com este código foram criados doze Partition Functions, que representam respectivamente os dados oriundos de cada mês de visitação ao museu. Desta forma, um Partition Function oculto é criado para armazenar qualquer dado existente com data inferior à 01/01/2014 pois a borda de corte é feita pela direita e estes dados devem ser armazenados, obrigatoriamente, em algum lugar.

Para simplificar o entendimento, acompanhe a tabela abaixo com os valores de intervalos para cada partição, inclusive com a partição oculta comentada acima.

# da Partição Valor Mínimo

-∞

Valor Máximo

1 2 3 4 5 6 7 8 9 10 11 12

01/01/2014 01/02/2014 01/03/2014 01/04/2014 01/05/2014 01/06/2014 01/07/2014 01/08/2014 01/09/2014 01/10/2014 01/11/2014

31/12/2013 31/01/2014 28/02/2014 31/03/2014 30/04/2014 31/05/2014 30/06/2014 31/07/2014 30/08/2014 30/09/2014 31/10/2014 30/11/2014

13

01/12/2014

∞

Tabela 4 - Valores de intervalos para as partições

- 34 -

Após a criação das partições, é possível acompanhar sua estrutura e bordas através dos comandos T-SQL, onde o primeiro apresenta os dados da função e o segundo seus intervalos. SELECT * FROM sys.partition_functions SELECT * FROM sys.partition_range_values

2.7 – Estrutura interna do armazenamento – Partition Scheme Como explica Hotex (2008), um Partition Scheme define a estrutura de armazenamento e os File Groups que serão usados pelos Partition Functions criados previamente. O Partition Scheme é responsável por encaminhar os dados para os respectivos File Groups, sempre seguindo o algoritmo de separação de dados pelas bordas criados no Partition Function. Quando foi feita a quebra dos dados no Partition Function MuseuPorMes apresentado anteriormente, 12 partições (uma para cada mês) foi definida, incluindo a partição oculta. Para cobrir todos os dados possíveis, serão criados 13 Partition Scheme, um para cada mês e um extra para os dados extras. O 13º Partition Scheme irá receber os dados abaixo de 01/01/2014, e irá encaminhar para o File Group informado, no exemplo de código T-SQL abaixo, os dados acima desta data serão enviados para o File Group PRIMARY. Veja o código: CREATE PARTITION SCHEME MuseuPorMesScheme AS PARTITION MuseuPorMes TO ('FG2014_01_JAN','FG2014_02_FEV','FG2014_03_MAR' ,'FG2014_04_ABR','FG2014_05_MAI','FG2014_06_JUN' ,'FG2014_07_JUL','FG2014_08_AGO','FG2014_09_SET' ,'FG2014_10_OUT','FG2014_11_NOV','FG2014_12_DEZ' ,'PRIMARY' ) GO

Lembrando que o MuseuPorMes é o nome do Partition Function criado anteriormente.

- 35 -

Caso todas as partições estivessem apontando para o mesmo Filegroup, seria possível escrever uma sintaxe mais simples, apontando todas as partições para o Filegroup PRIMARY. Veja como seria esse código: CREATE PARTITION SCHEME MuseuPorMesScheme_v2 AS PARTITION MuseuPorMes ALL TO ([PRIMARY]) GO

Para acompanhar as partições criadas, pode-se executar o comando T-SQL a seguir SELECT * FROM sys.partition_schemes

2.8 – Entendendo o armazenamento físico dos dados – Tabela Particionada Mais uma vez baseado em Hotek (2008), a criação de uma tabela, índice ou view indexada particionada é semelhante à criação destes objetos não particionados. Todos objetos de banco de dados criados possuem uma cláusula ON que especifica ao SGBD qual é o File Group que aquele objeto deve ser criado. Esta cláusula é rotineiramente omitida, criando o objeto no File Group padrão, que normalmente é o PRIMARY.

O segredo do particionamento de tabelas está justamente no emprego da cláusula ON durante a criação, ou alteração, do seu objeto informando onde o SQL Server deve criá-lo.

Para a criação da tabela particionada, deve-se informar o Partition Scheme e a coluna da tabela que será utilizada para fazer a quebra dos dados. No exemplo do Museu que está sendo apresentado neste estudo, a cláusula ON da criação do objeto deve receber o nome MuseuPorMesScheme e também a coluna Data, ficando como o código T-SQL:

- 36 -

CREATE TABLE tbVisitas_V2 (id INT IDENTITY(1,1) ,nome VARCHAR(50) ,data DATE ,valor DECIMAL(5,2)) ON MuseuPorMesScheme(data) GO

Com o uso do Partition Scheme na criação do objeto tbVisitas_V2, qualquer dado informado à tabela será encaminhado para o Partition Function definido através das bordas baseados na coluna Data, que por sua vez armazena o registro no File Group específico.

Para fins de comparação entre ambas tabelas, o próximo código T-SQL lê todos os dados da tabela original e escreve na tabela particionada, encaminhando os registros para o File Group correto: INSERT INTO tbVisitas_V2(Nome,Data) SELECT Nome,Data FROM tbVisitas GO

2.9 – Melhoras na performance de recuperação do dado – Índice Particionado O índice particionado segue a mesma estrutura e lógica da criação da tabela particionada, exigindo a utilização da cláusula ON para informar o Partition Scheme e a coluna que será particionado o dado. Como explica Hotek (2008) ao se especificar a criação de um índice em uma tabela particionada o SQL Server inclui a chave de particionamento na definição de cada índice, permitindo que seja particionado na mesma estrutura da tabela, este processo interno é chamado de alinhamento de índices e tabelas.

O código T-SQL para criar o índice lógico na tabela pode ser como este apresentado abaixo: CREATE NONCLUSTERED INDEX idx_Visitas ON TbVisitas(Data) ON MuseuPorMesScheme(data) GO

- 37 -

2.10 – Possibilidades de operações nas Partições Quando se particiona uma tabela ou índice, é comum resolver um problema existente. Porém, com o passar dos tempos, este problema pode mudar fazendo com que seu ambiente também precise de mudanças. Mudanças estas que podem ser de Split, Merge ou Switch, como apresentado na Figura 2.

Os operadores Split e Merge criam e removem, respectivamente, novas bordas à um Partition Function. Já o operador Switch move partições entre tabelas.

Segundo HOTEK (2008), o operador Split cria um novo ponto de borda (fronteira) à uma Partition Function, enquanto o operador Merge elimina um ponto de borda fazendo com que dados de agrupamentos diferentes sejam unidos em uma única partição. O Operador Switch permite trocar partições entre as tabelas de maneira expansível, sem onerar o banco de dados.

O código T-SQL para realizar um Split no Partition Function e criar um novo agrupador de data para o mês de Janeiro/2015 pode ser acompanhado abaixo: ALTER PARTITION FUNCTION MuseuPorMes(date) SPLIT RANGE (‘20150101’) GO

O Código T-SQL para realizar um Merge no Partition Function e remover o agrupador para o mês de Setembro/2014, fazendo com que os dados deste mês passem a compor a partição de Agosto/2014 (por causa da borda à direita), segue este abaixo: ALTER PARTITION FUNCTION MuseuPorMes(date) MERGE RANGE (‘20140901’) GO

O Código T-SQL para realizar um Switch e enviar uma partição para uma outra tabela, no caso para enviar a partição 5 para a tabela tbVisitas_V2 é como este: ALTER TABLE dbMuseu SWITCH PARTITION 5 to tbVisitas_V2 GO

- 38 -

2.11 – Técnicas efetivas para aumento de performance – ColumnStore Index O ColumnStore Index foi lançado junto ao SQL Server 2012, este índice usa um padrão de compressão de dados proprietário da Microsoft e altera o formato de armazenamento dos dados nas páginas do índice. A primeira vez que um registro é inserido no índice, ele registra o dado bruto, qualquer outra aparição deste mesmo dado dentro do índice, o SQL faz um apontamento de memória para o primeiro registro, diminuindo significativamente o tamanho da página com esta arquitetura.

Este novo formato de índice não chegou para substituir os já convencionais Index Clustered (Físico) e Non-Clustered (Lógico), ele vem para atender um outro cenário. O armazenamento do ColumnStore Index altera a escrita dos dados do índice de forma diferente do que é padrão de encontrar em um padrão linear registrando a tupla inteira em uma mesma página de dados, como apresentado na Figura 9.

Figura 9 - Páginas de dados convencionais

Para um armazenamento em formato colunar, cada coluna da tupla é mantida em uma mesma página, gerando mais páginas de dados mesmo com um armazenamento de uma única tupla na tabela. Uma representação visual do uso de páginas na criação do ColumnStore Index é apresentado na Figura 10.

- 39 -

Figura 10 - Páginas de dados colunares

[...] Partitioning data enable database administrator to manage and access subsets of their data quickly and efficiently while maintaining the integrity of the entire data collection […] Partition Switching is fully supported with a ColumnStore Index and is a practical way for update data. (MISTRY & MISNER, 2012 p. 48 e 49) […] Particionamento de dados permite que o administrador de banco de dados gerencie e acesse conjunto de dados de forma rápida e eficiente enquanto mantém a integridade da sua coleção de dados [...] Partition Switching é totalmente suportado com um ColumnStore Index e é um caminho prático para atualização de dados. (MISTRY & MISNER, 2012 p. 48 e 49)

Os ganhos de performance com o uso correto do ColumnStore Index varia entre 10 e 100X, segundo materiais promocionais da fabricante Microsoft. Porém, em alguns casos reais já presenciados no mercado de trabalho, chegar a retornos 400X mais rápidos. Porém o ColumnStore Index na versão 2012 do SQL Server possui uma limitação, a utilização deste índice em uma tabela transforma a mesma em somente leitura, impedindo manutenção trivial nos dados já existentes.

Este cenário de dados no formato de somente leitura remete o ambiente transacional à arquiteturas de ambientes para Data Warehouse, onde a informação armazenada sofre manutenção incremental em determinados momentos do dia e

- 40 -

normalmente não são operações de atualização ou exclusão de dados. Os casos mais comuns são incrementados somente uma vez por dia, geralmente de madrugada ou em momentos de menor consumo por parte de clientes. Para comprovar a criação de cada coluna da tupla em uma página de dados, veja o exemplo de código abaixo: USE dbMuseu GO /*Cria a tabela*/ CREATE TABLE tbVisitas (id INT IDENTITY(1,1) PRIMARY KEY ,nome VARCHAR(50) ,data DATE ,valor DECIMAL(5,2)) GO /*Insere um registro*/ INSERT INTO tbVisitas (nome, data, valor) VALUES (newid(), CONVERT(date, convert(varchar(15),'2014-' + CONVERT(varchar(5),(convert(int,rand()*12))+1) + '-' + CONVERT(varchar(5),(convert(int,rand()*27))+1) )), CONVERT(decimal(5,2),rand()*20) ) /*Duplica a tabela com o V2*/ select * into tbVisitas_v2 from tbVisitas /*Compara o tamanho das tabelas*/ sp_spaceused tbVisitas GO sp_spaceused tbVisitas_v2 /*Cria o ColumnStore Index*/ create nonclustered columnStore index idxVisitas on tbVisitas_v2 (id,nome,data,valor) /*Verifica quais índices tem nas tabelas INDID: 0 = HEAP 1 = Indice Clustered (físico) 2 até 249 = Indice NonClusterd (lógico) */ SELECT * FROM SYS.sysindexes WHERE OBJECT_ID('tbVisitas') GO SELECT * FROM SYS.sysindexes WHERE OBJECT_ID('tbVisitas_v2')

ID

=

ID

=

- 41 -

A quantidade de espaço em disco utilizado pela tabela V2 é 5 vezes maior que a tabela original apesar de serem os mesmos dados. Esta informação é lida ao executar o comando T-SQL: /*Compara o tamanho das tabelas*/ sp_spaceused tbVisitas GO sp_spaceused tbVisitas_v2

Lembrando que cada página de dados do SQL possui 8KB de tamanho, e o ColumnStore Index armazena uma coluna por página. Os dados de índices da tabela tbVisitas possui apenas 8KB que representam os dados já ordenados fisicamente através da Primary Key, já os dados da tabela tbVisitas_v2 possui 40KB de tamanho de índices, que representa os 8KB da página que os dados estão armazenados e mais 32KB, um para cada coluna criada no ColumnStore Index. Veja esta comprovação na Figura 11.

Figura 11 - Comparação de tamanho dos índices

Com o lançamento do SQL Server 2014 o cenário melhorou, agora é possível atualizar dados de uma tabela que possui o ColumnStore Index. Veja o que dizem sobre isso:

[...] Columnstore enhancements: Columnstore indexes are used to accelerate query performance for data warehousing that primarily performs bulk loads. In the previous version of SQL Server, tables that had columnstore indexes could not be updated. In the past, you had to drop the index, perform the update, and

- 42 -

then rebuild the index, or use partition switching or two tables— one with a columnstore index and another for updating—and then use UNION ALL queries to return data. As you can imagine, this could be challenging from an administrative perspective. With SQL Server 2014, in-memory columnstore has been modified to support updateable operations such as inserts, updates, and deletes. (MISTRY & MISNER, 2014 p. 09 e 10) […] Melhorias no ColumnStore: ColumnStore Index são usados para acelerar o desempenho de consultas em Data Warehouses que realizam, principalmente, carga em lote. Na versão anterior do SQL Server, tabelas que possuíssem o ColumnStore Index não podiam ser atualizadas. No passado você tinha que remover o índice, executar a modificação e em seguida reconstruir o índice. Uma alternativa é fazer o switch de dados de uma partição uma com ColumnStore Index e outra não para receber as atualizações em seguida usar um UNION ALL para recuperar os dados. Como você pode imaginar, isso era um desafio para os administradores. Com o SQL Server 2014 o ColumnStore In Memory permite operações de manutenção, como inserts, updates e deletes. (MISTRY & MISNER, 2014 p. 09 e 10)

- 43 -

3 - Metodologia e Estudo de Caso Nesta seção da pesquisa são apresentados os passos utilizados para realização dos experimentos apresentados no estudo de caso, buscando provar a eficiência da técnica proposta. Mais uma vez são apresentados códigos T-SQL e também a configuração do ambiente computacional para que a reprodução da pesquisa possa ser realizada por outros pesquisadores, caso queiram.

3.1 - O Experimento Com o objetivo de provar a eficiência do uso das técnicas apresentadas neste estudo sobre Partition Table, foi criado um experimento no qual um ambiente sem particionamento recebe um volume de dados e estes mesmos dados são replicados para um ambiente com a execução do particionamento de dados nas tabelas. Serão executados os mesmos procedimentos de manutenção dos dados em ambos cenários e comparados os resultados de performance.

O experimento consiste em executar três técnicas para atualizar os dados em uma tabela com ColumnStore Index que possui 26.000.000 de registros aleatórios, e o resultado será comparar qual arquitetura teve melhor tempo de resposta. No primeiro momento foram criados 24 milhões de registros sendo dois milhões para cada mês de 2014, em seguida foi criado mais um milhão para Janeiro de 2015. O ultimo 1 milhão de registros é onde o experimento fará a medição, e chegará ao total de 26 milhões de registros armazenados.

Estas são as três possibilidades, e seus respectivos nomes que serão usados nas legendas dos gráficos de comparação: 1) Desabilitar o ColumnStore Index, inserir os dados e reabilitar o índice – Rebuild; 2) Remover o índice, inserir os dados e recriar o índice – Drop 3) Trabalhar com Particionamento de Tabelas – Partition.

- 44 -

3.2 - Ambiente analisado

3.2.1 - O Servidor

Para este ambiente um servidor com processador Intel(r) Xeon(r) E5620 com 8 núcleos lógicos de processamento, 32GB de memória RAM, 4 unidades de discos físicos e Windows Server 2012, que podem ser verificados na Figura 12.

Figura 12 - Ambiente do Servidor

Estes discos possuem arquiteturas e rotações diferentes, sendo dois discos SATA - Serial Ata - de 500GB representando o disco 0 e disco 1. Um disco SSD – Solid State Disk – de 256GB representando o disco 2. E por fim um disco SATA de 2TB representando o disco 3. Confira esta arquitetura na Figura 13.

- 45 -

Figura 13 - Arquitetura de Discos do Servidor

O Sistema Operacional Microsoft Windows Server 2012 está instalado no drive C do disco 0 ocupando 100GB. Este disco ainda possui a área de Backup, que está assinada para o drive B do ambiente. O disco 1 possui os arquivos temporários e de Log do SQL Server, e para isso possui 500GB de espaço. O disco 2 armazena o arquivo de Dados do SQL Server e é onde precisa-se de mais performance. Por fim, o disco 3 possui algumas segmentações para receber as unidades de disco lógicas para cada uma das máquinas virtuais deste ambiente, sendo o drive G para a máquina virtual de SQL Server, o drive H para o ambiente de Business Intelligence, o J para a máquina virtual de Sharepoint, e o drive I para uma outra máquina com SQL Server. Estas unidades lógicas podem ser visualizadas na Figura 14, que representa o ambiente físico que possui a instalação do Sistema Operacional e tem o virtualizador para gerenciamento das máquinas virtuais. Este cenário hipotético foi criado para se assemelhar a diversos ambientes encontrados no mercado de trabalho, e com isso, provar o real benefício do experimento em um cenário simulado próximo ao real.

- 46 -

Figura 14 - Unidades de discos lógicas

Cada ambiente possui uma quantidade específica de memória RAM para suportar a carga de atividades diárias que recebe. O Sistema Operacional controla os 32GB de RAM, ficando com o mínimo de 2GB para gerenciamento das máquinas virtuais e processos internos. As máquinas virtuais de SQL Server e de Business Intelligence possuem cada uma 10GB de memória RAM para poder trabalhar com tranquilidade, a máquina de Sharepoint e a segunda de SQL Server iniciam com 1GB cada e vão crescendo de acordo com a necessidade. Esta configuração pode ser verificada na Figura 15.

Figura 15 - Memória RAM das máquinas virtuais

3.2.2 - IOPS dos discos

Uma das métricas mais importantes para se falar em discos é a medição de IOPS - Input/Output per Second - que representa o potencial que cada disco pode entregar de operações de entrada e saída de dados por segundo. Esta informação

- 47 -

pode ser recuperada com o fabricante do disco, ou através de ferramentas específicas para coletar esta métrica. Neste estudo foi utilizada a ferramenta gratuita IOMeter, que permite criar simulações de carga de dados seja de entrada, de saída ou de ambas operações para disparar contra o disco. O retorno desta ferramenta apresenta alguns resultados importantes para se analisar referente à performance, sendo os principais Total I/Os per Second - Total de Entrada e Saída por segundo - e Total MBs per Second - Total de Megabytes trafegados por segundo. Quando se dispara a mesma carga de testes em todos os discos que se tem interesse em conhecer a performance, é possível ver que a arquitetura do disco impacta diretamente neste quesito. A carga em questão foi um disparo de pacotes de 64Kbs com 50% de leitura e 50% de escrita. Veja na Figura 16 o comparativo de IOPS e de MBs do ambiente virtual do SQL Server, onde são comparados três unidades lógicas que são diretamente ligadas à este ambiente, sendo o drive C o sistema operacional, o drive L o local dos Logs e o drive M o local dos arquivos de Dados.

Figura 16 - Performance de discos diferentes

O cenário piora ainda mais quando o tamanho do disco físico aumenta, confirme a performance do ambiente de Sharepoint na Figura 17. Lembrando que o ambiente de Sharepoint está armazenado na unidade lógica do disco 3.

- 48 -

Figura 17 - Performance do disco de 2TB

3.2.3 - O SQL Server

Como Ben-Gan (2012) explica, o SQL é a sigla para Structured Query Language, e é a linguagem padrão para consultas e gerenciamento de SGBD – Sistemas Gerenciadores de Bancos de Dados – que são sistemas baseados em modelagem relacional no qual aplicam duas vertentes matemáticas, Teoria de Conjuntos e Lógica de Predicado. A Microsoft se baseou no SQL para escrever seu dialeto próprio, criando o T-SQL – Transact SQL – que é sua extensão para manipular e operar em sua distribuição de SGBD, o SQL Server. O SQL Server não consiste somente na visão de modelagem relacional, também tem suporte para trabalhar com Business Intelligence em modelagem tabular ou multidimensional, transformando o SQL Server não somente em um SGBD mas em uma Plataforma de Dados completa. Pode-se consumir o SQL Server através de três formatos, conhecidos como ABC. Sendo o A ofertado para Appliance, um ambiente consolidado de hardware e software já instalado e configurado para um workload específico e que o comprador pode começar a usar dentro de algumas horas. A oferta de B é para Box, ou “software de caixa”, que o comprador adquire a licença do produto e fica responsável por ter e manter o hardware para suportar o SGBD. A terceira forma de comercialização é o C que se aplica à Cloud Computing, no qual o comprador pode utilizar o SGBD como

- 49 -

serviço, pagando por apenas o que utilizou sem a necessidade de carência de contratação.

O presente estudo utilizará um SQL Server que foi instalado na instância padrão do servidor, com as configurações padrões de fábrica sugeridas pela Microsoft para facilitar a replicação/reprodução deste estudo por outros pesquisadores.

3.3 - Criando o ambiente O Código T-SQL para criar o ambiente do experimento é este abaixo: CREATE DATABASE dbMuseuExperimento GO USE dbMuseuExperimento GO CREATE TABLE tabelaProducao( id INT NOT NULL , idOrigem INT NOT NULL , nome VARCHAR(20) NOT NULL , endereco VARCHAR(30) NOT NULL , dataCadastro date NOT NULL , dataAtualizacao date NOT NULL ON [PRIMARY] GO

)

ALTER TABLE tabelaProducao WITH CHECK ADD CONSTRAINT [validarCodigo_V1_Check] CHECK (dataAtualizacao >= '2014-01-01' AND dataAtualizacao < '2015-02-01') GO ALTER TABLE tabelaProducao CHECK CONSTRAINT [validarCodigo_V1_Check] GO CREATE SEQUENCE seq_Codigo AS INT INCREMENT BY 1 minvalue 1 maxvalue 30000000 GO

Após a criação do ambiente, é necessário popular cada mês com 2 milhões de registros aleatórios. Veja como fica cada inserção no código T-SQL abaixo, repare que a mudança ocorre somente na penúltima e antepenúltima linha de cada código,

- 50 -

onde é informado um número que representa um sequencial de dias desde o dia 01/01/1900. Cada um destes números significa o primeiro dia do mês que está inserindo o registro aleatório na tabela. Inserir 2 milhões de registros em JANEIRO/2014: INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41638 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,41638 + RAND()*29))) go 2000000

Inserir 2 milhões de registros em FEVEREIRO/2014: INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41669 + RAND()*27)), CONVERT(DATETIME,CONVERT(INT,41669 + RAND()*27))) go 2000000

Inserir 2 milhões de registros em MARÇO/2014: INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41697 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,41697 + RAND()*29))) go 2000000

Inserir 2 milhões de registros em ABRIL/2014: INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41728 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,41728 + RAND()*29))) go 2000000

- 51 -

Inserir 2 milhões de registros em MAIO/2014: INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41758 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,41758 + RAND()*29))) go 2000000

Inserir 2 milhões de registros em JUNHO/2014: INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41789 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,41789 + RAND()*29))) go 2000000

Inserir 2 milhões de registros em JULHO/2014: INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41819 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,41819 + RAND()*29))) go 2000000

Inserir 2 milhões de registros em AGOSTO/2014 INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41850 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,41850 + RAND()*29))) go 2000000

- 52 -

Inserir 2 milhões de registros em SETEMBRO/2014 INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41881 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,41881 + RAND()*29))) go 2000000

Inserir 2 milhões de registros em OUTUBRO/2014 INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41911 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,41911 + RAND()*29))) go 2000000

Inserir 2 milhões de registros em NOVEMBRO/2014 INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41942 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,41942 + RAND()*29))) go 2000000

Inserir 2 milhões de registros em DEZEMBRO/2014 INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,41972 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,41972 + RAND()*29))) go 2000000

- 53 -

Inserir 1 milhão de registros em JANEIRO/2015 INSERT INTO tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,42003 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,42003 + RAND()*29))) go 1000000

Após toda a população da tabela, é necessário recuperar os metadados de IO (Input/Output) e tempo de execução das ações. Isso é feito com o código T-SQL a seguir: SET STATISTICS IO ON; SET STATISTICS TIME ON GO

3.4 - Prova da Hipótese 3.4.1 - Fase 1 - Reconstruindo o Índice

O cenário apresentado na sessão anterior do presente estudo foi replicado para suportar a fase 1 do experimento, que consiste em desabilitar o ColumnStore Index na tabela para permitir a manutenção de dados. Em seguida serão inseridos 1 milhão de registros na tabela e reabilitar o índice colunar. A métrica de Elapsed Time, CPU Time e Logical Reads foram coletadas e comparadas com as métricas obtidas nas fases 2 e 3, e o resultado é apresentado em uma sessão seguinte.

Para desabilitar o ColumnStore Index na tabela, é necessário que este índice exista. Para isso, o código T-SQL abaixo foi criado: CREATE NONCLUSTERED [idx_csi_tabelaProducao] ON tabelaProducao ( id, nome, dataAtualizacao ) GO

COLUMNSTORE endereco,

INDEX

dataCadastro,

O código T-SQL para desabilitar o índice e popular a tabela pode ser acompanhado abaixo. Como a métrica para desabilitar o índice é extremamente baixo

- 54 -

e não onera o desempenho do banco de dados, ele não foi contabilizado para os dados de comparativo do experimento: ALTER INDEX DISABLE GO

idx_csi_tabelaProducao

ON

tabelaProducao

insert into tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) values (next value for seq_Codigo, convert(int,rand()*100)+1, substring(convert(varchar(40),newid()),1,20), substring(convert(varchar(40),newid()),1,30), CONVERT(DATETIME,CONVERT(INT,42003 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,42003 + RAND()*29)+1)) go 1000000

O terceiro e último passo desta fase do experimento é reabilitar o índice colunar na tabela. Veja este código abaixo: ALTER INDEX REBUILD GO

idx_csi_tabelaProducao

ON

tabelaProducao

3.4.2 - Fase 2 - Excluindo o Índice A segunda fase do experimento consiste em excluir ao invés de desabilitar o índice colunar na tabela que está mantendo em formato somente leitura. Esta referida fase também é dividida em alguns passos, tais como, excluir o índice da tabela, inserir 1 milhão de registros recriado o índice colunar.

Da mesma forma que foi feito na fase 1, é necessário criar o índice colunar para forçar o estado de somente leitura na tabela. Veja o código T-SQL para isso: CREATE NONCLUSTERED [idx_csi_tabelaProducao] ON tabelaProducao ( id, nome, dataAtualizacao ) GO

COLUMNSTORE endereco,

INDEX

dataCadastro,

- 55 -

A exclusão do índice também não apresenta nenhum valor significativo para influenciar a métrica aferida, por isso foi ignorado. Veja o código para excluir o índice e inserir mais 1 milhão de registros na tabela: DROP INDEX [idx_csi_tabelaProducao] ON tabelaProducao GO insert into tabelaProducao(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) values (next value for seq_Codigo, convert(int,rand()*100)+1, substring(convert(varchar(40),newid()),1,20), substring(convert(varchar(40),newid()),1,30), CONVERT(DATETIME,CONVERT(INT,42003 + RAND()*30)), CONVERT(DATETIME,CONVERT(INT,42003 + RAND()*30)+1)) go 1000000 O último passo desta segunda fase é a recriação do índice, uma vez que ele foi excluído. Acompanhe o código T-SQL abaixo para ver a criação: CREATE NONCLUSTERED [idx_csi_tabelaProducao] ON tabelaProducao ( id, nome, dataAtualizacao ) GO

COLUMNSTORE endereco,

INDEX

dataCadastro,

3.4.3 - Fase 3 - Particionando os Dados da Tabela Na terceira fase do experimento foi criada a arquitetura que permite que a tabela seja particionada e dados sejam trabalhados internamente em blocos menores. Esta fase possui alguns passos a mais que as anteriores, mas nenhum destes passos extras apresenta alguma métrica significativa que deva ser incluída na contagem e comparação final.

Primeira coisa a se fazer é criar o Partition Function e o Partition Scheme, acompanhe o código T-SQL a seguir para criar estes objetos do banco de dados:

- 56 -

CREATE PARTITION FUNCTION [pf_DataAtualizacao](date) RANGE RIGHT FOR VALUES ('2014-01-01','2014-02-01','2014-03-01', '2014-04-01','2014-05-01','2014-06-01', '2014-07-01','2014-08-01','2014-09-01', '2014-10-01','2014-11-01','2014-12-01', '2015-01-01','2015-02-01') GO

AS

CREATE PARTITION SCHEME [ps_DataAtualizacao] AS PARTITION [pf_DataAtualizacao] ALL TO ([PRIMARY]) GO

O segundo passo é criar o índice colunar, e também um índice físico na tabela existente. Veja o código de ambas criações: CREATE CLUSTERED INDEX [idx_DataCodigo] tabelaProducao(DataAtualizacao) ON ps_DataAtualizacao(dataAtualizacao) GO CREATE NONCLUSTERED [idx_csi_tabelaProducao] ON tabelaProducao (id, nome, dataAtualizacao ) GO

COLUMNSTORE endereco,

ON

INDEX

dataCadastro,

Para aplicar o conceito do particionamento de tabelas e utilizar o método de switch apresentado nas sessões anteriores, uma tabela secundária é criada. Veja o código T-SQL para isso: CREATE TABLE tabelaProducao_V2 (id INT NOT NULL, idOrigem int NOT NULL, nome VARCHAR(20) NOT NULL, endereco VARCHAR(30) NOT NULL, dataCadastro date NOT NULL, dataAtualizacao date NOT NULL) GO ALTER TABLE tabelaProducao_V2 WITH CHECK ADD CONSTRAINT[validarCodigo_V2_Check] CHECK (dataAtualizacao >= '2015-01-01' and dataAtualizacao < '2015-02-01') GO O mesmo índice físico criado na tabela original será criado na tabela secundária, veja o código da criação deste índice: CREATE CLUSTERED INDEX [idx_DataCodigo] ON tabelaProducao_v2(DataAtualizacao) ON [PRIMARY] GO

- 57 -

O uso do método switch movimenta os dados de uma partição para outra. Este código T-SQL faz a movimentação de 1 milhão de registros existentes na partição 14 da tabela original para a tabela secundária: ALTER TABLE tabelaProducao tabelaProducao_v2 GO

SWITCH

PARTITION

14

TO

A inserção de outros 1 milhão de registros na tabela secundária é realizada através do código T-SQL a seguir, deixando esta tabela com 2 milhões de registros referentes ao mês de Janeiro/2015: insert into tabelaProducao_V2(id, idOrigem, nome, endereco, dataCadastro, dataAtualizacao) VALUES (NEXT value FOR seq_Codigo, CONVERT(INT,RAND()*100)+1, SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,20), SUBSTRING(CONVERT(VARCHAR(40),NEWID()),1,30), CONVERT(DATETIME,CONVERT(INT,42003 + RAND()*29)), CONVERT(DATETIME,CONVERT(INT,42003 + RAND()*29))) go 1000000

Como os dados foram inseridos na tabela secundária, é necessário criar um índice colunar nesta tabela para poder alinhar com a mesma estrutura da tabela original. Veja o código da criação deste índice abaixo: CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_csi_tabelaProducao] ON tabelaProducao_v2 ( id, nome, endereco, dataCadastro, dataAtualizacao ) GO

Da mesma forma que o método switch alterou a partição 14 da tabela original para a tabela secundária, este código T-SQL abaixo altera a partição inteira da tabela secundária de volta para a partição 14 da tabela original. ALTER TABLE tabelaProducao_V2 partition 14 GO

switch

to

tabelaProducao

- 58 -

3.5 - Analisando os resultados Após as três fases de testes, a tabela chamada tabelaProducao que originalmente possuía 25 milhões de registros e um índice colunar, no final de cada fase esta tabela passou a ter 26 milhões de registros e manteve o índice colunar, cada fase testando uma alternativa para inserir os novos registros nesta tabela.

As métricas de Elapsed Time, CPU Time e Logical Reads foram coletadas em três experimentos de cada fase, totalizando 9 execuções ao todo, e os dados de média das comparações são apresentados nos gráficos desta sessão.

As métricas foram coletadas através dos dados de estatísticas obtidos com o código T-SQL apresentado abaixo: SET STATISTICS IO ON; SET STATISTICS TIME ON GO

Em casos que os dados de leituras de páginas não puderam ser coletadas através das estatísticas de IO, o código T-SQL abaixo foi utilizado: DBCC SHOWCONTIG

Após a coleta e cálculo das médias de cada operação em cada fase, pode-se gerar os gráficos a seguir.

O gráfico impresso na Figura 18 apresenta os resultados da quantidade de páginas lidas em cada operação de manutenção realizada, aplicada a cada uma das três fases:

- 59 -

Total de Páginas Lidas 100%

50%

0%

32274

275420

10201

255258

10201

255369

10201

Criar ColumnStore Index na Tabela antes da Inserção 275420

Inserir 1 milhão de registros

Drop

255258

Rebuild

255369

Partition

317895

265689 531148 265593

531163

10201

Criar ColumnStore Index na Tabela após a Inserção 32274

Total

317895

10201

265689

531148

10201

265593

531163

Figura 18 - Comparativo de Páginas Lidas

O gráfico da Figura 19 apresenta o resultado do tempo de execução de cada fase:

Tempo de Execução (em milisegundos) 100%

115461

663905

19030

798396

119381 50%

0%

130905

740658

129563

640865

Criar ColumnStore Index na Tabela antes da Inserção 115461

Inserir 1 milhão de registros

Drop Rebuild

Partition

990944 108450

878878

663905

Criar ColumnStore Index na Tabela após a Inserção 19030

Total

798396

130905

740658

119381

990944

129563

640865

108450

878878

Figura 19 - Comparativo de Tempo de Execução

O gráfico apresentado na figura 20 apresenta o tempo total de processamento em cada uma das três fases.

- 60 -

Tempo de CPU (em milisegundos) 100%

50%

0%

204125

312208

210546

450829

209565

405676

534114

204735

Criar ColumnStore Index na Tabela antes da Inserção 204125

Inserir 1 milhão de registros

Drop

210546

Rebuild

209565

Partition

17781

866110 188923

804164

312208

Criar ColumnStore Index na Tabela após a Inserção 17781

Total

534114

450829

204735

866110

405676

188923

804164

Figura 20 - Comparativo de Tempo de Processamento

3.6 Resultados No cenário criado e experimentado neste estudo, os números apresentam que a quantidade de páginas lidas sem o uso de Partition Table é de 67% a mais comparado com a tabela que possui o particionamento. Quando a comparação é em tempo de execução, excluir e recriar o índice é aproximadamente 24% pior e aplicar a técnica que reconstrói o índice é 10% mais lento do que usar o Partition Table. Por fim, focando no tempo de uso de CPU, excluir e recriar o índice computou 62% a mais de tempo e fazer a reconstrução através do rebuild ocupou 51% a mais tempo do que o partition table.

- 61 -

4 – Considerações Finais 4.1 - Conclusão A partir dos resultados obtidos nas três fases do referido experimento, é possível concluir que o particionamento de tabelas apresentou a melhor performance no ambiente comparando quantidade de leituras em páginas, tempo de execução e também em tempo total de processamento.

A prova foi realizada em um ambiente com índice colunar em uma tabela com 25 milhões de registros e em seguida mais 1 milhão foi inserido, mas também se aplica em uma arquitetura que não implemente índice colunar.

4.2 – Trabalhos Futuros Este estudo focou o experimento exclusivamente à uma arquitetura composta de um único servidor SQL Server e em uma modelagem no qual existe a implementação de índice colunar. Trabalhos de outros pesquisadores podem ocorrer em linhas semelhantes, utilizando de uma arquitetura similar em outro SGDB como Oracle, MySQL ou DB2 buscando verificar o desempenho destes SGBD comparados com o experimentado neste estudo. Outra linha de pesquisa que pode ser seguida, é utilizar arquitetura distribuída em clusters para comparar também o desempenho das operações de manutenção de dados como experimentado nesta obra.

- 62 -

5 - Bibliografia Agrawal, S., Narasayya, V., & Yang, B. (2012). Integrating Vertical and Horizontal Partitioning into Automated Physical Database Design. Microsoft Research, 359-370. Amorim, F. (2011). Complete Showplan Operators. Pasadena: Red-Gate. Beaulieu, A. (2009). Learning SQL. Sebastopol: O'Reilly. Ben-Gan, I. (2013). Microsoft SQL Server 2012 T-SQL Fundamentals. Sebastopol: Microsoft Press. Ben-Gan, I., Sarka, D., Wolter, R., Low, G., Katibah, E., Kunen, I., & Delaney, K. (2010). Inside Microsoft SQL Server 2008: T-SQL Programing. Redmond: Microsoft Press. Carvalho, N. P. (2003). Proposta de Esquema Dimensional Hierárquico Genérico para Implementação em SGBD Relacional. Recife: Universidade Federal de Pernambuco. Costa, A. M. (2013). Ferramenta de Gestão de Dados. Braga: Escola de Engenharia - Universidade do Minho. Date, C. (2009). SQL and Relational Theory: How to write accurate SQL code. Sebastopol: O'Reilly. Dave, P., & Morelan, R. A. (2011). SQL Performance Tuning Techniques Using Wait Statistics, Types & Queues. Lexington: Joes 2 Pros. Elmasri, R., & Navathe, S. B. (2000). Fundamentals of Database Systems - Third Edition. Vancouver: Pearson. Filho, J. d. (2008). Uma Abordagem Não-Intrusiva para a Manutenção Automática do Projeto Físico de Bancos de Dados. Rio de Janeiro: Pontifícia Universidade Católica do Rio de Janeiro. Gray, D. E. (2009). Pesquisa no Mundo Real. Londres: Penso. Harinath, S., Pihlgren, R., Lee, D. G.-Y., Sirmon, J., & Bruckner, R. M. (2012). Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX. Indianapolis: Wiley. Herodotou, H., Borisov, N., & Babu, S. (2013). Query Optimization Techniques for Partitioned Tables. North Carolina: Duke University.

- 63 -

Hotek, M. (2008). Microsoft SQL Server 2008 - Implementação e Manutenção. Redmond: Bookman. Hotek, M. (2008). Microsoft SQL Server 2008 - Passo a Passo. Redmond: Bookman. Kimball, R., & Caserta, J. (2004). The Data Warehouse ETL Toolkit. Indianapolis: Wiley. Knight, B., Knight, D., Jorgensen, A., LeBlanc, P., & Davis, M. (2010). Kinight's Microsoft Business Intelligence 24-Hour Trainer. Indianapolis: Wiley. Kumar, V. P., & Krishnaiah, R. V. (2012). Horizontal Aggregations in SQL to Prepare Data Sets for Data Mining Analysis. IOSR Journal of Computer Engineering (IOSRJCE), 36-41. Larson, P.-A. (2000). Redmond, Washington Patente Nº 6,115,705. Matuck, P. d., Silva, M. V., & Pardini, D. J. (2012). Sistema Gerenciador de Banco de Dados (SGBD) e particionamento horizontal em tabelas análise de desempenho em uma empresa siderúrgica mineira. 9º CONTECSI International Conference on Information Systems and Technology Management (pp. 1578 - 1601). São Carlos: USP. Mistry, R., & Misner, S. (2012). Introducing Microsoft SQL Server 2012. Redmond: Microsoft Press. Monteiro, J. M., Lifschitz, S., & Brayner, Â. (2008). Estado da Arte em Auto-Sintonia do Projeto Físico de Banco de Dados. Rio de Janeiro: PUC Rio. Rabelo, B. M. (2008). Uma arquitetura para bancos de dados distribuídos apoiada em serviços. Belo Horizonte: Pontifícia Universidade Católica de Minas Gerais. Rao, J., Zhang, C., Lohman, G., & Megiddo, N. (2002). Automating Physical Database Design in a Parallel Database. IBM Almaden Research Center. Rocha, A. B. (2000). Guardando Históricos de Dimensões em Data Warehouses. Campina Grande: UNIVERSIDADE FEDERAL DA PARAÍBA. Russo, M., Ferrari, A., & Webb, C. (2012). Microsoft SQL Server 2012 Analysis Services - The BISM Tabular Mode. Sebastopol: Microsoft Press. Stanek, W. R. (2009). Microsoft SQL Server 2008 - Guia de Bolso do Administrador. Redmond: Bookman. Ullman, J. D. (1988). Principles of Database and Knowledge - Base Systems - Volume I. Rockville: Computer Science Press. Ullman, J. D. (1989). Principles of Database and Knowledge - Base Systems - Volume II: The New Technologies. Rockville: Computer Science Press.

- 64 -

Volodarsky, M. D., & Ng, P. Y. (2007). US/Washington Patente Nº US 2007/0073829 A1. Wazlawick, R. S. (2009). Metodologia de Pesquisa para Ciência da Computação. Rio de Janeiro: Elsevier. Weinert, A. T., Morin, B. R., & Caiafa, D. M. (2010). Redmond, Washington Patente Nº US 7,827,302 B2. William, S. R. (2008). Windows Server 2008 - Guia Completo. Redmond: Bookman.