SQL Server 2008

Desempenho de consultas de data warehouse

Sunil Agarwal and Torsten Grabs and Dr. Joachim Hammer

 

Visão geral:

• Otimização de consultas de junção em estrela
• Paralelismo de tabela particionada
• Compactação ROW e PAGE
• Exibições indexadas alinhadas com a partição

O SQL Server 2008 oferecerá recursos relacionais de data warehouse muito mais eficientes do que os de seu antecessor, mas você talvez ainda esteja se perguntando como usar toda essa nova tecnologia na criação de um data warehouse com bom desempenho destinado a dar apoio a decisões relacionadas a bilhões de linhas. Ou talvez queira saber quais recursos lhe ajudarão a obter o melhor desempenho das consultas relacionadas a consultas e relatórios de apoio à decisão ou que tipo de melhoria no desempenho você pode esperar de fato dessa nova versão do SQL Server®.

Certamente haverá muitas perguntas na medida em que nos aproximarmos do lançamento real. Esperamos que essa análise aprofundada de alguns dos recursos mais importantes de data warehouse relacionados ao desempenho do SQL Server 2008 possa lhe ajudar em sua preparação.

Design do banco de dados lógico: modelagem dimensional

Os aplicativos de linha de negócios transacionais costumam apresentar um esquema de banco de dados normalizado. O design do esquema de banco de dados lógico dos data warehouses relacionais enfatiza menos a normalização. Muitos designs de data warehouse relacionais atuais seguem uma abordagem de modelagem dimensional, popularizada por Ralph Kimball e Margy Ross no livro The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling.

Caso perca muito tempo lidando com data warehouse, é provável que você já esteja familiarizado com padrões de esquema comuns destinados a data warehouses relacionais (como, por exemplo, os esquemas estrela e floco de neve). A modelagem dimensional difere as tabelas de dimensões das tabelas de fatos. As tabelas de dimensões são as responsáveis pelo armazenamento dos dados mestre (como, por exemplo, produtos, clientes, lojas ou países) e as tabelas de fatos, pelo armazenamento dos dados transacionais (como, por exemplo, ordens, pedidos ou devoluções).

As tabelas de dimensões e de fatos são vinculadas pelas relações de PK (chave primária)/FK (chave estrangeira). Você verá que muitos data warehouses não impõem as restrições de FK como uma forma de manter os requisitos de armazenamento mínimos. Isso evita a sobrecarga de armazenamento dos índices subjacentes e mantém baixo o custo de manutenção da tabela de fatos. As tabelas de dimensões de um data warehouse costumam ser bem pequenas – elas normalmente mantêm milhares ou até muitos milhões de linhas. Por outro lado, a tabela de fatos pode ser muito grande, mantendo centenas de milhões até bilhões de linhas. Por isso, o design lógico realmente precisa prestar mais atenção nos requisitos de armazenamento da tabela de fatos.

O fator tamanho tem implicações quanto à determinação da chave a ser escolhida em uma tabela de dimensões para manter as relações entre as tabelas de fatos e dimensões. As chaves compostas com base na chave de negócios da dimensão – que diz respeito ao identificador do mundo real da entidade representada pela dimensão – normalmente abrangem várias colunas. Você deve saber que se trata de um problema para a chave estrangeira correspondente na tabela de fatos porque a chave composta com várias colunas será repetida em todas as linhas da tabela de fatos.

Como resposta a isso, uma prática comum é usar pequenas chaves alternativas para implementar as relações entre uma tabela de fatos e suas dimensões. A chave alternativa é uma coluna de identidade de tipo inteiro que funciona como uma chave primária artificial da tabela de dimensões. Com a tabela de fatos se referindo à chave alternativa menor, há uma redução significativa nos requisitos de armazenamento das tabelas de fatos maiores. A Figura 1 ilustra um esquema de data warehouse de modelagem dimensional usando tabelas de dimensões e fatos com chaves alternativas.

Figure 1** Exemplo de esquema estrela com uma tabela de fatos e duas tabelas de dimensões **(Clique na imagem para aumentar a exibição)

O design do esquema floco de neve difunde uma ou muitas dimensões em vários níveis (por exemplo, cliente, país e região para uma dimensão cliente), o que normaliza dimensões maiores que talvez sofram com redundâncias excessivas nos dados. Os níveis são representados por tabelas separadas, dando ao esquema a forma de um floco de neve. Por outro lado, o design do esquema estrela não difunde nenhuma das dimensões nas tabelas. Um esquema estrela tem a forma de uma estrela na qual as tabelas de dimensões são agrupadas próximas à tabela de fatos no centro.

