Compartilhar via

Tabelas e índices particionados

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de dados SQL no Microsoft Fabric

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.

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 retirar partições individuais de uma tabela e movê-las para uma tabela de arquivo.

  • Você pode melhorar o desempenho da consulta, 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. Para obter mais informações, consulte a seção em Consultas.

  • Você pode melhorar a simultaneidade da carga de trabalho habilitando o escalonamento de bloqueio no nível da partição em vez de no nível da tabela. Isso pode reduzir a contenção de bloqueio na tabela. Para reduzir a contenção de bloqueio permitindo que o bloqueio escale para a partição, defina a 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 tem. Por exemplo, considerando uma tabela que contém dados de pedidos de vendas, você pode querer particionar a tabela em 12 partições (mensais) com base em uma coluna datetime, como uma data de vendas.

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

  • Um LEFT intervalo especifica que o valor de limite pertence ao lado esquerdo do intervalo de valor de 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 valor limite mais alto está incluído em uma partição.
  • Um RIGHT intervalo especifica que o valor de limite pertence ao lado direito do intervalo de valor de 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 valor de delimitação inferior é incluído em uma partição.

Se LEFT ou RIGHT não for especificado, o LEFT tipo de intervalo 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 RIGHT tipo de intervalo é usado, indicando que os valores de limite servem como os valores delimitadores mais baixos em cada partição. RIGHT os intervalos geralmente são mais simples de trabalhar ao particionar uma tabela com base em uma coluna de tipos de dados datetime, datetime2 ou datetimeoffset , pois as linhas com um valor de meia-noite são armazenadas na mesma partição que as linhas com os valores posteriores no mesmo dia. Da mesma forma, se estiver usando o tipo de data e usando partições de um mês ou mais, um RIGHT intervalo manterá o primeiro dia do mês na mesma partição que os dias subsequentes daquele 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 índice que usa essa função de partição na coluna de particionamento datecol é particionada. 1º de fevereiro é o primeiro ponto de limite definido na função. Como um RIGHT tipo de intervalo é usado, 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 AND datecol<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 ambos 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.

Para obter mais exemplos de funções de partição usando os tipos de intervalo LEFT e RIGHT, consulte 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.

Localize a sintaxe de exemplo para criar esquemas de partição no CREATE PARTITION SCHEME.

Filegroups

Há dois motivos para usar um esquema de partição com vários grupos de arquivos:

  • 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.
  • Você pode fazer backup e restaurar cada grupo de arquivos de forma independente. Isso significa que você pode ignorar backups repetidos de partições que não estão sendo alteradas ou reduzir o tempo de restauração quando apenas os dados em algumas partições precisarem ser restaurados.

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 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 e se você não usar o armazenamento em camadas, um único 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, conforme aplicável a objetos não particionados.

Para obter mais informações sobre como criar grupos de arquivos no SQL Server e na Instância Gerenciada de SQL do Azure, consulte opções de arquivo e grupo de arquivos ALTER DATABASE (Transact-SQL).

Coluna de particionamento

A coluna de uma tabela ou índice que é a entrada para uma função de partição. 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 a coluna de particionamento, em alguns casos, a concatenação de várias colunas em 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 de tipos de dados lob (objeto grande), como ntext, texto, imagem, xml, varchar(max), nvarchar(max)e varbinary(max) não podem ser especificadas.
  • Colunas que usam os tipos de dados definidos pelo usuário CLR e tipos de dados de alias não podem ser especificadas.

Para particionar uma tabela ou um índice, 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 um esquema de partição ou um 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, como a tabela subjacente. Para alterar como um índice existente é particionado, use CREATE INDEX com a DROP_EXISTING cláusula. Isso permite particionar um índice não particionado, fazer um índice particionado não particionado ou alterar o esquema de partição do índice.

Índice alinhado

Um índice criado no mesmo esquema de partição que sua tabela correspondente é chamado de índice alinhado. Quando uma tabela e seus índices não clusterizados estão alinhados, o mecanismo de banco de dados pode alternar partições dentro ou fora da tabela 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 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 você particiona 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 clusterizado. Se o índice clusterizado for exclusivo, você deverá adicionar explicitamente a coluna de particionamento à chave de índice clusterizado. 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 forma diferente da tabela correspondente. Ou seja, o índice usa uma função de partição com uma definição diferente de limites de partição ou usa uma coluna de particionamento diferente. 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, não contém a coluna de particionamento da tabela e a exclusividade do índice deve ser preservada.
  • Você deseja usar junções agrupadas entre uma tabela e várias outras tabelas particionadas de forma diferente.

