Tabelas e índices particionados

Aplica-se a:SQL Server Azure SQL Database Azure SQL Managed Instance Base de dados SQL no Microsoft Fabric

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.
Podes melhorar a concorrência da carga de trabalho ao ativar a escalonação de bloqueios ao nível da partição em vez de ao nível da tabela. Isso pode reduzir a contenção de bloqueio na tabela. Para reduzir a contenção de bloqueios, permitindo a escalada do bloqueio para a partição, defina a opção LOCK_ESCALATION da declaração ALTER 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 de partição define o número de partições e os limites das partições que a tabela tem. 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 (ou LEFT ou RIGHT) especifica como os valores de fronteira da função de partição são colocados nas partições resultantes.

Um LEFT intervalo especifica que o valor de fronteira pertence ao lado esquerdo do intervalo de valores de fronteira quando os valores de intervalo são ordenados pelo motor da base de dados por ordem crescente da esquerda para a direita. Por outras palavras, o valor limite mais alto está incluído numa partição.
Uma RIGHT faixa especifica que o valor limite pertence ao lado direito do intervalo de valores limite quando os valores do intervalo são ordenados pelo motor da base de dados por ordem crescente, da esquerda para a direita. Ou seja, o valor de limite mais baixo está incluído numa partição.

Se LEFT ou RIGHT não for especificado, o tipo de intervalo LEFT é o 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 tipo de intervalo é usado, indicando que os valores-limite RIGHT servem como valores de limite mínimo em cada partição. RIGHT Os intervalos são frequentemente mais simples de trabalhar ao particionar uma tabela com base numa coluna de tipos de dados datetime, datetime2 ou datetimeoffset , pois as linhas com valor de meia-noite são armazenadas na mesma partição que linhas com os valores posteriores no mesmo dia. De forma semelhante, se usar o tipo de dado data e partições de um mês ou mais, um RIGHT intervalo mantém o primeiro dia do mês na mesma partição que os dias posteriores desse 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 seguinte mostra como uma tabela ou índice que utiliza esta função de partição na coluna de partição datecol é particionado. 1 de fevereiro é o primeiro ponto de fronteira definido na função. Como é utilizado um RIGHT tipo de intervalo, 1 de fevereiro é 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 tanto RANGE LEFT quanto RANGE RIGHT, a partição mais à esquerda possui o valor mínimo do tipo de dados como limite inferior, e a partição mais à direita possui o valor máximo do tipo de dados como limite superior.

Para mais exemplos de funções de partição que utilizam os LEFT tipos de intervalo e (e RIGHT tipos de intervalo), veja CRIAR FUNÇÃO DE PARTIÇÃO.

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

Existem duas razões para usar um esquema de partição com múltiplos grupos de ficheiros:

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.
Podes fazer backup e restaurar cada grupo de ficheiros de forma independente. Isto significa que podes saltar cópias de segurança repetidas de partições que não mudam, ou encurtar o tempo de restauro quando apenas os dados de algumas partições precisam de ser restaurados.

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 seus procedimentos de backup e restauro não beneficiarem do uso de múltiplos grupos de ficheiros, e se não usar armazenamento em níveis, recomenda-se um único grupo de ficheiros 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.

Para mais informações sobre a criação de grupos de ficheiros no SQL Server e Azure SQL Managed Instance, consulte ALTER DATABASE (Transact-SQL) Opções de Ficheiros e Grupos de Ficheiros.

Coluna de particionamento

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

Colunas calculadas que participam numa função de partição devem ser explicitamente criadas como PERSISTED.
- Como apenas uma coluna pode ser usada como coluna de partição, em alguns casos a concatenação de múltiplas colunas numa coluna calculada 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 objetos grandes (LOB), como ntext, text, image, xml, varchar(max), nvarchar(max) e varbinary(max), não podem ser especificadas.
Colunas que usam os tipos de dados CLR definidos pelo utilizador e os tipos de dados alias não podem ser especificadas.

Para particionar uma tabela ou um índice, especifique o esquema de partição e a coluna de partição nas instruções CREATE TABLE, ALTER TABLE e CREATE INDEX .