Com os esquemas estrela ou floco de neve de modelagem dimensional, as consultas de apoio à decisão seguem um padrão típico: a consulta seleciona várias medidas de interesse na tabela de fatos, adiciona as linhas de fatos a uma ou a várias dimensões com as chaves alternativas, coloca predicados de filtro nas colunas de negócios das tabelas de dimensões, agrupa uma ou várias colunas de negócios e agrega as medidas recuperadas da tabela de fatos durante um determinado período. A seguir, o padrão, às vezes conhecido como consulta de junção em estrela:

select ProductAlternateKey,
CalendarYear,sum(SalesAmount)
from FactInternetSales Fact
     join DimTime 
on Fact.OrderDateKey = TimeKey
     join DimProduct 
on DimProduct.ProductKey =
   Fact.ProductKey
where CalendarYear between 2003 and 2004
      and ProductAlternateKey like 'BK%'
group by ProductAlternateKey,CalendarYear

Design físico

Muitas consultas SQL no data warehouse relacional seguirão a estrutura da consulta de junção em estrela. No entanto, as consultas de apoio à decisão normalmente variam conforme o tempo porque os tomadores de decisão estão sempre tentando compreender melhor os dados corporativos fundamentais de novas formas. É por isso que as cargas de trabalho dos data warehouses tendem a apresentar uma alta taxa de consultas ad hoc. Isso torna o design físico para consultas de apoio à decisão e um esquema de data warehouse com dados modelados dimensionalmente algo desafiador.

Com o SQL Server, os designers de data warehouse normalmente começam com um plano gráfico ou design físico que ajustam e refinam com o passar do tempo à medida que as cargas de trabalho evoluem. Você deve se sentir livre para adotar e variar esse plano gráfico de acordo com seu próprio ambiente de data warehouse. Caso você faça isso, não se esqueça, obviamente, das práticas recomendadas para o design físico de banco de dados como, por exemplo, o impacto sobre o desempenho causado pela manutenção do índice em relação a atualizações e os requisitos de armazenamento dos índices.

A tabela de fatos

O design de plano gráfico pretende prever a forma da consulta em estrela típica e cria índices na tabela de fatos. O índice clusterizado da tabela de fatos usa várias colunas de chaves alternativas da dimensão (as colunas de chaves estrangeiras) como chaves de índice. As colunas mais usadas devem estar na lista das chaves de índice. Você talvez queira passar o tempo verificando se isso realmente proporciona um bom caminho de acesso às consultas mais executadas na carga de trabalho.

Além disso, o plano gráfico cria um índice não clusterizado com uma única coluna para cada coluna alternativa (chave estrangeira) da dimensão na tabela de fatos. Ele fornece um caminho de acesso altamente eficiente para consultas que sejam muito seletivas em uma das dimensões.

O objetivo do índice clusterizado é proporcionar um bom desempenho para a maioria das consultas na carga de trabalho. O conjunto de índices não-clusterizados se destina a consultas que recuperam as medidas da tabela de fatos para um cliente ou produto específico. Esses índices não clusterizados garantem que você, por exemplo, não precise examinar a tabela de fatos para poder recuperar dados de vendas referentes a um único cliente.

Tabelas de dimensões

Ao aplicar o design de plano gráfico às tabelas de dimensões, você precisa criar índices para cada uma das tabelas de dimensões. Entre eles estão um índice restrito de palavras primárias não-clusterizado na coluna da chave alternativa da dimensão e um índice clusterizado nas colunas da chave de negócios da entidade da dimensão. Para tabelas de dimensões maiores, você também deve considerar a inclusão de índices não-clusterizados nas colunas mais usadas em predicados altamente seletivos.

O índice clusterizado facilita um ETL (extração, transformação e carregamento) mais eficiente durante a janela de manutenção do data warehouse, que costuma ser um processo em que o tempo é crítico. Com dimensões que mudam mais lentamente, por exemplo, as linhas existentes são atualizadas in-loco, ao passo que as linhas ainda não presentes na dimensão são acrescentadas à tabela de dimensões. Para que haja êxito, esse padrão de acesso exige uma pesquisa com bom desempenho e a atualização da tabela de dimensões no momento do ETL.

O design de projeto gráfico que descrevemos serve como um bom ponto de partida para designs físicos em data warehouses relacionais criados com o SQL Server. Com base nessa instalação de data warehouse relacional típica, podemos explorar novos recursos importantes do SQL Server 2008.

Otimização de consultas de junção em estrela

O processamento da tabela de fatos costuma significar o maior custo da execução de uma consulta de junção em estrela em um data warehouse relacional de modelagem dimensional. É fácil de ver isso porque mesmo as consultas altamente seletivas recuperam uma ordem de magnitude de mais linhas da tabela de fatos do que de qualquer dimensão. Por isso, usar o melhor caminho de acesso até a tabela de fatos é essencial para um bom desempenho da consulta.