Eliminação de partição

Quando o predicado de consulta faz referência à coluna de particionamento, o mecanismo de banco de dados pode ser capaz de eliminar ou ignorar algumas partições durante a leitura de uma tabela ou índice particionado. Isso pode melhorar o desempenho 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, as tabelas e os índices particionados não estavam disponíveis em todas as edições do SQL Server. Para obter uma lista de recursos que têm suporte nas edições do SQL Server, veja Edições e recursos com suporte no 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, banco de dados SQL no Fabric e Instância Gerenciada de SQL do Azure.

    • No Banco de Dados SQL do Azure e no Banco de Dados SQL no Fabric, todas as partições devem ser colocadas no PRIMARY grupo de arquivos porque somente o PRIMARY grupo de arquivos é fornecido.

  • O particionamento de tabela 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.

  • 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. 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 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ão colocados na segunda partição.

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

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 um grande número de partições tem implicações na memória, operações de índice particionadas, comandos DBCC, modificação de esquema e desempenho de consulta. Esta seção descreve as implicações de desempenho de designs que envolvem um grande número de partições e fornecem soluções alternativas conforme necessário.

Aviso

Se o design usar muitas centenas ou milhares de partições por tabela ou índice, verifique se você entende as implicações de desempenho, teste e valide cenários críticos de uso e tenha um plano para resolver qualquer impacto no desempenho.

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

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 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 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. Esse é especialmente o caso quando o índice não está alinhado com sua tabela base ou não está alinhado com seu í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, consulte configuração do servidor: criação de memória do índice.

Para o Banco de Dados SQL do Azure, considere aumentar temporaria ou permanentemente o tamanho da computação do banco de dados para obter mais memória.

Operações de índice particionado

A criação e a recriação de índices desalinhados em uma tabela com mais de 1.000 partições podem ser possíveis, mas não é suportado. Fazer isso pode provocar redução de 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. 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 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. Em seguida, ele compila as tabelas de classificaçã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 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 build será bem-sucedida, 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 serialmente 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 poderá ser maior se o mecanismo de banco de dados estiver usando paralelismo de consulta na operação de construção do índice. Quanto maior o grau de paralelismo (DOP), 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 o grau de paralelismo como uma solução alternativa, ao custo de um tempo de construção de índice potencialmente mais longo.

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.

Queries

Depois de particionar uma tabela ou índice, as consultas que usam a eliminação de partição podem ter um desempenho comparável ou aprimorado. 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, suponha que uma tabela tenha 100 milhões de linhas e colunas A e B.

  • 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 que filtra a coluna A executará a eliminação de partição e examinará 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 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 para verificar.

As consultas que usam TOP, MAXou MIN em colunas diferentes da coluna de particionamento podem ter um desempenho reduzido com particionamento, pois todas as partições devem ser avaliadas.

Da mesma forma, uma consulta que executa uma busca de linha única ou uma pequena verificação de intervalo leva mais tempo em relação a uma tabela particionada do que em uma tabela não particionada se o predicado de consulta não incluir a coluna de particionamento, pois ela precisará executar quantas buscas ou verificações 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.

Dessa forma, o otimizador de consulta pode processar a junção mais rapidamente, pois a junção processa dados de pares de partições agrupadas. Se uma consulta unir duas tabelas que não são agrupadas ou não são particionadas no campo de junção, a presença de partições poderá, na verdade, diminuir o processamento de consulta em vez de acelerá-la.

Você pode achar útil usar $PARTITION em algumas consultas. Para obter mais informações, consulte $PARTITION.

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

Computação de estatísticas durante operações de índice particionado

Quando um índice não particionado é criado ou recriado, o mecanismo de banco de dados também cria estatísticas no índice verificando todas as linhas no índice. No entanto, quando um índice particionado é criado ou recriado, as estatísticas são criadas usando o algoritmo de amostragem padrão.

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

Conteúdo relacionado

  • Last updated on