Tabelas e índices particionados

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

O SQL Server, o Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure dão suporte para particionamento de tabela e índice. Os dados de tabelas e índices particionados são divididos em unidades que podem ser distribuídas em mais de um grupo de arquivos em um banco de dados ou armazenados em um único grupo de arquivos. Quando existem vários arquivos em um grupo de arquivos, os dados são distribuídos pelos arquivos usando o algoritmo de preenchimento proporcional. Os dados são particionados horizontalmente, de forma que os grupos de linhas são mapeados em partições individuais. Todas as partições de um único índice ou de uma única tabela devem residir no mesmo banco de dados. A tabela ou o índice é tratado como uma única entidade lógica quando são executadas consultas ou atualizações nos dados.

Antes do SQL Server 2016 (13.x) SP1, as tabelas e os índices particionados não estavam disponíveis em todas as edições do SQL Server. Veja uma lista de recursos que têm suporte nas edições do SQL Server em Edições e recursos com suporte no SQL Server 2022. As tabelas e índices particionados estão disponíveis em todas as camadas de serviço do Banco de Dados SQL do Azure e da Instância Gerenciada de SQL do Azure.

O particionamento de tabela também está disponível em pools de SQL dedicados no Azure Synapse Analytics, com algumas diferenças de sintaxe. Saiba mais em Como particionar tabelas no pool de SQL dedicado.

Importante

O mecanismo de banco de dados dá suporte a até 15 mil partições por padrão. Em versões anteriores ao SQL Server 2012 (11.x), o número de partições era limitado por padrão a 1.000.

Benefícios do particionamento

O particionamento de tabelas ou índices grandes pode ter a capacidade de gerenciamento e os benefícios de desempenho a seguir.

  • Você pode transferir ou acessar subconjuntos de dados de forma rápida e eficaz e, ao mesmo tempo, manter a integridade de uma coleção de dados. Por exemplo, uma operação como o carregamento de dados de um sistema OLTP para OLAP leva apenas segundos, em vez dos minutos ou horas necessários quando os dados não estão paticionados.

  • Você pode executar operações de manutenção ou retenção de dados mais rapidamente em uma ou mais partições. As operações são mais eficientes porque elas visam apenas estes subconjuntos de dados, e não a tabela inteira. Por exemplo, você pode optar por compactar dados em uma ou mais partições ou recriar uma ou mais partições de um índice ou truncar dados em uma só partição. Você também pode alternar partições individuais de uma tabela para uma tabela de arquivos.

  • Você pode aprimorar o desempenho de consultas com base nos tipos de consultas executadas com frequência. Por exemplo, o otimizador de consulta pode processar consultas de junção de igualdade entre duas ou mais tabelas particionadas mais rápido, quando as colunas de particionamento são iguais às colunas nas quais as tabelas são unidas. Confira Consultas abaixo para obter mais informações.

Você pode melhorar o desempenho habilitando o escalonamento de bloqueios em nível de partição em, e não em uma tabela inteira. Isso pode reduzir a contenção de bloqueio na tabela. Para reduzir a contenção de bloqueio permitindo o escalonamento de bloqueios para a partição, defina opção LOCK_ESCALATION da instrução ALTER TABLE como AUTO.

Componentes e conceitos

As condições a seguir são aplicáveis ao particionamento de tabela e de índice.

Função de partição

Uma 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. Cada valor na coluna de particionamento é uma entrada para a função de particionamento, que retorna um valor de partição.

A função de partição define o número de partições e os limites de partição que a tabela terá. Por exemplo, considerando uma tabela que contém dados de ordem de venda, você pode desejar particionar a tabela em 12 (mensalmente) partições com base em uma coluna datetime como uma data de vendas.

Um tipo de intervalo (LEFT ou RIGHT), especifica como os valores de marco de delimitação da função de partição serão colocados nas partições resultantes:

  • Um intervalo LEFT especifica que o valor do marco de delimitação pertence ao lado esquerdo do valor do marco de delimitação quando os valores do intervalo são classificados pelo mecanismo de banco de dados em ordem ascendente da esquerda para a direita. Em outras palavras, o valor de marco de delimitação mais alto será incluído em uma partição.
  • Um intervalo RIGHT especifica que o valor do marco de delimitação pertence ao lado direito do intervalo de valor do marco de delimitação quando os valores do intervalo são classificados pelo mecanismo de banco de dados em ordem ascendente da esquerda para a direita. Em outras palavras, o valor de marco de delimitação mais baixo será incluído em cada partição.

Se nem LEFT nem RIGHT for especificado, o intervalo LEFT será o padrão.