Com o SQL Server, o otimizador de consulta escolhe o caminho de acesso com o menor custo estimado de um conjunto de alternativas. No contexto de data warehouse, o principal objetivo é verificar se o otimizador de consulta considera as alternativas atraentes dos caminhos de acesso para o plano de execução da consulta de junção em estrela. O SQL Server inclui várias funcionalidades no otimizador de consulta para fornecer automaticamente planos de execução da consulta de junção em estrela de bom desempenho.

É possível pensar nas consultas de junção em estrela como estando divididas em três classes diferentes, como mostrado na Figura 2. Essas classes amplas também ajudam o mecanismo do SQL Server a identificar as opções de plano apropriadas a essas consultas. O principal conceito no qual o SQL Server se baseia é a seletividade dessas consultas na tabela de fatos. Uma consulta é considerada mais seletiva com menos linhas usadas da tabela de fatos. A porcentagem das linhas recuperadas da tabela de fatos é usada para fornecer a intuição dessas classes de consulta. Essas porcentagens representam valores das implantações de cliente típicas, embora não sejam limites rígidos usados na geração das definições do caminho de acesso.

Figure 2** Intervalos de seletividade para consultas de junção em estrela **(Clique na imagem para aumentar a exibição)

A primeira classe aborda as consultas altamente seletivas, que processam até 10% das linhas na tabela de fatos. A segunda, de seletividade média, consiste em consultas que processam mais de 10% e até 75% das linhas da tabela de fatos. Já as consultas na terceira classe, de baixa seletividade, exigem um processamento de mais de 75% das linhas armazenadas na tabela de fatos. As caixas da figura também realçam as opções do plano de execução da consulta básica em cada classe de seletividade.

Opção de plano com base na seletividade

Como as consultas em estrela de alta seletividade normalmente não recuperam mais de 10% das linhas da tabela de fatos, essas consultas podem conseguir o acesso aleatório à tabela de fatos. Por isso, os planos de consulta dessa classe dependem muito das junções em loop aninhadas com buscas em índices (não-clusterizados) e pesquisas em marcadores da tabela de fatos. Como realizam E/S aleatória na tabela de fatos, elas são executadas por E/S seqüencial porque precisamos recuperar partes maiores da tabela de fatos. Isso gera planos de consulta diferentes porque o número de linhas da tabela de fatos excede um determinado número.

Como as consultas em estrela de seletividade média processam uma parte significativa das linhas na tabela de fatos, junções de hash com verificações da tabela de fatos ou verificações do intervalo da tabela de fatos costumam ser a opção preferencial para o caminho de acesso à tabela. O SQL Server usa filtros de bitmap para melhorar o desempenho dessas junções de hash.

A Figura 3 ilustra como o SQL Server usa esses filtros de bitmap para melhorar o desempenho da junção durante a execução da consulta de junção em estrela. A figura mostra um plano de consulta em duas tabelas de dimensões, Product e Time, que se juntam à tabela de fatos com as chaves alternativas. A consulta usa predicados de filtro como, por exemplo, cláusulas WHERE nas tabelas de dimensões de forma que apenas uma linha se qualifique para cada dimensão. Isso é indicado pelas tabelas vermelhas pequenas próximas aos dois operadores da junção.

Figure 3** Plano de consulta de junção em estrela com processamento de redução das junções **(Clique na imagem para aumentar a exibição)

A implementação de cada junção é uma junção de hash, que permite ao SQL Server usar as informações sobre linhas qualificadas das tabelas de dimensões naquilo que chamamos informações de redução das junções em ambas as tabelas de dimensões. As caixas verdes na figura representam as estruturas de dados das informações de redução das junções. Uma vez preenchido usando as tabelas de dimensões subjacentes, o SQL Server move essas estruturas de dados automaticamente durante a execução da consulta para o operador que processa a tabela de fatos como, por exemplo, uma verificação de tabela. Esse operador usa as informações sobre as linhas da tabela de dimensões para eliminar as linhas da tabela de fatos que não se qualificarão para as condições de junção em relação às dimensões.

O SQL Server remove essas linhas da tabela de fatos já no início do processamento das consultas – depois de recuperar a linha da tabela de fatos. Isso possibilita as economias da CPU e de E/S de disco potencial porque as linhas removidas não precisam ser processadas em mais operadores do plano de consulta. O SQL Server usa uma representação em bitmap para implementar de maneira eficiente as estruturas de dados das informações de redução durante a execução da consulta.

Pipeline de otimização na junção em estrela

O processo de otimização usa heurística padrão na otimização da consulta de junção para gerar um conjunto inicial de alternativas ao plano de execução da consulta. Em seguida, as extensões com objetivos especiais são invocadas para gerar alternativas adicionais ao plano de consulta.

No caso de data warehouse, a extensão detecta esquemas estrela, flocos de neve, além dos padrões de consulta em estrela, e estima a seletividade da consulta com base na tabela de fatos. Caso o esquema e a forma de consulta correspondam aos padrões, o SQL Server adiciona automaticamente mais planos de consulta ao espaço do plano, abordado em seguida pela otimização com base no custo para escolher o plano mais promissor para a execução.

