Tabelas e índices particionados

O SQL Server dá suporte ao particionamento de tabela e índice. Os dados de tabelas e índices particionados são divididos em unidades que podem ser distribuídas por mais de um grupo de arquivos em um banco de dados. Os dados são particionados horizontalmente, de modo que os grupos de linhas sejam mapeados em partições individuais. Todas as partições de um único índice ou tabela devem residir no mesmo banco de dados. A tabela ou índice é tratado como uma única entidade lógica quando consultas ou atualizações são executadas nos dados. Tabelas e índices particionados não estão disponíveis em todas as edições do MicrosoftSQL Server. Para obter uma lista de recursos compatíveis com as edições do SQL Server, consulte recursos compatíveis com as edições do SQL Server 2014.

Benefícios do particionamento

Particionar tabelas ou índices grandes pode ter os seguintes benefícios de gerenciamento e desempenho.

• Você pode transferir ou acessar subconjuntos de dados de forma rápida e eficiente, mantendo a integridade de uma coleta de dados. Por exemplo, uma operação como carregar dados de um OLTP para um sistema OLAP leva apenas segundos, em vez dos minutos e horas que a operação leva quando os dados não são particionados.

• Você pode executar operações de manutenção em uma ou mais partições mais rapidamente. As operações são mais eficientes porque destinam-se apenas a esses subconjuntos de dados, em vez de toda a tabela. Por exemplo, você pode optar por compactar dados em uma ou mais partições ou recriar uma ou mais partições de um índice.

• Você pode melhorar o desempenho da consulta, com base nos tipos de consultas que você executa com frequência e na configuração de hardware. Por exemplo, o otimizador de consulta pode processar consultas de equi-join entre duas ou mais tabelas particionadas mais rapidamente quando as colunas de particionamento nas tabelas são as mesmas, porque as próprias partições podem ser unidas.

  Quando o SQL Server executa a classificação de dados para operações de E/S, ele classifica os dados primeiro por partição. O SQL Server acessa uma unidade por vez e isso pode reduzir o desempenho. Para melhorar o desempenho na ordenação de dados, distribua os arquivos de dados das suas partições em mais de um disco ao configurar um RAID. Dessa forma, embora o SQL Server ainda classifique dados por partição, ele pode acessar todas as unidades de cada partição ao mesmo tempo.

  Além disso, você pode melhorar o desempenho habilitando o escalonamento de bloqueio no nível da partição em vez de uma tabela inteira. Isso pode reduzir a contenção de bloqueio na tabela.

Componentes e conceitos

Os termos a seguir são aplicáveis ao particionamento de tabela e índice.

Função de partição
Um objeto de banco de dados que define como as linhas de uma tabela ou índice são mapeadas para um conjunto de partições com base nos valores de determinada coluna, chamada de coluna de particionamento. Ou seja, a função de partição define o número de partições que a tabela terá e como os limites das partições são definidos. Por exemplo, considerando uma tabela que contém dados de pedidos de vendas, convém particionar a tabela em doze partições (mensais) com base em uma coluna datetime, como a data de vendas.

Esquema de partição
Um objeto de banco de dados que mapeia as partições de uma função de partição para um conjunto de grupos de arquivos. O principal motivo para colocar suas partições em grupos de arquivos separados é garantir que você possa executar operações de backup de forma independente em partições. Isso ocorre porque você pode executar backups em grupos de arquivos individuais.

Coluna de particionamento
A coluna de uma tabela ou índice que uma função de partição usa para particionar a tabela ou o índice. As colunas computadas que participam de uma função de partição devem ser explicitamente marcadas como PERSISTED. Todos os tipos de dados válidos para uso como colunas de índice podem ser usados como uma coluna de particionamento, exceto timestamp. Os tipos de dados ntext, text, image, xml, varchar(max), nvarchar(max), ou varbinary(max) não podem ser especificados. Além disso, não é possível especificar colunas de tipo de dados CLR (common language runtime) do Microsoft .NET Framework, definidas pelo usuário e colunas de tipo de dados de alias.

Índice alinhado
Um índice criado no mesmo esquema de partição que sua tabela correspondente. Quando uma tabela e seus índices estão alinhados, o SQL Server pode alternar partições de forma rápida e eficiente, mantendo a estrutura de partição da tabela e de seus índices. Um índice não precisa participar da mesma função de partição nomeada para ser alinhado com sua tabela base. No entanto, a função de partição do índice e da tabela base deve ser essencialmente a mesma, nesse 1) os argumentos das funções de partição têm o mesmo tipo de dados, 2) definem o mesmo número de partições e 3) definem os mesmos valores de limite para partições.

Índice desalinhado
Um índice particionado independentemente de sua tabela correspondente. Ou seja, o índice tem um esquema de partição diferente ou é colocado em um grupo de arquivos separado da tabela base. A criação de um índice particionado não alinhado pode ser útil nos seguintes casos:

• A tabela base não foi particionada.

• A chave de índice é exclusiva e não contém a coluna de particionamento da tabela.

• Você deseja que a tabela base participe de junções agrupadas com mais tabelas usando colunas de junção diferentes.

Eliminação de partição
O processo pelo qual o otimizador de consulta acessa apenas as partições relevantes para atender aos critérios de filtro da consulta.

Diretrizes de desempenho

O novo limite mais alto de 15.000 partições afeta memória, operações de índice particionadas, comandos DBCC e consultas. Esta seção descreve as implicações de desempenho de aumentar o número de partições acima de 1.000 e fornece soluções alternativas conforme necessário. Com o limite do número máximo de partições sendo aumentado para 15.000, você pode armazenar dados por mais tempo. No entanto, você deve reter dados apenas pelo tempo necessário e manter um equilíbrio entre o desempenho e o número de partições.

Uso e diretrizes de memória

Recomendamos que você use pelo menos 16 GB de RAM se um grande número de partições estiver em uso. Se o sistema não tiver memória suficiente, instruções DML (Linguagem de Manipulação de Dados), instruções DDL (Linguagem de Definição de Dados) e outras operações poderão falhar devido à memória insuficiente. Sistemas com 16 GB de RAM que executam muitos processos com uso intensivo de memória podem ficar sem memória em operações executadas em um grande número de partições. Portanto, quanto mais memória você tiver mais de 16 GB, menor a probabilidade de encontrar problemas de desempenho e memória.

As limitações de memória podem afetar o desempenho ou a capacidade do SQL Server de criar um índice particionado. Esse é especialmente o caso quando o índice não está alinhado com sua tabela base ou não está alinhado com seu índice clusterizado, se a tabela já tiver um índice clusterizado aplicado a ele.

Operações de índice particionado

As limitações de memória podem afetar o desempenho ou a capacidade do SQL Server de criar um índice particionado. Esse é especialmente o caso com índices desalinhados. É possível criar e reconstruir índices não alinhados em uma tabela com mais de 1.000 partições, mas não há suporte para isso. Fazer isso pode provocar degradação do desempenho ou consumo excessivo de memória durante essas operações.

A criação e a recriação de índices alinhados podem demorar mais para serem concluídos à medida que o número de partições aumenta. Recomendamos que você não execute vários comandos de criação e recompilação de índice ao mesmo tempo em que pode encontrar problemas de desempenho e memória.

Quando o SQL Server executa a classificação para criar índices particionados, ele primeiro cria uma tabela de classificação para cada partição. Em seguida, ele constrói as tabelas de ordenação no respectivo grupo de arquivos de cada partição ou em tempdb, se a opção de índice SORT_IN_TEMPDB for especificada. Cada tabela de classificação requer uma quantidade mínima de memória para compilar. Quando você está criando um índice particionado alinhado com sua tabela base, as tabelas de classificação são criadas uma de cada vez, usando menos memória. No entanto, quando você está criando um índice particionado não alinhado, as tabelas de ordenação são criadas ao mesmo tempo. Como resultado, deve haver memória suficiente para lidar com essas classificações simultâneas. Quanto maior o número de partições, mais memória será necessária. O tamanho mínimo para cada tabela de classificação, para cada partição, é de 40 páginas, com 8 quilobytes por página. Por exemplo, um índice particionado não alinhado com 100 partições requer memória suficiente para classificar serialmente 4.000 (40 * 100) páginas ao mesmo tempo. Se essa memória estiver disponível, a operação de build terá êxito, mas o desempenho poderá sofrer. Se essa memória não estiver disponível, a operação de build falhará. Como alternativa, um índice particionado alinhado com 100 partições requer apenas memória suficiente para classificar 40 páginas, pois as classificações não são executadas ao mesmo tempo.

Para índices alinhados e não alinhados, o requisito de memória poderá ser maior se o SQL Server estiver aplicando graus de paralelismo à operação de construção em um computador multiprocessador. Isso ocorre porque quanto maior o grau de paralelismo, maior o requisito de memória. Por exemplo, se o SQL Server definir graus de paralelismo como 4, um índice particionado não alinhado com 100 partições exigirá memória suficiente para quatro processadores classificarem 4.000 páginas ao mesmo tempo, totalizando 16.000 páginas. Se o índice particionado estiver alinhado, a necessidade de memória é reduzida para que quatro processadores classifiquem 40 páginas, ou 160 (4 * 40) páginas. Você pode usar a opção de índice MAXDOP para reduzir manualmente os graus de paralelismo.

Comandos DBCC

Com um número maior de partições, os comandos DBCC podem levar mais tempo para serem executados à medida que o número de partições aumenta.

Perguntas

As consultas que usam a eliminação de partição podem ter um desempenho comparável ou aprimorado com um número maior de partições. As consultas que não usam a eliminação de partição podem levar mais tempo para serem executadas à medida que o número de partições aumenta.

Por exemplo, suponha que uma tabela tenha 100 milhões de linhas e colunas A, Be C. No cenário 1, a tabela é dividida em 1000 partições na coluna A. No cenário 2, a tabela é dividida em 10.000 partições na coluna A. Uma consulta na tabela que tem uma filtragem de cláusula WHERE na coluna A executará a eliminação de partição e examinará uma partição. Essa mesma consulta pode ser executada mais rapidamente no cenário 2, pois há menos linhas para verificar em uma partição. Uma consulta que tem uma filtragem de cláusula WHERE na coluna B examinará todas as partições. A consulta pode ser executada mais rapidamente no cenário 1 do que no cenário 2, pois há menos partições para verificar.

Consultas que usam operadores como TOP ou MAX/MIN em colunas diferentes da coluna de particionamento podem apresentar desempenho reduzido com particionamento, pois todas as partições devem ser avaliadas.

Alterações de comportamento na computação de estatísticas durante operações de índice particionado

A partir do SQL Server 2012, as estatísticas não são criadas verificando todas as linhas na tabela quando um índice particionado é criado ou recriado. Em vez disso, o otimizador de consultas usa o algoritmo de amostragem padrão para gerar estatísticas. Depois de atualizar um banco de dados com índices particionados, você poderá notar uma diferença nos dados de histograma desses índices. Essa alteração no comportamento pode não afetar o desempenho das consultas. Para obter estatísticas em índices particionados verificando todas as linhas da tabela, use CREATE STATISTICS ou UPDATE STATISTICS com a cláusula FULLSCAN.

Tarefas Relacionadas

Conteúdo relacionado

Você pode achar úteis os seguintes white papers sobre estratégias e implementações de tabelas particionadas e índices.

• Tabela particionada e estratégias de índice usando o SQL Server 2008

• Como implementar uma janela deslizante automática

• Carregamento em massa em uma tabela particionada

• Aprimoramentos de processamento de consultas em tabelas e índices particionados

• Principais 10 práticas recomendadas para a criação de um data warehouse relacional em grande escala