Por exemplo, a função de partição a seguir particiona uma tabela ou um índice em 12 partições, uma para cada mês de valores válidos no ano em uma coluna datetime. Um intervalo RIGHT é usado, indicando que os valores de marco de delimitação servirão como valores delimitadores inferiores em cada partição. Os intervalos RIGHT geralmente são mais simples de trabalhar ao particionar uma tabela com base em uma coluna de tipos de dados datetime ou datetime2, pois linhas com um valor de meia-noite serão armazenadas na mesma partição que linhas com valores posteriores no mesmo dia. Da mesma forma, se usar o tipo de dados de data e usar partições de um mês ou mais, um intervalo RIGHT manterá o primeiro dia do mês na mesma partição que os dias posteriores desse mês. Isso ajuda na Eliminação de partição precisa ao consultar o valor de um dia inteiro de dados.

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)  
AS RANGE RIGHT FOR VALUES ('2022-02-01', '2022-03-01', '2022-04-01',  
               '2022-05-01', '2022-06-01', '2022-07-01', '2022-08-01',   
               '2022-09-01', '2022-10-01', '2022-11-01', '2022-12-01');

A tabela a seguir mostra como uma tabela ou um índice que usa essa função de partição na coluna de particionamento datecol seria particionada. O dia 1 de fevereiro é o primeiro ponto do marco de delimitação definido na função, por isso atua como o marco de delimitação inferior da partição 2.

Partição 1 2 ... 11 12
Valores datecol<2022-02-01 12:00AM datecol>= 2022-02-01 12:00AM AND datecol<2022-03-01 12:00AM datecol>= 2022-11-01 12:00AM AND col1<2022-12-01 12:00AM datecol>= 2022-12-01 12:00AM

Para RANGE LEFT e RANGE RIGHT, a partição mais à esquerda tem o valor mínimo do tipo de dados como seu limite inferior, e a partição mais à direita tem o valor máximo do tipo de dados como seu limite superior.

Encontre mais exemplos de funções de partição ESQUERDA e DIREITA em CREATE PARTITION FUNCTION (Transact-SQL).

Esquema de partição

Um esquema de partição é um objeto de banco de dados que mapeia as partições de uma função de partição para um grupo de arquivos ou para vários grupos de arquivos.

Encontre exemplo de sintaxe para criar esquemas de partição em CREATE PARTITION SCHEME (Transact-SQL).

Grupos de arquivos

A principal razão para colocar suas partições em vários grupos de arquivos é certificar-se de que você pode executar independentemente operações de backup e restauração em partições. Isso porque se pode executar backups em grupos de arquivos individuais. Ao usar o armazenamento em camadas, o uso de vários grupos de arquivos permite atribuir partições específicas a níveis de armazenamento específicos, por exemplo, para colocar partições mais antigas e acessadas com menos frequência em um armazenamento mais lento e mais econômico. Todos os outros benefícios de particionamento se aplicam, independentemente do número de grupos de arquivos usados ou do posicionamento de partição em grupos de arquivos específicos.

O gerenciamento de arquivos e grupos de arquivos para tabelas particionadas pode adicionar complexidade considerável às tarefas administrativas ao longo do tempo. Se os procedimentos de backup e restauração não se beneficiarem do uso de vários grupos de arquivos, um só grupo de arquivos para todas as partições será recomendado. As mesmas Regras para criar arquivos e grupos de arquivos se aplicam a objetos particionados que se aplicam a objetos não particionados.

Observação

O particionamento tem suporte total no banco de dados SQL do Azure. Como há suporte apenas para o grupo de arquivos PRIMARY no Banco de Dados SQL do Azure, todas as partições devem ser colocadas no grupo de arquivos PRIMARY.

Encontre código de exemplo para criar grupos de arquivos para SQL Server e Instância Gerenciada de SQL do Azure em Opções de Arquivo e Grupo de Arquivos ALTER DATABASE (Transact-SQL).

Coluna de particionamento

A coluna de uma tabela ou índice que uma função de partição usa para particionar a tabela ou índice. As seguintes considerações se aplicam ao selecionar uma coluna de particionamento:

  • Colunas computadas que participam de uma função de partição devem ser criadas explicitamente como PERSISTED.
    • Como apenas uma coluna pode ser usada como coluna de partição, em alguns casos a concatenação de várias colunas com uma coluna computada pode ser útil.
  • Colunas de todos os tipos de dados que são válidos para uso como colunas de chave de índice podem ser usados como uma coluna de particionamento, exceto timestamp.
  • Colunas dos tipos de dados de objeto grande (LOB), como ntext, text, image, xml, varchar(max), nvarchar(max) e varbinary(max), não podem ser especificadas.
  • Não podem ser especificados o tipo definido pelo usuário do CLR (Common Language Runtime) do Microsoft .NET Framework e colunas de tipo de dados do alias.