No momento da execução da consulta, o SQL Server também monitora a seletividade real da redução de junções durante o runtime. Caso haja alterações na seletividade, o SQL Server reorganiza as estruturas de dados das informações de redução das junções dinamicamente para que seja aplicada a mais seletiva primeiro.

Heurística da junção em estrela

Muitos designs físicos para data warehouses seguem o esquema estrela, mas não especificam completamente as relações entre as tabelas de fatos e de dimensões, como mencionado anteriormente a respeito das restrições de chave estrangeira, por exemplo. Sem as restrições de chave estrangeira especificadas explicitamente, o SQL Server deve depender da heurística para detectar padrões de consulta do esquema em estrela. A seguinte heurística é aplicada para detectar padrões de consulta da junção em estrela:

1. A maior das tabelas participantes da junção n-ary é considerada a tabela de fatos. Há restrições adicionais quanto ao tamanho mínimo da tabela de fatos. Por exemplo, mesmo que a tabela maior não exceda um tamanho específico, a junção n-ary não é considerada uma junção em estrela.
2. Todas as condições das junções binárias em uma consulta de junção em estrela devem ter predicados de igualdade de coluna únicos. As junções devem ser internas. Embora possa parecer algo restritivo, isso abrange a grande maioria das junções entre as tabelas de fatos e de dimensões na chave alternativa em esquemas estrela típicos. Caso uma junção tenha uma condição mais complexa que não corresponda ao padrão descrito acima, ela é excluída da junção em estrela. Uma junção com cinco direções, por exemplo, pode levar a uma junção em estrela com três direções (com duas junções adicionais posteriormente), caso duas delas tenham predicados de junção mais complexos.

Observe que essas regras são heurísticas. Há poucas circunstâncias reais que levarão a heurística a escolher uma tabela de dimensões como sendo a tabela de fatos. Isso influencia a opção, embora não tenha nenhum impacto sobre a correção do plano selecionado. Em seguida, as junções binárias envolvidas em uma junção em estrela são ordenadas por seletividade decrescente. Nesse contexto, a seletividade da junção é definida como uma taxa de cardinalidade de entrada da tabela de fatos e a cardinalidade de resultado da junção – a seletividade da junção indica em que proporção uma dimensão específica reduz a cardinalidade de uma tabela de fatos. Como regra geral, queremos considerar inicialmente as junções de maior seletividade.

O processador de consultas do SQL Server aplica automaticamente a otimização a consultas que sigam o padrão de junção em estrela e as condições já mencionadas quando os planos de consulta resultantes apresentarem custos de consulta estimados atrativos. Por isso, você não precisa fazer nenhuma alteração no aplicativo para aproveitar esse aprimoramento significativo feito no desempenho. No entanto, observe que algumas das otimizações de junção em estrela como, por exemplo, a redução das junções só estão disponíveis no SQL Server Enterprise Edition.

Resultados do desempenho da junção em estrela

Como parte do esforço de desenvolvimento para a otimização da junção em estrela no SQL Server 2008, realizamos vários estudos de desempenho com base em cargas de trabalho de parâmetro de comparação e de clientes reais. Vale a pena observar os resultados de três dessas cargas de trabalho.

Data warehouse da organização de vendas da Microsoft Essa carga de trabalho acompanha o desempenho de um data warehouse usado internamente no apoio à decisão dentro da organização de vendas da Microsoft. Tiramos um instantâneo de amostra do banco de dados cujo tamanho é de aproximadamente 750 GB (inclusive índices). As consultas nessa carga de trabalho são desafiadoras quanto ao processamento porque muitas delas têm mais de dez junções.

Cliente comercial Essa série de experimentos se baseia em um cliente de data warehouse na empresa (com loja convencional e presença online). O cliente é caracterizado por um esquema floco de neve de modelagem dimensional e consultas de junção em estrela canônicas. Usamos cerca de 100 GB de dados brutos para preencher um instantâneo do depósito para os nossos experimentos.

Carga de trabalho de apoio à decisão Essa série de experimentos investiga o desempenho de uma carga de trabalho de apoio à decisão em um banco de dados de modelagem dimensional com 100 GB. A Figura 4 mostra os resultados dessas três cargas de trabalho. A figura indica meios geométricos normalizados do tempo de resposta da consulta em relação a todas as consultas na carga de trabalho. Essa métrica é um bom indicador de qual deve ser o desempenho da consulta durante a execução de uma consulta arbitrária na carga de trabalho. As barras da figura comparam o desempenho da linha de base (1.0) quando não se usa a otimização da junção em estrela de acordo com o desempenho otimizado da junção em estrela. Todas essas execuções foram realizadas com o SQL Server 2008.

Figure 4** Aprimoramentos no desempenho com otimização da junção em estrela **(Clique na imagem para aumentar a exibição)

