Partitioned Tables and IndexesPartitioned Tables and Indexes

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoSQL Data Warehouse do Azure nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

OSQL ServerSQL Server oferece suporte ao particionamento de tabelas e índices.SQL ServerSQL Server supports table and index partitioning. Os dados de tabelas e índices particionados são divididos em unidades que podem ser difundidas por mais de um grupo de arquivos em um banco de dados.The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. Os dados são particionados horizontalmente, de forma que os grupos de linhas são mapeados em partições individuais.The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. Todas as partições de um único índice ou de uma única tabela devem residir no mesmo banco de dados.All partitions of a single index or table must reside in the same database. A tabela ou o índice é tratado como uma única entidade lógica quando são executadas consultas ou atualizações nos dados.The table or index is treated as a single logical entity when queries or updates are performed on the data. Antes do SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, as tabelas e os índices particionados não estavam disponíveis em todas as edições do SQL ServerSQL Server.Prior to SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, partitioned tables and indexes were not available in every edition of SQL ServerSQL Server. Para obter uma lista de recursos com suporte nas edições do SQL ServerSQL Server, consulte Edições e recursos com suporte no SQL Server 2016.For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

Importante

OSQL Server 2017SQL Server 2017 oferece suporte a até 15.000 partições por padrão.SQL Server 2017SQL Server 2017 supports up to 15,000 partitions by default. Em versões anteriores à SQL Server 2012 (11.x)SQL Server 2012 (11.x), o número de partições era limitado por padrão a 1.000.In versions earlier than SQL Server 2012 (11.x)SQL Server 2012 (11.x), the number of partitions was limited to 1,000 by default. Em sistemas baseados em x86, é possível criar uma tabela ou índice com mais de 1.000 partições, mas isso não tem suporte.On x86-based systems, creating a table or index with more than 1,000 partitions is possible, but is not supported.

Benefícios do particionamentoBenefits of Partitioning

O particionamento de tabelas ou índices grandes pode ter a capacidade de gerenciamento e os benefícios de desempenho a seguir.Partitioning large tables or indexes can have the following manageability and performance benefits.

  • 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.You can transfer or access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. 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.For example, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes when the data is not partitioned.

  • Você pode executar operações de manutenção mais rapidamente em uma ou mais partições.You can perform maintenance operations on one or more partitions more quickly. As operações são mais eficientes porque elas visam apenas estes subconjuntos de dados, e não a tabela inteira.The operations are more efficient because they target only these data subsets, instead of the whole table. Por exemplo, você pode optar por compactar dados em uma ou mais partições ou recriar uma ou mais partições de um índice.For example, you can choose to compress data in one or more partitions or rebuild one or more partitions of an index.

  • Você pode aprimorar o desempenho de consultas com base nos tipos de consultas executadas com frequência e em sua configuração de hardware.You may improve query performance, based on the types of queries you frequently run and on your hardware configuration. 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.For example, the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns are the same as the columns on which the tables are joined. Confira Consultas abaixo para obter mais informações.See Queries below for further information.

Quando o SQL ServerSQL Server executa classificação de dados para operações de E/S, ele classifica os dados primeiro pela partição.When SQL ServerSQL Server performs data sorting for I/O operations, it sorts the data first by partition. OSQL ServerSQL Server acessa uma unidade de cada vez e isso pode reduzir o desempenho.SQL ServerSQL Server accesses one drive at a time, and this might reduce performance. Para melhorar o desempenho da classificação de dados, distribua os arquivos de dados de suas partições em mais de um disco configurando um RAID.To improve data sorting performance, stripe the data files of your partitions across more than one disk by setting up a RAID. Dessa maneira, embora o SQL ServerSQL Server ainda classifique os dados por partição, ele pode acessar todas as unidades de cada partição ao mesmo tempo.In this way, although SQL ServerSQL Server still sorts data by partition, it can access all the drives of each partition at the same time.

Além disso, você pode melhorar o desempenho habilitando o escalonamento de bloqueios em nível de partição em, e não em uma tabela inteira.In addition, you can improve performance by enabling lock escalation at the partition level instead of a whole table. Isso pode reduzir a contenção de bloqueio na tabela.This can reduce lock contention on the table. 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.To reduce lock contention by allowing lock escalation to the partition, set the LOCK_ESCALATION option of the ALTER TABLE statement to AUTO.

Componentes e conceitosComponents and Concepts

As condições a seguir são aplicáveis ao particionamento de tabela e de índice.The following terms are applicable to table and index partitioning.

Função de partiçãoPartition function

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.A database object that defines how the rows of a table or index are mapped to a set of partitions based on the values of certain column, called a partitioning column. Ou seja, a função de partição define o número de partições que a tabela terá e como serão definidos os limites das partições.That is, the partition function defines the number of partitions that the table will have and how the boundaries of the partitions are defined. Por exemplo, considerando uma tabela que contém dados de ordem de venda, você pode desejar particionar a tabela em doze (mensalmente) partições com base em uma coluna datetime como uma data de vendas.For example, given a table that contains sales order data, you may want to partition the table into twelve (monthly) partitions based on a datetime column such as a sales date.

Esquema de partiçãoPartition scheme

Um objeto de banco de dados que mapeia as partições de uma função de partição para um conjunto de grupos de arquivos.A database object that maps the partitions of a partition function to a set of filegroups. O principal motivo para colocar suas partições em grupos de arquivos separados é para garantir que poderá efetuar operações de backup em partições de forma independente.The primary reason for placing your partitions on separate filegroups is to make sure that you can independently perform backup operations on partitions. Isso porque se pode executar backups em grupos de arquivos individuais.This is because you can perform backups on individual filegroups.

Coluna de particionamentoPartitioning column

A coluna de uma tabela ou índice que uma função de partição usa para particionar a tabela ou índice.The column of a table or index that a partition function uses to partition the table or index. Colunas computadas que participam de uma função de partição devem ser marcadas explicitamente como PERSISTED.Computed columns that participate in a partition function must be explicitly marked PERSISTED. Todos os tipos de dados que são válidos para uso como colunas de índice podem ser usados como uma coluna de particionamento, exceto timestamp.All data types that are valid for use as index columns can be used as a partitioning column, except timestamp. Os tipos de dados ntext, text, image, xml, varchar(max) , nvarchar(max) ou varbinary(max) não podem ser especificados.The ntext, text, image, xml, varchar(max), nvarchar(max), or varbinary(max) data types cannot be specified. Também, 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.Also, Microsoft .NET Framework common language runtime (CLR) user-defined type and alias data type columns cannot be specified.

Índice alinhadoAligned index

Um índice que é baseado no mesmo esquema de partição que sua tabela correspondente.An index that is built on the same partition scheme as its corresponding table. Quando uma tabela e seus índices estão em alinhamento, o SQL ServerSQL Server pode trocar as partições rápida e eficientemente enquanto, mantém a estrutura de partição em ambos, tabela e seus índices.When a table and its indexes are in alignment, SQL ServerSQL Server can switch partitions quickly and efficiently while maintaining the partition structure of both the table and its indexes. Um índice não precisa participar na mesma função de partição nomeada para ser alinhado com sua tabela base.An index does not have to participate in the same named partition function to be aligned with its base table. No entanto, a função de partição do índice e a tabela base devem ser essencialmente o mesmo, no sentido em que:However, the partition function of the index and the base table must be essentially the same, in that:

  1. os argumentos das funções de partição têm o mesmo tipo de dados.The arguments of the partition functions have the same data type.
  2. elas definem o mesmo número de partições.They define the same number of partitions.
  3. elas definem os mesmos valores de limite para partições.They define the same boundary values for partitions.

Particionando índices clusterizadosPartitioning Clustered Indexes

Ao particionar um índice clusterizado, a chave de clustering deve conter a coluna de particionamento.When partitioning a clustered index, the clustering key must contain the partitioning column. Quando particiona um índice clusterizado não exclusivo e a coluna de particionamento não está explicitamente especificada na chave de clustering, o SQL ServerSQL Server adiciona a coluna de particionamento, por padrão, à lista de chaves de índices clusterizados.When partitioning a nonunique clustered index, and the partitioning column is not explicitly specified in the clustering key, SQL ServerSQL Server adds the partitioning column by default to the list of clustered index keys. Se o índice clusterizado for exclusivo, você deve especificar explicitamente que a chave de índice clusterizado contém a coluna de particionamento.If the clustered index is unique, you must explicitly specify that the clustered index key contain the partitioning column.

Particionando índices não clusterizadosPartitioning NonClustered Indexes

Ao particionar um índice não clusterizado exclusivo, a chave de índice deve conter a coluna de particionamento.When partitioning a unique nonclustered index, the index key must contain the partitioning column. Ao particionar um índice não clusterizado e não exclusivo, o SQL ServerSQL Server adiciona a coluna de particionamento, por padrão, como a coluna do índice não chave (incluída) para garantir que o índice estará alinhado com a tabela base.When partitioning a nonunique, nonclustered index, SQL ServerSQL Server adds the partitioning column by default as a nonkey (included) column of the index to make sure the index is aligned with the base table. O SQL ServerSQL Server não adicionará a coluna de particionamento ao índice se este já estiver presente no índice.SQL ServerSQL Server does not add the partitioning column to the index if it is already present in the index.

Índice não alinhadoNon-aligned index

Um índice particionado independentemente de sua tabela correspondente.An index partitioned independently from its corresponding table. Quer dizer, o índice tem um esquema de partição diferente ou é colocado em um grupo de arquivos separado da tabela base.That is, the index has a different partition scheme or is placed on a separate filegroup from the base table. A criação de um índice particionado não alinhado pode ser útil nos seguintes casos:Designing an non-aligned partitioned index can be useful in the following cases:

  • A tabela base não foi particionada.The base table has not been partitioned.
  • A chave de índice é exclusiva e não contém a coluna de particionamento da tabela.The index key is unique and it does not contain the partitioning column of the table.
  • Você deseja que a tabela base participe de junções colocadas com mais tabelas que usam colunas de junção diferentes.You want the base table to participate in collocated joins with more tables using different join columns.

Eliminação de partiçãoPartition elimination

O processo pelo qual o otimizador de consulta acessa apenas as partições relevantes para satisfazer os critérios de filtro da consulta.The process by which the query optimizer accesses only the relevant partitions to satisfy the filter criteria of the query.

Diretrizes de desempenhoPerformance Guidelines

O limite novo, superior de 15.000 partições afeta a memória, operações de índice particionadas, comandos DBCC e consultas.The new, higher limit of 15,000 partitions affects memory, partitioned index operations, DBCC commands, and queries. Esta seção descreve as implicações de desempenho de aumentar o número de partições acima de 1.000 e fornece soluções alternativas quando necessário.This section describes the performance implications of increasing the number of partitions above 1,000 and provides workarounds as needed. Com o limite no número máximo de partições elevado para 15.000, você pode armazenar dados por mais tempo.With the limit on the maximum number of partitions being increased to 15,000, you can store data for a longer time. Porém, você só deve reter dados enquanto necessário e manter um equilíbrio entre desempenho e número de partições.However, you should retain data only for as long as it is needed and maintain a balance between performance and number of partitions.

Diretrizes de núcleos de processador e número de partiçõesProcessor Cores and Number of Partitions Guidelines

Para maximizar o desempenho com operações paralelas, é recomendado usar o mesmo número de partições que de núcleos de processador, até no máximo 64 (que é o número máximo de processadores paralelos que o SQL ServerSQL Server pode utilizar).To maximize performance with parallel operations, we recommend that you use the same number of partitions as processor cores, up to a maximum of 64 (which is the maximum number of parallel processors that SQL ServerSQL Server can utilize).

Uso de memória e diretrizesMemory Usage and Guidelines

É recomendável usar pelo menos 16 GB de RAM se um número grande de partições estiver em uso.We recommend that you use at least 16 GB of RAM if a large number of partitions are in use. 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.If the system does not have enough memory, Data Manipulation Language (DML) statements, Data Definition Language (DDL) statements and other operations can fail due to insufficient memory. 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.Systems with 16 GB of RAM that run many memory-intensive processes may run out of memory on operations that run on a large number of partitions. Portanto, quanto mais memória você tiver acima de 16 GB, menor será a probabilidade de encontrar problemas de desempenho e memória.Therefore, the more memory you have over 16 GB, the less likely you are to encounter performance and memory issues.

As limitações de memória podem afetar o desempenho ou habilidade do SQL ServerSQL Server para construir um índice particionado.Memory limitations can affect the performance or ability of SQL ServerSQL Server to build a partitioned index. Este será o caso especialmente quando o índice não está alinhado com sua tabela base ou não está alinhado com o índice clusterizado, se a tabela já tiver um índice clusterizado aplicado a ela.This is especially the case when the index is not aligned with its base table or is not aligned with its clustered index, if the table already has a clustered index applied to it. Nesse caso, pode ser útil aumentar Opção de Configuração do Servidor index create memory.In this case, it may be useful to increase the index create memory Server Configuration Option. Para obter mais informações, consulte Configurar a opção index create memory de configuração de servidor.For more information, refer to Configure the index create memory Server Configuration Option.

Operações de índice particionadoPartitioned Index Operations

É 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.Creating and rebuilding non-aligned indexes on a table with more than 1,000 partitions is possible, but is not supported. Fazer isso pode provocar degradação do desempenho ou consumo excessivo de memória durante essas operações.Doing so may cause degraded performance or excessive memory consumption during these operations.

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.Creating and rebuilding aligned indexes could take longer to execute as the number of partitions increases. É 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.We recommend that you do not run multiple create and rebuild index commands at the same time as you may run into performance and memory issues.

Quando o SQL ServerSQL Server executar uma classificação para construir índices particionados, ele construirá primeiro uma tabela de classificação para cada partição.When SQL ServerSQL Server performs sorting to build partitioned indexes, it first builds one sort table for each partition. 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.It then builds the sort tables either in the respective filegroup of each partition or in tempdb if the SORT_IN_TEMPDB index option is specified. Cada tabela de classificação exige uma quantia mínima de memória para construir.Each sort table requires a minimum amount of memory to build. 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.When you are building a partitioned index that is aligned with its base table, sort tables are built one at a time, using less memory. Porém, quando você estiver construindo um índice particionado não alinhado, as tabelas de classificação serão criadas ao mesmo tempo.However, when you are building a nonaligned partitioned index, the sort tables are built at the same time. Como resultado, deve haver memória suficiente para controlar essas classificações simultâneas.As a result, there must be sufficient memory to handle these concurrent sorts. Quanto maior o número de partições, mais memória será necessária.The larger the number of partitions, the more memory required. O tamanho mínimo para cada tabela de classificação, para cada partição é de 40 páginas, com 8 quilobites por página.The minimum size for each sort table, for each partition, is 40 pages, with 8 kilobytes per page. 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.For example, a nonaligned partitioned index with 100 partitions requires sufficient memory to serially sort 4,000 (40 * 100) pages at the same time. Se essa memória estiver disponível, a operação de construção terá sucesso, mas o desempenho poderá ser afetado.If this memory is available, the build operation will succeed, but performance may suffer. Se essa memória não estiver disponível, a operação de criação falhará.If this memory is not available, the build operation will fail. 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.Alternatively, an aligned partitioned index with 100 partitions requires only sufficient memory to sort 40 pages, because the sorts are not performed at the same time.

Para ambos os índices, alinhados e não alinhados, os requisitos de memória poderão ser maiores se o SQL ServerSQL Server aplicar graus de paralelismo à operação de compilação em um computador multiprocessador.For both aligned and non-aligned indexes, the memory requirement can be greater if SQL ServerSQL Server is applying degrees of parallelism to the build operation on a multiprocessor computer. Isso ocorre porque quanto maior os graus de paralelismo, maior será o requisito de memória.This is because the greater the degrees of parallelism, the greater the memory requirement. Por exemplo, se o SQL ServerSQL Server define os graus de paralelismo como 4, um índice particionado não alinhado com 100 partições requer memória suficiente para quatro processadores para classificar serialmente 4.000 páginas, ao mesmo tempo, ou 16.000 páginas.For example, if SQL ServerSQL Server sets degrees of parallelism to 4, a nonaligned partitioned index with 100 partitions requires sufficient memory for four processors to sort 4,000 pages at the same time, or 16,000 pages. 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.If the partitioned index is aligned, the memory requirement is reduced to four processors sorting 40 pages, or 160 (4 * 40) pages. Você pode usar a opção de índice MAXDOP para reduzir os graus de paralelismo manualmente.You can use the MAXDOP index option to manually reduce the degrees of parallelism.

Comandos DBCCDBCC Commands

Com um número maior de partições, os comandos DBCC podem demorar mais para serem executados à medida que aumentar o número de partições.With a larger number of partitions, DBCC commands could take longer to execute as the number of partitions increases.

ConsultasQueries

Consultas que usam a eliminação de partição podem ter desempenho comparável ou aprimorado com número maior de partições.Queries that use partition elimination could have comparable or improved performance with larger number of partitions. 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.Queries that do not use partition elimination could take longer to execute as the number of partitions increases.

Por exemplo, digamos que uma tabela tem 100 milhões de linhas e colunas A, Be C.For example, assume a table has 100 million rows and columns A, B, and C.

  • No cenário 1, a tabela é dividida em 1.000 partições na coluna A.In scenario 1, the table is divided into 1000 partitions on column A.
  • No cenário 2, a tabela é dividida em 10.000 partições na coluna A.In scenario 2, the table is divided into 10,000 partitions on column 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.A query on the table that has a WHERE clause filtering on column A will perform partition elimination and scan one partition. Essa mesma consulta pode ser executada mais rapidamente no cenário 2, pois há menos linhas a serem examinadas em uma partição.That same query may run faster in scenario 2 as there are fewer rows to scan in a partition. Uma consulta com uma cláusula WHERE filtrada na coluna B examinará todas as partições.A query that has a WHERE clause filtering on column B will scan all partitions. 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.The query may run faster in scenario 1 than in scenario 2 as there are fewer partitions to scan.

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.Queries that use operators such as TOP or MAX/MIN on columns other than the partitioning column may experience reduced performance with partitioning because all partitions must be evaluated.

Se você executar consultas que envolvem uma junção de igualdade (equi-join) 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.If you frequently run queries that involve an equi-join between two or more partitioned tables, their partitioning columns should be the same as the columns on which the tables are joined. Além disso, as tabelas ou seus índices devem ser colocados.Additionally, the tables, or their indexes, should be collocated. Isso significa que eles usam a mesma função de partição nomeada ou usam funções diferentes que são essencialmente as mesmas, porque:This means that they either use the same named partition function, or they use different ones that are essentially the same, in that they:

  • 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.Have the same number of parameters that are used for partitioning, and the corresponding parameters are the same data types.
  • Definem o mesmo número de partições.Define the same number of partitions.
  • Definem os mesmos valores de limite para partições.Define the same boundary values for partitions. Desse modo, o otimizador de consulta pode processar a junção mais rapidamente, porque as próprias partições podem ser unidas.In this way, the query optimizer can process the join faster, because the partitions themselves can be joined. 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.If a query joins two tables that are not collocated or are not partitioned on the join field, the presence of partitions may actually slow down query processing instead of accelerate it.

Alterações de comportamento em computação de estatísticas durante operações de índice particionadasBehavior changes in statistics computation during partitioned index operations

Começando com o SQL Server 2012 (11.x)SQL Server 2012 (11.x), 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.Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), statistics are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. Em vez disso, o otimizador de consultas usa o algoritmo de amostragem padrão para gerar estatísticas.Instead, the query optimizer uses the default sampling algorithm to generate statistics. Depois de atualizar um banco de dados com índices particionados, você pode notar uma diferença nos dados de histograma destes índices.After upgrading a database with partitioned indexes, you may notice a difference in the histogram data for these indexes. Esta alteração no comportamento pode não afetar o desempenho de consulta.This change in behavior may not affect query performance. 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.To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

TarefasTasks TópicoTopic
Descreve como criar funções de partição e esquemas de partição, e depois aplicá-los a uma tabela e índice.Describes how to create partition functions and partition schemes and then apply these to a table and index. Criar tabelas e índices particionadosCreate Partitioned Tables and Indexes

Você pode localizar os livros brancos a seguir em estratégias e implementações úteis de tabelas e índices particionados.You may find the following white papers on partitioned table and index strategies and implementations useful.