Para particionar um objeto, especifique o esquema de partição e a coluna de particionamento nas instruções CREATE TABLE (Transact-SQL), ALTER TABLE (Transact-SQL) e CREATE INDEX (Transact-SQL).

Ao criar um índice não clusterizado, se partition_scheme_name ou filegroup não for especificado e a tabela estiver particionada, o índice será colocado no mesmo esquema de partição, usando a mesma coluna de particionamento que a tabela subjacente. Para alterar como um índice existente é particionado, use CREATE INDEX com a cláusula DROP_EXISTING. Isso permite particionar um índice não particionado, tornar um índice particionado não particionado ou alterar o esquema de partição do índice.

Índice alinhado

Um índice que é baseado no mesmo esquema de partição que sua tabela correspondente. Quando uma tabela e seus índices estão em alinhamento, o mecanismo de banco de dados pode trocar as partições para dentro ou para fora da tabela de modo rápido e eficaz enquanto mantém a estrutura de partição tanto na tabela quanto em seus índices. Um índice não precisa participar na 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 a tabela base devem ser essencialmente o mesmo, no sentido em que:

  • os argumentos das funções de partição têm o mesmo tipo de dados.
  • elas definem o mesmo número de partições.
  • elas definem os mesmos valores de limite para partições.

Como particionar í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 mecanismo de banco de dados 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. Para obter mais informações sobre os índices clusterizados e a arquitetura de índice, confira Diretrizes de design de índices clusterizados.

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 mecanismo de banco de dados adiciona a coluna de particionamento, por padrão, como a coluna de particionamento por padrão como uma coluna não chave não chave (incluída) para garantir que o índice estará alinhado com a tabela base. O mecanismo de banco de dados não adicionará a coluna de particionamento ao índice se este já estiver presente no índice. Para obter mais informações sobre os índices não clusterizados e a arquitetura de índice, confira Diretrizes de design de índices não clusterizados.

Índice não alinhado

Um índice não alinhado é particionado de modo diferente de sua tabela correspondente. Ou seja, o índice tem um esquema de partição diferente que o coloca em um grupo de arquivos ou conjunto de grupos 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 colocadas com mais tabelas que usam 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 satisfazer os critérios de filtro da consulta.

Saiba mais sobre eliminação de partições e conceitos relacionados em Aprimoramentos de processamento de consultas em tabelas e índices particionados.

Limitações

  • O escopo de uma função e de um esquema de partição é limitado ao banco de dados em que foram criados. No banco de dados, as funções de partição residem em um namespace separado das outras funções.

  • Se alguma linha em uma tabela particionada tiver NULLs na coluna de particionamento, essas linhas serão colocadas na partição mais à esquerda. No entanto, se NULL for especificado como o primeiro valor de marco de delimitação e RANGE RIGHT for especificado na definição de função de partição, a partição extrema esquerda permanecerá vazia e os valores NULL serão colocados na segunda partição.

Diretrizes de desempenho

O mecanismo de banco de dados dá suporte a até 15 mil partições por tabela ou índice. No entanto, o uso de mais de mil partições tem implicações na memória, operações de índice particionadas, comandos DBCC e consultas. Esta seção descreve as implicações de desempenho de usar mais de mil partições e fornece soluções alternativas conforme necessário.

Com até 15 mil partições permitidas por tabela ou índice particionado, você pode armazenar dados por longas durações em uma única tabela. Porém, você só deve reter dados enquanto necessário e manter um equilíbrio entre desempenho e número de partições.

Uso de memória e diretrizes

É recomendável usar pelo menos 16 GB de RAM se um número grande 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 intenso de memória podem ficar sem memória em operações executadas em um número grande de partições. Portanto, quanto mais memória você tiver acima de 16 GB, menor será a probabilidade de encontrar problemas de desempenho e memória.

As limitações de memória podem afetar o desempenho ou habilidade do mecanismo de banco de dados de construir um índice particionado. Este será o caso especialmente quando o índice não estiver alinhado com sua tabela base ou não estiver alinhado com o índice clusterizado, se a tabela já tiver um índice clusterizado.