Como mostra a figura, todas as cargas de trabalho melhoram significativamente, de 12% a 30%. Embora a milhagem individual varie, esperamos que as cargas de trabalho de apoio à decisão no SQL Server Engine melhorem de 15% a 20% com base na extensão das otimizações específicas da junção em estrela novas no SQL Server 2008.

Paralelismo de tabela particionada

Para agilizar o processamento em data warehouses grandes, os administradores de banco de dados normalmente particionam grandes tabelas de fatos por data. Isso coloca os dados em grupos de arquivos diferentes, reduzindo a quantidade de dados a serem pesquisados durante o processamento das linhas dentro um intervalo de dados e também usando o desempenho simultâneo do sistema de discos subjacente quando os grupos são implantados em um grande número de discos físicos.

O SQL Server 2005 introduziu a possibilidade de particionar uma grande relação em partes lógicas menores para melhorar a administração e o gerenciamento de tabelas maiores. Ele também tem sido usado com êxito para melhorar o processamento de consultas, especialmente quando há aplicativos de apoio à decisão maiores.

Infelizmente, alguns clientes que usam o SQL Server 2005 observaram problemas de desempenho associados a consultas nessas tabelas particionadas – em especial, durante a execução em máquinas com vários processadores de memória compartilhada em paralelo. Em meio ao processamento de consultas em paralelo nas tabelas particionadas no SQL Server 2005, talvez haja instâncias quando apenas um subconjunto dos threads disponíveis estiver atribuído para a execução da consulta.

Pense em uma máquina de 64 núcleos na qual as consultas poderiam usar até 64 threads em paralelo e em uma delas atingindo duas partições. Com o SQL Server 2005, ela recebe dois dos 64 threads e, por isso, deve usar apenas 2/64 (3,1%) da energia da CPU da máquina. Observou-se que, em algumas consultas, o desempenho no caso particionado poderia ser dez ou mais vezes pior que com a mesma consulta executada na mesma máquina ou uma versão não-particionada da mesma tabela de fatos.

Devemos observar que o SQL Server 2005 foi otimizado especificamente para consultas que atingem uma única partição. Nesse caso, o processador de consultas atribuirá todos os threads disponíveis para a realização da verificação. Essa otimização especial resultou em uma melhora significativa do desempenho em consultas de partição única executadas em máquinas com vários núcleos, e era natural que os clientes esperassem esse comportamento de consultas que atingissem várias partições.

O novo recurso PTP (paralelismo de tabela particionada) do SQL Server 2008 melhora o desempenho da consulta no caso particionado usando melhor a capacidade de processamento do hardware existente independentemente de quantas partições são atingidas por uma consulta ou do tamanho relativo das partições individuais. Em um cenário de data warehouse típico com uma tabela de fatos particionada, os usuários podem notar uma melhoria significativa relacionada a consultas em execução nos planos paralelos, especialmente se o número de núcleos de processador disponíveis for maior que o número de partições afetadas pela consulta. E esse novo recurso funciona prontamente sem qualquer tipo de ajuste ou de configuração.

Digamos que haja uma tabela de fatos representando os dados de venda organizados por data em quatro partições. O diagrama na Figura 5 lhe ajudará a visualizar esse exemplo. Observe que, em vez de um único índice clusterizado para todo o intervalo de datas como acontece no caso não-particionado, normalmente há um índice clusterizado na coluna de data para cada partição da tabela de fatos. Agora suponhamos que a consulta Q resuma as vendas referentes aos últimos sete dias. Na medida em que novos dados de vendas continuarem entrando na tabela de fatos por meio da última partição (identificada como P4), será provável que a consulta continue atingindo partições diferentes de acordo com o momento em que for executada. Isso está ilustrado na primeira linha do diagrama com a forma com que a consulta Q1 atinge uma única partição, ao passo que a consulta Q2 atinge duas partições, porque os dados relevantes no momento da execução se estendem pelas partições P3 e P4.

Figure 5** O novo recurso PTP em funcionamento **(Clique na imagem para aumentar a exibição)

Agora suponhamos que haja oito threads disponíveis. A execução de Q1 e Q2 no SQL Server 2005 pode gerar algum tipo de comportamento inesperado. O SQL Server 2005 conta com uma otimização por meio da qual se o otimizador souber, durante o momento da compilação, que apenas uma partição será atingida pela consulta, essa partição será tratada como uma tabela não-particionada única, e será gerado um plano que acessa a tabela com todos os threads disponíveis.

O resultado é que o Q1 envolvendo uma única partição (P3) acarretará um plano processado por oito threads (não mostrados). No caso do Q2, que atinge duas participações, o executor atribui um único thread a cada partição, mesmo que o hardware subjacente tenha threads adicionais disponíveis. Por isso, o Q2 só usará uma fração muito pequena da energia da CPU disponível, sendo provável que a execução seja significativamente mais lenta do que do Q1.

A execução do Q1 e do Q2 no SQL Server 2008 resulta em um melhor uso do hardware disponível, além de um melhor desempenho e de um comportamento mais previsível. No caso do Q1, o executor atribui novamente todos os oito threads disponíveis ao processamento dos dados em P2 (não mostrados). Enquanto isso, o Q2 resultará em um plano paralelo no qual o executor atribui todos os threads disponíveis a P3 e a P4 no estilo round-robin, produzindo o efeito ilustrado na linha inferior do diagrama em que cada uma das duas partições recebe quatro threads. A CPU permanece totalmente usada, e o desempenho de Q1 e Q2 são comparáveis.

Essa alocação round-robin dos threads permite que as consultas sejam realizadas de maneira cada vez melhor com os mais núcleos de processadores existentes em comparação com o número de partições acessadas pela consulta. Porém, infelizmente há casos em que a alocação dos threads para as partições não é tão simples quanto nesse exemplo.

Os ganhos de desempenho do SQL Server 2008 em relação ao SQL Server 2005 no cenário da tabela particionada em uma máquina de processadores com vários núcleos estão mais bem ilustrados na Figura 6. Esse gráfico curioso realça o desempenho da verificação em tabelas particionadas. Nesse teste específico, que foi realizado em um sistema com 64 núcleos e 256 GB de RAM, particionamos uma tabela única de 121 GB em 11 partições de 11 GB cada. Para o conjunto de testes descrito na figura, usamos uma organização de arquivos em heap com inicializações de buffer a frio e a quente. Todas as consultas realizam verificações simples nos dados.

Figure 6** Desempenho da verificação do SQL Server com o novo recurso PTP habilitado **(Clique na imagem para aumentar a exibição)

O eixo y mostra o tempo de resposta (segundos) e o eixo x indica o grau de paralelismo (DOP), análogo ao número de threads atribuídos à consulta. Como é possível ver, tanto no caso da inicialização a frio quanto da inicialização a quente, os tempos de resposta continuam diminuindo até DOP chegar a 22. Nesse ponto, o sistema de E/S se torna saturado para o caso da inicialização a frio. Isso acontece devido ao fato da consulta usada no exemplo ser vinculada a E/S. Em mais cargas de trabalho vinculadas à CPU, essa limitação talvez não exista ou ocorra em DOPs mais altos.

No entanto, a curva que representa o caso da inicialização a quente continua mostrando diminuições no tempo de resposta enquanto os níveis de DOP aumentam. No SQL Server 2005, ambas as curvas começariam a cair próximas do DOP 11 porque o número de threads por partição estaria limitado a 1 em caso de haver várias partições.

É importante ressaltar que, na prática, o ganho em tempos de resposta em relação a números de DOP cada vez maiores jamais é linear. Na verdade, o comportamento esperado é mais que uma função de etapa – ele reflete a realidade de que a consulta essencialmente aguarda a subparte mais lenta. Dessa forma, por exemplo, a simples inclusão de mais um thread a uma verificação não irá melhorar o tempo de conclusão de uma consulta até que todas as verificações restantes tenham recebido threads adicionais que as proporcionem também uma conclusão mais rápida.

Realizamos experimentos adicionais para testar o novo comportamento do PTP em vários outros hardwares e configurações de arquivo. Com isso, observamos um comportamento semelhante em termos de colocação de escala da produtividade à medida que o DOP excede um thread/partição.

Por fim, mas não menos importante, o novo recurso PTP do SQL Server 2008 também aumenta a legibilidade dos planos de consulta e possibilita um maior aprofundamento na execução de determinadas cargas de trabalho. Por exemplo, como parte do recurso PTP, a forma na qual os planos em paralelo e em série são representados no XML do plano de execução foi melhorada e as informações fornecidas sobre o particionamento nos planos de execução do tempo de compilação e do runtime.

Compactação de dados

À medida que businesses intelligence se torna cada vez mais comum, as empresas estão colocando mais e mais dados nos data warehouses para análise. O resultado é um crescimento exponencial no tamanho dos dados gerenciados. Em 1995, a primeira pesquisa feita pela Winter Corporation sobre o tamanho do banco de dados indicava que o maior sistema do mundo apresentava um terabyte de dados. Dez anos depois, e o maior banco de dados era aproximadamente 100 vezes maior. O fato mais impressionante é que o tamanho dos data warehouses triplica a cada dois anos. Isso gera novos desafios em termos do gerenciamento dessas grandes quantidades de dados e do fornecimento de níveis aceitáveis de desempenho para consultas de data warehouse. Essas consultas costumam ser complexas, envolvendo muitas junções e agregações, e acessam grandes quantidades de dados. E não é algo incomum muitas consultas na carga de trabalho serem vinculadas a E/S.

A compactação de dados nativos pretende resolver esse problema. O SQL Server 2005 SP2 apresentou um novo formato de armazenamento de comprimento variável, o formato de armazenamento vardecimal, para dados decimais e numéricos. Esse novo formato de armazenamento pode reduzir o tamanho dos bancos de dados de maneira significativa. Já as economias de espaço podem ajudar a melhorar o desempenho das consultas vinculadas a E/S de duas formas. Primeiro, há poucas páginas a serem lidas e, segundo, como os dados são mantidos compactados no pool do buffer, isso aumenta a expectativa de vida da página (em outras palavras, aumenta as chances de que a página solicitada seja encontrada no buffer). Obviamente, a economia de espaço obtida com a compactação de dados apresenta, de fato, um custo de CPU por conta do processo de compactação e descompactação dos dados.

O SQL Server 2008 aproveita o formato de armazenamento vardecimal, fornecendo dois tipos de compactação: ROW e PAGE. A compactação ROW estende o formato de armazenamento vardecimal armazenando todos os tipos de dados de comprimento fixo em um formato de armazenamento de comprimento variável.

Alguns exemplos dos tipos de dados de comprimento fixo são integer, char e float. Mesmo que o SQL Server armazene esses tipos de dados em formato de comprimento variável, a semântica dos tipos de dados permanecerá inalterada (um tipo de dados continua sendo o tipo de dados de comprimento fixo, do ponto de vista do aplicativo). Portanto, isso significa ser possível obter os benefícios da compactação de dados sem exigir nenhuma alteração nos aplicativos.

Já a compactação PAGE minimiza a redundância de dados em colunas de uma ou mais linhas em uma determinada página. Ela usa uma implementação própria do algoritmo LZ78 (Lempel-Ziv), armazenando os dados redundantes apenas uma vez na página e, em seguida, os referenciando nas várias colunas. Observe que quando você usa a compactação PAGE, a compactação ROW também é incluída, na verdade.

As compactações ROW e PAGE podem ser habilitadas em uma tabela ou em um índice, ou em uma ou mais partições das tabelas e dos índices particionados. Isso lhe dá total flexibilidade sobre a escolha de tabelas, índices e partições para compactação, o que permite que você obtenha o equilíbrio certo entre a economia de espaço e o impacto sobre a CPU. A Figura 7 ilustra isso usando uma tabela de vendas particionada de formas diferentes com índices alinhados.

Figure 7** Tabela particionada com configurações de compactação diferentes **(Clique na imagem para aumentar a exibição)

Cada partição representa um trimestre, com outubro-dezembro sendo o último deles. Suponhamos que as duas primeiras partições não sejam acessadas com freqüência, que a terceira apresenta atividade moderada e a última seja a mais ativa. Nesse caso, uma configuração possível é habilitar a compactação PAGE nas duas primeiras partições a fim de obter o máximo em economia de espaço com o mínimo impacto sobre o desempenho da carga de trabalho, além da compactação ROW na terceira partição e nenhuma compactação na última.

É possível habilitar a compactação, online ou offline, usando as instruções DDL (linguagem de definição de dados) Alter Table ou Alter Index. O SQL Server também fornece um procedimento armazenado para estimar a economia de espaço. As economias de espaço obtidas dependerão da distribuição de dados e do esquema do objeto que está sendo compactado.

Com base nos resultados vistos nos testes com muitos bancos de dados de clientes, parece que a maioria deles será capaz de reduzir o tamanho do banco de dados entre 50% e 65% e de melhorar o desempenho das consultas vinculadas a E/S de maneira significativa. No entanto, estimar o impacto sobre o desempenho das consultas vinculadas à CPU é mais complicado e depende da complexidade da consulta. No SQL Server, o custo da descompactação só incorre quando há o acesso a índices ou tabelas. Caso o custo de CPU relativo dos operadores de verificação seja baixo em comparação com o custo geral de CPU da consulta, como normalmente acontece no cenário de data warehouse, você deve observar um impacto inferior a 20% a 30% sobre o uso da CPU.

Exibições indexadas alinhadas com a partição

No SQL Server 2008, as exibições indexadas alinhadas com a partição lhe permitem criar e gerenciar agregações resumidas no data warehouse relacional com mais eficiência e usá-las em cenários nos quais você não poderia usá-las efetivamente antes. Isso melhora o desempenho da consulta. Em um cenário típico, você conta com uma tabela de fatos particionada por data. As exibições indexadas (ou agregações resumidas) são definidas nessa tabela para ajudar a agilizar as consultas. Quando você alterna uma nova partição, as partições correspondentes das exibições indexadas alinhadas com a partição definidas na tabela particionada mudam também, e fazem isso automaticamente.