Ao criar um índice não agrupado, se um esquema de partição ou grupo de ficheiros não for especificado e a tabela for particionada, o índice é colocado no mesmo esquema de partições, usando a mesma coluna de partição, que a tabela subjacente. Para alterar a forma como um índice existente é particionado, use CREATE INDEX com a DROP_EXISTING cláusula. 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 construído sobre o mesmo esquema de partição da sua tabela correspondente é chamado de índice alinhado. Quando uma tabela e os seus índices não agrupados estão alinhados, o motor da base de dados pode alternar partições para dentro ou fora da tabela de forma rápida e eficiente, mantendo a estrutura de partições tanto da tabela como dos seus índices. Um índice não precisa de participar na mesma função de partição para estar alinhado com a 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. Quando particionas um índice clusterizado não único e a coluna de partição não está explicitamente especificada na chave de clustering, o motor da base de dados adiciona a coluna de partição por defeito à lista de chaves de índice agrupadas. Se o índice agrupado for único, tem de adicionar explicitamente a coluna de partição à chave do índice agrupado. 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 usa uma função de partição com uma definição diferente dos limites das partições ou usa uma coluna de partição diferente. Criar um índice particionado não alinhado pode ser útil nos seguintes casos:

A tabela base não foi particionada.
A chave de índice é única, não contém a coluna de partição da tabela e a unicidade do índice deve ser preservada.
Queres usar junções colocadas entre uma tabela e várias outras tabelas que estejam particionadas de forma diferente.

Eliminação de partições

Quando o predicado da consulta faz referência à coluna de partição, o motor da base de dados pode ser capaz de eliminar, ou saltar, algumas partições ao ler uma tabela ou índice particionado. Isto pode melhorar o desempenho das consultas.

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. Funções de partição e esquemas de partição não pertencem a um esquema.
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 fronteira e RANGE RIGHT for especificado na definição da função de partição, então a partição mais à esquerda permanece vazia, e os NULLs são colocados na segunda partição.
O motor de base de dados suporta até 15.000 partições. 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, a utilização de um grande número de partições tem implicações na memória, nas operações de índice particionado, nos comandos DBCC, na modificação do esquema e no desempenho das consultas. Esta secção descreve as implicações de desempenho de designs que envolvem um grande número de partições e fornece soluções alternativas conforme necessário.

Advertência

Se o seu design utiliza centenas ou milhares de partições por tabela ou índice, certifique-se de que compreende as implicações de desempenho, testa e valida cenários críticos de utilização e tem um plano para resolver qualquer impacto no desempenho.

Evite projetos com o número de partições em centenas ou milhares, a menos que seja estritamente necessário.

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. Isto é especialmente verdade quando o índice não está alinhado com a sua tabela base ou não está alinhado com o seu índice agrupado.

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 a Azure SQL Database, considere aumentar temporária ou permanentemente o tamanho de computação da base de dados para obter mais memória.

Operações de índice particionado

Criar e reconstruir índices não alinhados numa tabela com mais de 1.000 partições pode ser 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. Depois, constrói as tabelas de ordenação quer no respetivo grupo de ficheiros de cada partição, quer 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 esta memória estiver disponível, a operação de compilação tem sucesso, mas o desempenho pode sofrer. Se essa memória não estiver disponível, a operação de compilação falha. 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 motor da base de dados estiver a usar paralelismo de consulta na operação de construção do índice. Quanto maior o grau de paralelismo (DOP), maior o requisito 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. Pode usar a opção do índice MAXDOP para reduzir o grau de paralelismo como solução alternativa, à custa de um tempo de construção do índice potencialmente mais longo.

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

Após a partição de uma tabela ou índice, consultas que utilizam eliminação de partições podem ter desempenho comparável ou melhorado. 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 tem 100 milhões de linhas e colunas A e B.

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 filtragem de cláusulas numa coluna A irá realizar a eliminação de partições e varrer um subconjunto de todas as partições. 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.

Consultas que utilizam TOP, MAX, ou MIN em colunas que não sejam a coluna de partição podem apresentar desempenho reduzido com a partição porque todas as partições têm de ser avaliadas.

De forma semelhante, uma consulta que realiza uma busca de uma linha ou uma varredura de pequeno intervalo demora mais tempo contra uma tabela particionada do que contra uma tabela não particionada se o predicado da consulta não incluir a coluna de partição, porque terá de realizar tantas buscas ou varrimentos quantas partições existam. 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 consultas pode processar a junção mais rapidamente, porque processa dados de pares de partições colocadas. 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. Para mais informações, consulte $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.

Cálculo estatístico durante operações de índice particionado

Quando um índice não particionado é criado ou reconstruído, o motor da base de dados também cria estatísticas no índice ao analisar todas as linhas do índice. No entanto, quando um índice particionado é criado ou reconstruído, as estatísticas são criadas usando o algoritmo de amostragem padrão.

Para criar ou atualizar estatísticas em índices particionados, examinando uma amostra maior ou todas as linhas da tabela, use CREATE STATISTICS ou UPDATE STATISTICS com as cláusulas SAMPLE ou FULLSCAN.

Feedback

Esta página foi útil?

Last updated on 2025-12-30