No SQL Server e na Instância Gerenciada de SQL do Azure, você pode aumentar a Opção de Configuração do Servidor index create memory (KB). Para obter mais informações, confira Configurar a opção index create memory de configuração do servidor. Para o Banco de Dados SQL do Azure, considere aumentar temporária ou permanentemente o objetivo de nível de serviço para o banco de dados no portal do Azure para alocar mais memória.

Operações de índice particionado

É 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 recompilação de índices alinhados poderão demorar mais para serem executadas à medida que aumentar o número de partições. É recomendável não executar vários comandos de índice de criação e recriação ao mesmo tempo porque você pode encontrar problemas de desempenho e memória.

Quando o mecanismo de banco de dados executar uma classificação para construir índices particionados, ele construirá primeiro uma tabela de classificação para cada partição. Ele 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 mecanismo de banco de dados estiver usando paralelismo de consulta para criar a operação em um computador multiprocessador. Isso ocorre porque quanto maior os DOPs (graus de paralelismo), maior será o requisito de memória. Por exemplo, se o mecanismo de banco de dados define os DOPs como 4, um índice particionado não alinhado com 100 partições requer memória suficiente para quatro processadores para classificar serialmente 4 mil páginas, ao mesmo tempo, ou 16 mil 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.

Comandos DBCC

Com um número maior de partições, os comandos como DBCC CHECKDB e DBCC CHECKTABLE podem demorar mais para serem executados à medida que aumentar o número de partições.

Consultas

Depois de particionar uma tabela ou índice, as consultas que usam a eliminação de partição podem ter desempenho comparável ou aprimorado com número maior de partições. Consultas que não usam a eliminação de partição podem levar mais tempo para executar à medida que o número de partições aumenta.

Por exemplo, digamos que uma tabela tem 100 milhões de linhas e colunas A, Be C.

  • No cenário 1, a tabela é dividida em mil 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 cláusula WHERE filtrada 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 a serem examinadas em uma partição. Uma consulta com uma cláusula WHERE filtrada 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 a serem examinadas.

As consultas que usam operadores como TOP ou MAX/MIN em colunas que não sejam a coluna de particionamento podem sofrer redução do desempenho com o particionamento porque todas as partições precisam ser avaliadas.

Da mesma forma, uma consulta que executa uma busca de linha única ou uma varredura de intervalo pequeno levará mais tempo em uma tabela particionada do que em uma tabela não particionada se o predicado de consulta não incluir a coluna de particionamento, porque ele precisará executar tantas buscas ou varreduras quanto houver partições. Por isso, o particionamento raramente melhora o desempenho em sistemas OLTP em que essas consultas são comuns.

Se você executar consultas que envolvem uma junção de igualdade (equijoin) entre duas ou mais tabelas particionadas, as colunas de particionamento deverão ser as mesmas que as colunas nas quais as tabelas são unidas. Além disso, as tabelas ou seus índices devem ser colocados. Isso significa que eles usam a mesma função de partição nomeada ou usam funções de partição diferentes que são essencialmente as mesmas, porque:

  • Elas têm o mesmo número de parâmetros usados para particionamento e os parâmetros correspondentes são os mesmos tipos de dados.
  • Definem o mesmo número de partições.
  • Definem os mesmos valores de limite para partições.

Desse modo, o otimizador de consulta pode processar a junção mais rapidamente, porque as próprias partições podem ser unidas. Se uma consulta unir duas tabelas que não estão colocadas ou não estão particionadas no campo de junção, a presença de partições pode realmente retardar o processamento da consulta em vez de acelerá-lo.

Pode ser útil usar $PARTITION em algumas consultas. Saiba mais em $PARTITION (Transact-SQL).

Para obter mais informações sobre manipulação de partição no processamento de consultas, incluindo estratégia de execução de consulta paralela para tabelas e índices particionados e práticas recomendadas adicionais, confira Aprimoramentos de processamento de consulta em tabelas e índices particionados.

Alterações de comportamento em computação de estatísticas durante operações de índice particionadas

No Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure e SQL Server 2012 (11.x) e superiores, as estatísticas não são criadas por meio do exame de todas as linhas da tabela quando um índice particionado é criado ou reconstruído. 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 de uma versão do SQL Server inferior a 2012 (11.x), você pode notar uma diferença nos dados de histograma para esses índices. Essa alteração no comportamento pode não afetar o desempenho de consulta. Para obter as estatísticas dos índices particionados ao examinar todas as linhas da tabela, use CREATE STATISTICS ou UPDATE STATISTICS com a cláusula FULLSCAN.

Conteúdo relacionado

Saiba mais sobre tabelas particionadas e estratégias de índice nos seguintes artigos: