Tabelas e índices particionados

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada SQL do Azure

Os dados de tabelas e índices particionados são divididos em unidades que podem ser espalhadas por mais de um grupo de arquivos em um banco de dados ou armazenadas em um único grupo de arquivos. Quando existem vários arquivos em um grupo de arquivos, os dados são distribuídos entre os arquivos usando o algoritmo de preenchimento proporcional. Os dados são particionados horizontalmente, de modo que grupos de linhas são 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 realizadas nos dados.

Benefícios do particionamento

O particionamento de 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 ou retenção de dados em uma ou mais partições mais rapidamente. As operações são mais eficientes porque visam apenas esses subconjuntos de dados, em vez de toda a tabela. Por exemplo, você pode optar por compactar dados em uma ou mais partições, reconstruir uma ou mais partições de um índice ou truncar dados em uma única partição. Você também pode alternar partições individuais de uma tabela para uma tabela de arquivo.
Você pode melhorar o desempenho da consulta, com base nos tipos de consultas que você executa com freqüência. Por exemplo, o otimizador de consulta pode processar consultas equijoin entre duas ou mais tabelas particionadas mais rapidamente quando as colunas de particionamento são as mesmas que as colunas nas quais as tabelas são unidas. Para obter mais informações, consulte a seção Consultas.

Você pode melhorar o desempenho ativando 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. Para reduzir a contenção de bloqueio permitindo o escalonamento de bloqueio para a partição, defina a opção da instrução LOCK_ESCALATIONALTER TABLE para AUTO.

Componentes e conceitos

Os seguintes termos são aplicáveis ao particionamento de tabelas e índices.

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 uma determinada coluna, chamada 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 partition define o número de partições e os limites de partição que a tabela terá. Por exemplo, dada uma tabela que contém dados de encomendas de venda, poderá querer particionar a tabela em 12 partições (mensais) com base numa coluna data e hora, como a data de uma venda.

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

Um intervalo do tipo LEFT especifica que o valor limite pertence ao lado esquerdo do intervalo de valores limites quando os valores de intervalo são classificados pelo motor do banco de dados em ordem crescente da esquerda para a direita. Em outras palavras, o valor limite mais alto será incluído dentro de uma partição.
Um intervalo RIGHT especifica que o valor do limite pertence ao lado direito do intervalo de valores do limite quando os valores de intervalo são classificados pelo mecanismo de banco de dados em ordem crescente da esquerda para a direita. Em outras palavras, o menor valor limite será incluído em cada partição.

Se LEFT ou RIGHT não estiver especificado, o intervalo LEFT será definido como padrão.

Por exemplo, a função de partição a seguir divide uma tabela ou índice em 12 partições, uma para cada mês de valores armazenados ao longo de um ano em uma coluna datetime. Um intervalo RIGHT é usado, indicando que os valores de fronteira servirão como valores de limite 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 de tipo RIGHT mantém o primeiro dia do mês na mesma partição que os dias mais tarde nesse mês. Isso ajuda na eliminação exata de partições ao consultar 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 índice que usa essa função de partição no particionamento da coluna datecol seria particionado. 1 de fevereiro é o primeiro ponto de fronteira definido na função, por isso atua como o limite inferior da partição 2.

Partition	1	2	...	11	12
Values	datecol<`2022-02-01 12:00AM`	datecol>= `2022-02-01 12:00AM` E datacol<`2022-03-01 12:00AM`		datecol>= `2022-11-01 12:00AM` E 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.

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 exemplos de sintaxe para criar esquemas de partição em CREATE PARTITION SCHEME.

Filegroups

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 ocorre porque você pode executar backups em grupos de arquivos individuais. Ao usar o armazenamento hierárquico, 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 barato. Todos os outros benefícios de particionamento se aplicam independentemente do número de grupos de arquivos usados ou do posicionamento da partição em grupos de arquivos específicos.

O gerenciamento de arquivos e grupos de arquivos para tabelas particionadas pode adicionar complexidade significativa à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, recomenda-se um único grupo de arquivos para todas as partições. As mesmas regras para projetar arquivos e grupos de arquivos se aplicam a objetos particionados que se aplicam a objetos não particionados.

Encontre um código de exemplo para criar grupos de arquivos para SQL Server e Instância Gerenciada 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 o índice. As seguintes considerações se aplicam ao selecionar uma coluna de particionamento:

As 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.
As colunas de todos os tipos de dados válidos para serem usadas como chave de índice podem ser usadas como coluna de particionamento, exceto timestamp.
Colunas de tipos de dados de objeto grande (LOB), como ntext, text, image, xml, varchar(max), nvarchar(max) e varbinary(max), não podem ser especificadas.
O tipo de dados definido pelo utilizador e as colunas de tipo de dados de alias do tempo de execução de linguagem comum (CLR) do Microsoft .NET Framework não podem ser especificadas.

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

Ao criar um índice não clusterizado, se partition_scheme_name ou grupo de arquivos não for especificado e a tabela for particionada, o índice será colocado no mesmo esquema de partição, usando a mesma coluna de particionamento, que a tabela subjacente. Para alterar a forma 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 é construído no mesmo esquema de partição que sua tabela correspondente. Quando uma tabela e seus índices estão alinhados, o mecanismo de banco de dados pode alternar partições para dentro ou para fora da tabela de forma rápida e eficiente, mantendo a estrutura de partição da tabela e 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, na medida em que:

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

Particionamento de índices clusterizados

Ao particionar um índice clusterizado, a chave de clusterização deve conter a coluna de particionamento. Ao particionar um índice clusterizado não exclusivo e a coluna de particionamento não é especificada explicitamente na chave de clustering, o mecanismo de banco de dados adiciona a coluna de particionamento por padrão à lista de chaves de índice clusterizadas. Se o índice clusterizado for exclusivo, você deverá especificar explicitamente que a chave de índice clusterizado contém a coluna de particionamento. Para obter mais informações sobre índices clusterizados e arquitetura de índice, consulte Diretrizes de design de índice clusterizado.

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

Índice não alinhado

Um índice não alinhado é particionado de forma diferente da tabela correspondente. Ou seja, o índice tem um esquema de partição diferente que o coloca em um grupo de arquivos separado ou conjunto de grupos de arquivos 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 colocalizadas com tabelas adicionais usando colunas de junção diferentes.

Eliminação de partições

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.

Limitations

Antes do SQL Server 2016 (13.x) SP1, tabelas e índices particionados não estavam disponíveis em todas as edições do SQL Server. Para obter uma lista de recursos suportados pelas edições do SQL Server, consulte Edições e recursos com suporte do SQL Server 2022.
Tabelas e índices particionados estão disponíveis em todas as camadas de serviço do Banco de Dados SQL do Azure, do Banco de Dados SQL na Malha e da Instância Gerenciada SQL do Azure.
- No Banco de Dados SQL do Azure e no Banco de Dados SQL na Malha, todas as partições devem ser colocadas no PRIMARY grupo de arquivos porque apenas o PRIMARY grupo de arquivos é fornecido.
O particionamento de tabela está disponível em pools SQL dedicados no Azure Synapse Analytics, com algumas diferenças de sintaxe. Saiba mais em Particionamento de tabelas no pool SQL dedicado.
O escopo de uma função e esquema de partição é limitado ao banco de dados no qual eles foram criados. Dentro do banco de dados, as funções de partição residem em um namespace separado de 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 limite e RANGE RIGHT for especificado na definição da função de partição, a partição mais à esquerda permanecerá vazia e NULLs será colocada na segunda partição.
O mecanismo de banco de dados suporta até 15.000 partições por padrão. Em versões anteriores ao SQL Server 2012 (11.x), o número de partições era limitado a 1.000 por padrão.

Diretrizes de desempenho

O mecanismo de banco de dados suporta até 15.000 partições por tabela ou índice. No entanto, o uso de mais de 1.000 partições tem implicações na memória, operações de índice particionado, comandos DBCC e consultas. Esta seção descreve as implicações de desempenho do uso de mais de 1.000 partições e fornece soluções alternativas, conforme necessário.

Com até 15.000 partições permitidas por tabela ou índice particionado, você pode armazenar dados por longos períodos em uma única tabela. No entanto, você deve reter os dados apenas pelo tempo necessário e manter um equilíbrio entre o desempenho e o número de partições.

Uso de memória e diretrizes

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, as instruções DML (Data Manipulation Language), DDL (Data Definition Language) e outras operações podem falhar devido a memória insuficiente. Sistemas com 16 GB de RAM que executam muitos processos que consomem muita 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 mecanismo de banco de dados de criar um índice particionado. Este é 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.

No SQL Server e na Instância Gerenciada SQL do Azure, você pode aumentar a Opção de Configuração do index create memory (KB) Servidor. Para obter mais informações, consulte Configuração do servidor: memória de criação de índice.

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

Criar e reconstruir índices não alinhados em uma tabela com mais de 1.000 partições é possível, mas não é suportado. Isso pode causar desempenho degradado ou consumo excessivo de memória durante essas operações.

Criar e reconstruir índices alinhados pode levar mais tempo para ser executado à medida que o número de partições aumenta. Recomendamos que você não execute vários comandos de criação e reconstrução de índice ao mesmo tempo, pois pode ter problemas de desempenho e memória.

Quando o mecanismo de banco de dados executa a classificação para criar índices particionados, ele primeiro cria uma tabela de classificação para cada partição. Em seguida, ele cria as tabelas de classificação no respetivo 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 ser construída. Quando você cria 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 classificaçã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 necessária. O tamanho mínimo para cada tabela de classificação, para cada partição, é de 40 páginas, com 8 kilobytes por página. Por exemplo, um índice particionado não alinhado com 100 partições requer memória suficiente para classificar em série 4.000 (40 * 100) páginas ao mesmo tempo. Se essa memória estiver disponível, a operação de compilação será bem-sucedida, mas o desempenho poderá ser prejudicado. Se essa memória não estiver disponível, a operação de compilação falhará. Como alternativa, um índice particionado alinhado com 100 partições requer apenas memória suficiente para classificar 40 páginas, porque as classificações não são executadas ao mesmo tempo.

Para índices alinhados e não alinhados, o requisito de memória pode ser maior se o mecanismo de banco de dados estiver usando paralelismo de consulta para a operação de compilação em um computador multiprocessador. Isso ocorre porque quanto maior o grau de paralelismo (DOP), maior a necessidade de memória. Por exemplo, se o mecanismo de banco de dados definir DOP 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, ou 16.000 páginas. Se o índice particionado estiver alinhado, o requisito de memória será reduzido para quatro processadores classificando 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, comandos DBCC como DBCC CHECKDB e DBCC CHECKTABLE podem levar mais tempo para serem executados à medida que o número de partições aumenta.

Queries

Depois de particionar uma tabela ou índice, as consultas que usam eliminação de partição podem ter desempenho comparável ou melhorado com um número maior de partições. As consultas que não usam eliminação de partições 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 1.000 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 tenha uma WHERE cláusula de filtragem na coluna A executará a eliminação da partição e fará a leitura de 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 tenha uma WHERE cláusula de filtragem na coluna B verificará 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.

As consultas que usam operadores como TOP ou MAX/MIN em colunas diferentes da coluna de particionamento podem ter um desempenho reduzido com o particionamento porque todas as partições devem ser avaliadas.

Da mesma forma, uma consulta que executa uma busca de linha única ou uma verificação 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 precisará executar tantas buscas ou verificações quantas forem as partições. Por esse motivo, o particionamento raramente melhora o desempenho em sistemas OLTP onde essas consultas são comuns.

Se você executar consultas com freqüência que envolvam uma equijunção entre duas ou mais tabelas particionadas, suas colunas de particionamento devem 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 diferentes funções de partição que são essencialmente as mesmas, na medida em que:

Têm o mesmo número de parâmetros que são usados para particionamento, e os parâmetros correspondentes são os mesmos tipos de dados.
Defina o mesmo número de partições.
Defina os mesmos valores de limite para partições.

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

Você pode achar útil usá-lo $PARTITION em algumas consultas. Saiba mais em $PARTITION.

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

Alterações de comportamento no cálculo de estatísticas durante operações de índice particionado

No Banco de Dados SQL do Azure, no Banco de Dados SQL no Fabric, na Instância Gerenciada SQL do Azure e no SQL Server 2012 (11.x) e versões posteriores, as estatísticas não são criadas examinando todas as linhas da tabela quando um índice particionado é criado ou reconstruído. Em vez disso, o otimizador de consulta utiliza o algoritmo de amostragem padrão para gerar as 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 afetar o desempenho das consultas. Para obter estatísticas sobre índices particionados examinando todas as linhas da tabela, use CREATE STATISTICS ou UPDATE STATISTICS com a cláusula FULLSCAN.

Feedback

Esta página foi útil?

Last updated on 2025-07-29