Diretrizes especiais para índices particionados
Apesar de índices particionados poderem ser implementados independentemente de suas tabelas, normalmente, é adequado projetar uma tabela particionada e criar um índice na tabela. Quando você fizer isso, o SQL Server particionará automaticamente o índice usando o mesmo esquema de partição e coluna particionada como tabela. Como resultado, o índice é particionado essencialmente da mesma maneira que a tabela. Isto deixa o índice alinhado com a tabela.
O SQL Server não alinhará o índice com a tabela se você especificar um esquema de partição diferente ou um grupo de arquivos separado para colocar o índice no momento da criação.
O alinhamento de um índice com uma tabela particionada é especialmente importante se você antecipar que ela será expandida com partições adicionais ou que ela estará envolvida em freqüentes trocas de partição. Para obter mais informações, consulte Criando partições para gerenciar subconjuntos de dados. Quando uma tabela e seus índices estão em alinhamento, o SQL Server pode trocar as partições rápida e eficientemente enquanto, mantém a estrutura de partição em ambos, tabela e seus índices.
.gif "Observação") Observação |
|---|
Um índice não precisa participar na mesma função de partição nomeada para ser alinhado com sua tabela base. Entretanto, a função de partição de um índice e a tabela base devem ser essencialmente as mesmas, em que 1) os argumentos das funções de partição têm o mesmo tipo de dados, 2) eles definem o mesmo número de partições, e 3) eles definem os mesmos valores de limite para as partições. |
A guia Opções de Ajuste do Orientador de Otimização do Mecanismo de Banco de Dados fornece a configuração de Particionamento alinhado para especificar que os novos índices recomendados sejam alinhados com suas tabelas base. A configuração Manter particionamento alinhado pode ser usada para o mesmo propósito e também pode se usada para descartar os índices não alinhados existentes. Para obter mais informações, consulte Orientador de Otimização do Mecanismo de Banco de Dados (guia Opções de Ajuste). Normalmente, o Orientador de Otimização do Mecanismo de Banco de Dados pode ser usado para recomendar índices para o desempenho, isso pode ser uma mistura de índices alinhados e não alinhados. Para obter mais informações, consulte Visão geral do Orientador de Otimização do Mecanismo de Banco de Dados.
O projeto de índice particionado independentemente (não alinhado) da tabela base 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 dispostas com mais tabelas que usam colunas de junção diferentes.
| Observação |
|---|
Para habilitar a troca de partição, todos os índices na tabela devem estar alinhados. |
Considere as informações nas seções a seguir ao criar um índice particionado.
Particionando índices exclusivos
Ao particionar um índice exclusivo (clusterizado ou não clusterizado), a coluna de particionamento deve ser escolhida entre aquelas usadas na chave de índice exclusiva.
| Observação |
|---|
Essa restrição habilita o SQL Server para investigar apenas uma única partição para ter certeza de que nenhuma duplicata do novo valor de chave já exista na tabela. |
Se não for possível incluir a coluna particionada na chave exclusiva, você deverá usar um gatilho DML em vez de impor exclusividade.
Particionando índices clusterizados
Ao particionar um índice clusterizado, a chave de clustering deve conter a coluna de particionamento. Quando particiona um índice clusterizado não exclusivo e a coluna de particionamento não está explicitamente especificada na chave de clustering, o SQL Server adiciona a coluna de particionamento, por padrão, à lista de chaves de índices clusterizados. Se o índice clusterizado for exclusivo, você deve especificar explicitamente que a chave de índice clusterizado contém a coluna de particionamento.
Particionando índices não clusterizados
Ao particionar um índice não clusterizado exclusivo, a chave de índice deve conter a coluna de particionamento. Ao particionar um índice não clusterizado e não exclusivo, o SQL Server adiciona a coluna de particionamento, por padrão, como a coluna do índice não-chave (incluída) para garantir que o índice estará alinhado com a tabela base. O SQL Server não adiciona a coluna de particionamento ao índice se este já estiver presente no índice.
Limitações de memória e índices particionados
As limitações de memória podem afetar o desempenho ou habilidade do SQL Server para construir um índice particionado. Este será o caso especialmente quando o índice não está alinhado com sua tabela base ou não está alinhado com o índice clusterizado, se a tabela já tiver um índice clusterizado aplicado a ela.
Quando o SQL Server executar uma classificação para construir índices particionados, ele construirá primeiro uma tabela de classificação para cada partição. Ele então construirá as tabelas de classificação no respectivo grupo de arquivos de cada partição ou no tempdb, se a opção de índice SORT_IN_TEMPDB for especificada.
Cada tabela de classificação exige uma quantia mínima de memória para construir. Quando você estiver construindo um índice particionado que está alinhado com a tabela base, uma tabela de classificação por vez será criada, usando menos memória. Porém, quando você estiver construindo um índice particionado não-alinhado, as tabelas de classificação serão criadas ao mesmo tempo.
Como resultado, deve haver memória suficiente para controlar 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 quilobites 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 x 100) páginas ao mesmo tempo. Se essa memória estiver disponível, a operação de construção terá sucesso, mas o desempenho poderá ser afetado. Se essa memória não estiver disponível, a operação de criação falhará. Como alternativa, um índice particionado alinhado com 100 partições requer apenas memória suficiente para classificar serialmente 40 páginas, porque as classificações não são executadas ao mesmo tempo.
Para ambos os índices, alinhados e não alinhados, os requisitos de memória poderão ser maiores se o SQL Server aplicar graus de paralelismo à operação de criação em um computador com multiprocessador. Isso ocorre porque quanto maior os graus de paralelismo, maior será o requisito de memória. Por exemplo, se o SQL Server define os graus de paralelismo como 4, um índice particionado não alinhado com 100 partições requer memória suficiente para quatro processadores para classificar serialmente 4.000 páginas, ao mesmo tempo, ou 16.000 páginas. Se o índice particionado for alinhado, o requisito de memória será reduzido para quatro processadores classificando 40 páginas, 160 (4 x 40) páginas. Você pode usar a opção de índice MAXDOP para reduzir os graus de paralelismo manualmente. Para obter mais informações, consulte Configurando operações de índice paralelo.
Para obter mais informações sobre como o SQL Server executa operações de classificação quando você está construindo índices, consulte tempdb e criação de índice.
Consulte também