Há uma melhoria significativa em relação ao SQL Server 2005, em que você deve descartar todas as exibições indexadas definidas em uma tabela particionada antes de usar a operação ALTER TABLE SWITCH a fim de entrar ou sair de uma partição. O recurso das exibições indexadas alinhadas com a partição do SQL Server 2008 lhe oferece os benefícios das exibições indexadas em tabelas particionadas maiores ao mesmo tempo em que evita o custo de recriação das agregações em uma tabela particionada inteira. Entre esses benefícios estão a manutenção automática das agregações e a correspondência da exibição indexada.

Escalonamento de bloqueios no nível da partição

O SQL Server dá suporte ao particionamento por intervalos, que lhe permite particionar os dados por questões de gerenciamento ou agrupar os dados de acordo com o padrão de uso. Dessa forma, por exemplo, os dados de vendas podem ser particionados em limites mensal ou trimestral. É possível mapear uma partição para seu próprio grupo de arquivos e, por sua vez, mapear o grupo de arquivos para um conjunto de arquivos. Isso oferece dois benefícios importantes. Primeiro, é possível fazer o backup e restaurar uma partição como uma unidade independente. Segundo, você pode mapear um grupo de arquivos para um subsistema de E/S lento ou rápido dependendo do padrão de uso ou da carga de consulta.

Um ponto interessante aqui é o padrão de acesso dos dados. As consultas e as operações DML talvez só precisem acessar ou manipular um subconjunto de partições. Portanto, caso esteja analisando, por exemplo, os dados de vendas referentes a 2004, você precisa apenas acessar as partições relevantes e o ideal é que você não seja afetado, exceto em relação aos recursos do sistema pelas consultas que acessam os dados de outras partições simultaneamente. No SQL Server 2005, o acesso simultâneo dos dados em outras partições pode levar a um bloqueio da tabela com impacto sobre o acesso às demais partições.

Para minimizar essa interferência, o SQL Server 2008 apresenta uma opção em nível de tabela para controlar o escalonamento de bloqueios no nível da tabela ou da partição. Por padrão, o escalonamento de bloqueios é habilitado no nível da tabela, como acontece no SQL Server 2005. No entanto, é possível substituir a política de escalonamento de bloqueios da tabela. Assim, por exemplo, você pode definir o escalonamento de bloqueios da seguinte forma:

Alter table <mytable> set (LOCK_ESCALATION = AUTO)

Esse comando instrui o SQL Server a escolher a granularidade do escalonamento de bloqueios apropriada ao esquema da tabela. Caso a tabela não esteja particionada, o escalonamento de bloqueios está no nível da TABELA. Caso a tabela esteja particionada, a granularidade do escalonamento de bloqueios está no nível da partição. Essa opção também é usada como uma dica pelo SQL Server para desqualificar a granularidade de bloqueio no nível da tabela.

Conclusão

Esta é apenas uma visão geral resumida dos recursos aprimorados encontrados no SQL Server 2008 que lhe ajudarão a obter um melhor desempenho em relação às consultas de apoio à decisão em data warehouses relacionais. Mas lembre-se de que, embora tempos de resposta competitivos para as consultas de apoio à decisão sejam essenciais, há outros requisitos importantes que estão além do escopo deste artigo.

Entre algumas das funcionalidades adicionais relacionadas ao data warehouse relacional estão:

• Suporte à sintaxe MERGE em T-SQL para atualizar, excluir ou inserir dados (dimensionais) com uma instrução e uma viagem de ida e volta ao banco de dados.
• Desempenho de registro em log do mecanismo do SQL Server para possibilitar um ETL mais eficiente.
• Grupos configurados de forma a facilitar a escrita de consultas de apoio à decisão agregadas em T-SQL.
• Compactação de backup para reduzir os requisitos de E/S para backups completos e incrementais.
• Governança de recursos para controlar a alocação dos recursos a cargas de trabalho diferentes.

Incentivamos você a consultar informações mais detalhadas sobre todos esses recursos interessantes na página da Web do SQL Server, disponível em microsoft.com/sql.

Gostaríamos de agradecer a Boris Baryshnikov, Prem Mehra, Peter Zabback e Shin Zhang por fornecerem sua experiência técnica.

Sunil Agarwal é gerente de programa sênior do grupo de mecanismo de armazenamento do SQL Server na Microsoft. Ele é responsável por simultaneidade, índices, tempdb, LOBS, capacidade de suporte e importações/exportações em massa.

Torsten Grabs é gerente geral sênior de programa do mecanismo de armazenamento de núcleo na equipe do Microsoft SQL Server. Ele possui PhD em sistemas de bancos de dados e tem dez anos de experiência trabalhando com o SQL Server.

Dr. Joachim Hammer é gerente de programa do grupo de processamento de consultas na Microsoft. Ele é especialista na otimização de consultas para aplicativos de data warehouse em larga escala, bem como em consultas distribuídas, em ETL e na integração de informações.

© 2008 Microsoft Corporation e CMP Media, LLC. Todos os direitos reservados. A reprodução parcial ou completa sem autorização é proibida..