Reorganizar e recompilar índicesReorganize and rebuild indexes

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure simAzure Synapse Analytics (SQL DW) simParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Este artigo descreve como reorganizar ou recompilar o índice fragmentado no SQL ServerSQL Server usando o SQL Server Management StudioSQL Server Management Studio ou o Transact-SQLTransact-SQL.This article describes how to reorganize or rebuild a fragmented index in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. O Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine modifica os índices automaticamente sempre que são realizadas operações de entrada, atualização ou exclusão nos dados subjacentes.The Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data. No decorrer do tempo, essas modificações podem fazer com que as informações do índice sejam dispersadas pelo banco de dados (fragmentadas).Over time these modifications can cause the information in the index to become scattered in the database (fragmented). A fragmentação ocorre quando os índices têm páginas nas quais a ordem lógica, com base no valor de chave, não corresponde à ordem física do arquivo de dados.Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Os índices com fragmentação pesada podem degradar o desempenho da consulta e causar lentidão de resposta do aplicativo, especialmente nas operações de exame.Heavily fragmented indexes can degrade query performance and cause your application to respond slowly, especially scan operations.

Você pode solucionar a fragmentação de índice reorganizando ou recriando um índice.You can remedy index fragmentation by reorganizing or rebuilding an index. Para índices particionados criados em um esquema de partição, é possível usar qualquer um desses métodos em um índice completo ou em uma única partição de índice:For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index:

  • Reorganizar um índice usa recursos mínimos do sistema e é uma operação online. Reorganizing an index uses minimal system resources and is an online operation. Isso significa que os bloqueios de tabela de longo prazo não são mantidos e que as consultas ou atualizações da tabela subjacente podem continuar durante a transação ALTER INDEX REORGANIZE.This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.

    • Para índices rowstore, ela desfragmenta o nível folha de índices clusterizados e não clusterizados em tabelas e exibições, reordenando fisicamente as páginas de nível folha para que correspondam à ordem lógica, da esquerda para a direita, dos nós folha.For rowstore indexes, it defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. A reorganização também compacta as páginas de índice.Reorganizing also compacts the index pages. A compactação baseia-se no valor do fator de preenchimento existente.Compaction is based on the existing fill factor value. Para exibir a configuração do fator de preenchimento, use sys.indexes.To view the fill factor setting, use sys.indexes.
    • Ao usar índices columnstore, é possível que, depois de carregar os dados, o armazenamento Delta tenha vários rowgroups pequenos.When using columnstore indexes, it is possible that after loading data the delta store has multiple small rowgroups. A reorganização do índice columnstore força todos os rowgroups no columnstore e, em seguida, combina os rowgroups em menos RowGroups com mais filas.Reorganizing the columnstore index forces all of the rowgroups into the columnstore, and then combines the rowgroups into fewer rowgroups with more rows. A operação de reorganização removerá também as linhas que foram excluídas do columnstore.The reorganize operation will also remove rows that have been deleted from the columnstore. Inicialmente, a reorganização exigirá recursos de CPU adicionais para compactar os dados, o que pode reduzir o desempenho geral do sistema.Reorganizing will initially require additional CPU resources to compress the data, which could slow overall system performance. No entanto, assim que os dados forem compactados, o desempenho de consulta poderá aumentar.However, as soon as the data is compressed, query performance can improve.
  • A recriação de um índice descarta e recria o índice. Rebuilding an index drops and re-creates the index. Dependendo do tipo de índice e a versão Mecanismo de Banco de DadosDatabase Engine, isso pode ser feito online ou offline.Depending on the type of index and Mecanismo de Banco de DadosDatabase Engine version, this can be done online or offline.

    • Para índices rowstore, isso remove a fragmentação, recupera espaço em disco ao compactar as páginas com base na configuração do fator de preenchimento especificada ou existente, e reclassifica as linhas do índice em páginas contíguas.For rowstore indexes, rebuilding removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. Quando ALL é especificado, todos os índices da tabela são descartados e recriados em uma única transação.When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. As restrições de chave estrangeira não precisam ser descartadas com antecedência.Foreign key constraints do not have to be dropped in advance. Quando índices com 128 extensões ou mais são recriados, o Mecanismo de Banco de DadosDatabase Engine adia as desalocações de página atuais e seus bloqueios associados até depois da confirmação da transação.When indexes with 128 extents or more are rebuilt, the Mecanismo de Banco de DadosDatabase Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.
    • Para índices columnstore, a recompilação remove a fragmentação, move todas as linhas para o columnstore e recupera o espaço em disco ao excluir fisicamente as linhas que foram excluídas logicamente da tabela.For columnstore indexes, rebuilding removes fragmentation, moves all rows into the columnstore, and reclaims disk space by physically deleting rows that have been logically deleted from the table. Iniciando com SQL Server 2016 (13.x)SQL Server 2016 (13.x), normalmente, não é necessário recompilar o índice columnstore porque REORGANIZE executa as etapas básicas de uma recompilação em segundo plano como uma operação online.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), rebuilding the columnstore index is usually not needed since REORGANIZE performs the essentials of a rebuild in the background as an online operation.

Nas versões anteriores do SQL ServerSQL Server, às vezes, era possível recriar um índice não clusterizado do rowstore para corrigir as inconsistências causadas por falhas de hardware.In earlier versions of SQL ServerSQL Server, you could sometimes rebuild a rowstore nonclustered index to correct inconsistencies caused by hardware failures. Iniciando com o SQL Server 2008SQL Server 2008, ainda é possível reparar essas inconsistências entre o índice e o índice clusterizado recriando um índice não clusterizado offline.Starting with SQL Server 2008SQL Server 2008, you may still be able to repair such inconsistencies between the index and the clustered index by rebuilding a nonclustered index offline. Entretanto, não é possível reparar inconsistências de índice não clusterizado recriando o índice online, porque o mecanismo de recriação online usará o índice não clusterizado existente como base para a recriação e, portanto, a inconsistência persistirá.However, you cannot repair nonclustered index inconsistencies by rebuilding the index online, because the online rebuild mechanism will use the existing nonclustered index as the basis for the rebuild and thus persist the inconsistency. A recriação do índice offline poderá forçar, algumas vezes, um exame do índice clusterizado (ou heap) e, consequentemente, remover a inconsistência.Rebuilding the index offline can sometimes force a scan of the clustered index (or heap) and so remove the inconsistency. Para garantir uma recompilação do índice clusterizado, remova e recrie o índice não clusterizado.To assure a rebuild from the clustered index, drop and recreate the nonclustered index. Como nas versões anteriores, é recomendável que a recuperação de inconsistências seja feita com a restauração dos dados afetados de um backup; porém, talvez seja possível reparar as inconsistências do índice recriando o índice não clusterizado offline.As with earlier versions, we recommend recovering from inconsistencies by restoring the affected data from a backup; however, you may be able to repair the index inconsistencies by rebuilding the nonclustered index offline. Para obter mais informações, veja DBCC CHECKDB (Transact-SQL).For more information, see DBCC CHECKDB (Transact-SQL)

Detectando a fragmentação

Detecting fragmentation

A primeira etapa para optar pelo método de fragmentação a ser usado é analisar o índice para determinar o grau de fragmentação.The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation.

Detectando a fragmentação em índices de rowstoreDetecting fragmentation on rowstore indexes

Usando a função de sistema sys.dm_db_index_physical_stats, você pode detectar a fragmentação em um índice específico, em todos os índices de uma tabela ou exibição indexada, em todos os índices de um banco de dados ou em todos os índices de todos os bancos de dados.By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. Para índices particionados, sys.dm_db_index_physical_stats também fornece informações de fragmentação por partição.For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

O conjunto de resultados retornado pela função sys.dm_db_index_physical_stats inclui as colunas a seguir:The result set returned by the sys.dm_db_index_physical_stats function includes the following columns:

ColunaColumn DESCRIÇÃODescription
avg_fragmentation_in_percent
avg_fragmentation_in_percent Porcentagem de fragmentação lógica (páginas fora de ordem no índice).The percent of logical fragmentation (out-of-order pages in the index).
fragment_count
fragment_count Número de fragmentos (páginas folha fisicamente consecutivas) do índice.The number of fragments (physically consecutive leaf pages) in the index.
avg_fragment_size_in_pages
avg_fragment_size_in_pages Número médio de páginas em um fragmento de índice.Average number of pages in one fragment in an index.

Depois que o grau de fragmentação for conhecido, use a tabela a seguir para determinar o melhor método para corrigir a fragmentação.After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.

|Valoravg_fragmentation_in_percent avg_fragmentation_in_percent value|Instrução corretivaCorrective statement| |-----------------------------------------------|--------------------------| |> 5% e < = 30%> 5% and < = 30%|ALTER INDEX REORGANIZEALTER INDEX REORGANIZE| |> 30%> 30%|ALTER INDEX REBUILD WITH (ONLINE = ON) 1ALTER INDEX REBUILD WITH (ONLINE = ON) 1|

1 A recompilação de um índice pode ser executada online ou offline. 1 Rebuilding an index can be executed online or offline. A reorganização de um índice sempre é executada online.Reorganizing an index is always executed online. Para atingir disponibilidade semelhante à opção de reorganização, recrie índices online.To achieve availability similar to the reorganize option, you should rebuild indexes online. Para obter mais informações, consulte Perform Index Operations Online.For more information, see Perform Index Operations Online.

Dica

Esses valores fornecem uma orientação aproximada para determinar o ponto em que você deve mudar entre ALTER INDEX REORGANIZE e ALTER INDEX REBUILD.These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. Contudo, os valores reais podem variar de acordo com o caso.However, the actual values may vary from case to case. É importante que você experimente para poder determinar o melhor limite para um ambiente.It is important that you experiment to determine the best threshold for your environment. Por exemplo, se um determinado índice for usado principalmente para operações de verificação, o desempenho delas poderá ser aprimorado pela remoção da fragmentação.For example, if a given index is used mainly for scan operations, removing fragmentation can improve performance of these operations. O benefício de desempenho é menos perceptível para índices que são usados principalmente para operações de busca.The performance benefit is less noticeable for indexes that are used primarily for seek operations. Da mesma forma, remover a fragmentação em um heap (uma tabela sem índice clusterizado) é especialmente útil para operações de verificação de índice não clusterizado, mas tem pouco efeito em operações de pesquisa.Similarly, removing fragmentation in a heap (a table with no clustered index) is especially useful for nonclustered index scan operations, but has little effect in lookup operations.

Em geral, níveis muito baixos de fragmentação (menos de 5 por cento) não devem ser resolvidos por nenhum desses comandos, pois o benefício da remoção de uma pequena quantidade de fragmentação é quase sempre amplamente excedido pelo custo da reorganização ou da recriação do índice.Very low levels of fragmentation (less than 5 percent) should typically not be addressed by either of these commands, because the benefit from removing such a small amount of fragmentation is almost always vastly outweighed by the cost of reorganizing or rebuilding the index. Para obter mais informações sobre ALTER INDEX REORGANIZE e ALTER INDEX REBUILD, consulte ALTER INDEX (Transact-SQL).For more information about ALTER INDEX REORGANIZE and ALTER INDEX REBUILD, refer to ALTER INDEX (Transact-SQL).

Observação

A recriação ou reorganização de índices rowstore pequenos geralmente não reduz a fragmentação.Rebuilding or reorganizing small rowstore indexes often does not reduce fragmentation. As páginas de índices pequenos às vezes são armazenadas em extensões mistas.The pages of small indexes are sometimes stored on mixed extents. Extensões mistas são compartilhadas por até oito objetos, portanto, a fragmentação em um índice pequeno pode não ser reduzida após a reorganização ou recriação.Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it.

Detectando a fragmentação em índices de columnstoreDetecting fragmentation on columnstore indexes

Usando o DMV sys.dm_db_column_store_row_group_physical_stats, você pode determinar a porcentagem de linhas excluídas, que é uma boa medida para a fragmentação em um rowgroup.By using the DMV sys.dm_db_column_store_row_group_physical_stats you can determine the percentage of deleted rows, which is a good measure for the fragmentation in a rowgroup. Use essas informações para computar a fragmentação em um índice específico, todos os índices em uma tabela, todos os índices em um banco de dados ou todos os índices em todos os bancos de dados.Use this information to compute the fragmentation in a specific index, all indexes on a table, all indexes in a database, or all indexes in all databases.

O conjunto de resultados retornado pelo DMV sys.dm_db_column_store_row_group_physical_stats inclui as seguintes colunas:The result set returned by the sys.dm_db_column_store_row_group_physical_stats DMV includes the following columns:

ColunaColumn DESCRIÇÃODescription
total_rows
total_rows Número de linhas armazenadas fisicamente no grupo de linhas.Number of rows physical stored in the row group. Para grupos de linhas compactados, isso inclui as linhas marcadas como excluídas.For compressed row groups, this includes the rows that are marked deleted.
deleted_rows
deleted_rows Número de linhas fisicamente armazenadas em um grupo de linhas compactado que são marcadas para exclusão.Number of rows physically stored in a compressed row group that are marked for deletion. 0 para grupos de linhas que estão no repositório Delta.0 for row groups that are in the delta store.

Isso pode ser usado para calcular a fragmentação usando a fórmula 100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0).This can be used to compute the fragmentation using the formula 100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0). Depois que o grau de fragmentação for conhecido, use a tabela a seguir para determinar o melhor método para corrigir a fragmentação.After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.

|fragmentação calculada em valor percentual computed fragmentation in percent value|Aplica-se à versãoApplies to version|Instrução corretivaCorrective statement| |-----------------------------------------------|--------------------------|--------------------------| |> = 20%> = 20%|SQL Server 2012 (11.x)SQL Server 2012 (11.x) e SQL Server 2014 (12.x)SQL Server 2014 (12.x)-and|ALTER INDEX REBUILDALTER INDEX REBUILD| |> = 20%> = 20%|A partir do SQL Server 2016 (13.x)SQL Server 2016 (13.x)Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)|ALTER INDEX REORGANIZEALTER INDEX REORGANIZE|

Considerações sobre fragmentação de índiceIndex defragmentation considerations

Em determinadas condições, recompilar um índice clusterizado recompilará automaticamente todo índice não clusterizado que faça referência à chave de clustering, se os identificadores físicos ou lógicos contidos nos registros de índice não clusterizados precisam ser alterados.Under certain conditions, rebuilding a clustered index will automatically rebuild any nonclustered index that reference the clustering key, if the physical or logical identifiers contained in the nonclustered index records need to change.

Cenários que requerem que todos os índices rowstore não clusterizados sejam automaticamente recompilados em uma tabela:Scenarios that force all rowstore nonclustered indexes to be automatically rebuilt on a table:

  • Criar um índice clusterizado em uma tabelaCreating a clustered index on a table
  • Remover um índice clusterizado, fazendo com que a tabela seja armazenada como um heapRemoving a clustered index, causing the table to be stored as a heap
  • Alterar a chave de clustering para incluir ou excluir colunasChanging the clustering key to include or exclude columns

Cenários que não requerem que todos os índices rowstore não clusterizados sejam automaticamente recompilados em uma tabela:Scenarios that do not require all rowstore nonclustered indexes to be automatically rebuilt on a table:

  • Recompilar um índice clusterizado exclusivoRebuilding a unique clustered index
  • Recompilar um índice clusterizado não exclusivoRebuilding a non-unique clustered index
  • Alterar o esquema de índice (assim como ao aplicar um esquema de particionamento a um índice clusterizado) ou mover o índice clusterizado para um grupo de arquivos diferenteChanging the index schema, such as applying a partitioning scheme to a clustered index or moving the clustered index to a different filegroup

Importante

Um índice não poderá ser reorganizado ou recriado se o grupo de arquivos no qual ele está localizado estiver offline ou definido como somente leitura.An index cannot be reorganized or rebuilt if the filegroup in which it is located is offline or set to read-only. Quando a palavra-chave ALL for especificada e um ou mais índices estiver em um grupo de arquivos offline ou somente leitura, a instrução falhará.When the keyword ALL is specified and one or more indexes are in an offline or read-only filegroup, the statement fails.

Enquanto ocorre uma reconstrução de índice, a mídia física deve ter espaço suficiente para armazenar duas cópias do índice.While an index rebuild occurs, the physical media must have enough space to store two copies of the index. Quando a recompilação é concluída, o SQL ServerSQL Server exclui o índice original.When the rebuild is finished, SQL ServerSQL Server deletes the original index.

Quando ALL for especificado com a instrução ALTER INDEX, os índices relacionais, clusterizados e não clusterizados e os índices XML da tabela serão reorganizados.When ALL is specified with the ALTER INDEX statement, relational indexes, both clustered and nonclustered, and XML indexes on the table are reorganized.

Considerações específicas para recompilar um índice columnstoreConsiderations specific to rebuilding a columnstore index

Ao recriar um índice columnstore, o Mecanismo de Banco de DadosDatabase Engine lê todos os dados do índice columnstore original, incluindo o armazenamento Delta.When rebuilding a columnstore index, the Mecanismo de Banco de DadosDatabase Engine reads all data from the original columnstore index, including the delta store. Combina os dados em novos rowgroups e compacta os rowgroups em columnstore.It combines the data into new rowgroups, and compresses the rowgroups into the columnstore. O Mecanismo de Banco de DadosDatabase Engine desfragmenta o columnstore excluindo fisicamente as linhas que foram excluídas logicamente da tabela; os bytes excluídos são recuperados no disco.The Mecanismo de Banco de DadosDatabase Engine defragments the columnstore by physically deleting rows that have been logically deleted from the table; the deleted bytes are reclaimed on the disk.

Recriar uma partição em vez de toda a tabela:Rebuild a partition instead of the entire table:

  • Recriar a tabela inteira é uma tarefa demorada se o índice é grande, e isso exige espaço em disco suficiente para armazenar uma cópia adicional do índice durante a recriação.Rebuilding the entire table takes a long time if the index is large, and it requires enough disk space to store an additional copy of the index during the rebuild. Geralmente, é necessário recriar apenas a partição mais recentemente usada.Usually it is only necessary to rebuild the most recently used partition.
  • Em tabelas particionadas, não é necessário recriar todo o índice columnstore, pois é provável que a fragmentação ocorra apenas nas partições que foram modificadas recentemente.For partitioned tables, you do not need to rebuild the entire columnstore index because fragmentation is likely to occur in only the partitions that have been modified recently. As tabelas de fatos e de dimensões grandes geralmente são particionadas para executar operações de backup e gerenciamento em partes da tabela.Fact tables and large dimension tables are usually partitioned in order to perform backup and management operations on chunks of the table.

Recriar uma partição após operações DML pesadas:Rebuild a partition after heavy DML operations:

  • Recriar uma partição desfragmentará a partição e reduzirá o armazenamento em disco.Rebuilding a partition will defragment the partition and reduce disk storage. Essa recriação excluirá todas as linhas do columnstore marcadas para exclusão e moverá todos os rowgroups do armazenamento Delta para o columnstore.Rebuilding will delete all rows from the columnstore that are marked for deletion, and it will move all rowgroups from the delta store into the columnstore. Observe que pode haver várias rowgroups no armazenamento Delta, que possuem menos de um milhão de linhas.Note, there can be multiple rowgroups in the delta store that have less than one million rows.

Recriar uma partição após carregar dados:Rebuild a partition after loading data:

  • Isso garante que todos dados sejam armazenados no columnstore.This ensures all data is stored in the columnstore. Quando os processos atuais carregam menos de 100 mil linhas cada um na mesma partição em simultâneo, a partição pode acabar com vários armazenamento Delta.When concurrent processes each load less than 100,000 rows into the same partition at the same time, the partition can end up with multiple delta stores. A recriação moverá todas as linhas do armazenamento Delta para o columnstore.Rebuilding will move all delta store rows into the columnstore.

Considerações específicas para reorganizar um índice columnstoreConsiderations specific to reorganizing a columnstore index

Ao reorganizar um índice columnstore, Mecanismo de Banco de DadosDatabase Engine compacta cada rowgroup delta FECHADO no columnstore como um rowgroup compactado.When reorganizing a columnstore index, the Mecanismo de Banco de DadosDatabase Engine compresses each CLOSED delta rowgroup into the columnstore as a compressed rowgroup. A partir do SQL Server 2016 (13.x)SQL Server 2016 (13.x) e no Banco de Dados SQL do AzureAzure SQL Database, o comando REORGANIZE executa as seguintes otimizações adicionais de desfragmentação online:Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Banco de Dados SQL do AzureAzure SQL Database, the REORGANIZE command performs the following additional defragmentation optimizations online:

  • Remove fisicamente linhas de um grupo de linhas quando 10% ou mais linhas foram excluídas logicamente.Physically removes rows from a rowgroup when 10% or more of the rows have been logically deleted. Os bytes excluídos são recuperados na mídia física.The deleted bytes are reclaimed on the physical media. Por exemplo, se um grupo de linhas compactado de 1 milhão de linhas tiver 100 mil linhas excluídas, o SQL Server removerá as linhas excluídas e recompactará o rowgroup com 900 mil linhas.For example, if a compressed row group of 1 million rows has 100K rows deleted, SQL Server will remove the deleted rows and recompress the rowgroup with 900k rows. Ele salva no armazenamento removendo as linhas excluídas.It saves on the storage by removing deleted rows.

  • Combina um ou mais rowgroups compactados para aumentar linhas por rowgroup até o máximo de 1.024.576 linhas.Combines one or more compressed rowgroups to increase rows per rowgroup up to the maximum of 1,024,576 rows. Por exemplo, se você importar em massa cinco lotes de 102.400 linhas, obterá cinco rowgroups compactados.For example, if you bulk import 5 batches of 102,400 rows you will get 5 compressed rowgroups. Se você executar REORGANIZE, esses rowgroups serão mesclados em um grupo de linhas compactado de 512 mil linhas de tamanho.If you run REORGANIZE, these rowgroups will get merged into 1 compressed rowgroup of size 512,000 rows. Isso pressupõe que não havia nenhuma limitação de tamanho ou memória de dicionário.This assumes there were no dictionary size or memory limitations.

  • Para rowgroups em que 10% ou mais linhas foram excluídas logicamente, o Mecanismo de Banco de DadosDatabase Engine tenta combinar esse grupo de linhas com um ou mais rowgroups.For rowgroups in which 10% or more of the rows have been logically deleted, the Mecanismo de Banco de DadosDatabase Engine will try to combine this rowgroup with one or more rowgroups. Por exemplo, o rowgroup 1 é compactado com 500 mil linhas e o rowgroup 21 é compactado com o máximo de 1.048.576 linhas.For example, rowgroup 1 is compressed with 500,000 rows and rowgroup 21 is compressed with the maximum of 1,048,576 rows. O rowgroup 21 tem 60% das linhas excluídas, o que deixa 409.830 linhas.Rowgroup 21 has 60% of the rows deleted which leaves 409,830 rows. O Mecanismo de Banco de DadosDatabase Engine favorece combinar esses dois rowgroups para compactar um novo rowgroup com 909.830 linhas.The Mecanismo de Banco de DadosDatabase Engine favors combining these two rowgroups to compress a new rowgroup that has 909,830 rows.

Depois de executar os carregamentos de dados, você poderá ter vários rowgroups pequenos no armazenamento Delta.After performing data loads, you can have multiple small rowgroups in the delta store. Use ALTER INDEX REORGANIZE para forçar todos os rowgroups no índice columnstore e, em seguida, combinar os rowgroups em rowgroups menores com mais linhas.You can use ALTER INDEX REORGANIZE to force all of the rowgroups into the columnstore, and then to combine the rowgroups into fewer rowgroups with more rows. A operação de reorganização removerá também as linhas que foram excluídas do columnstore.The reorganize operation will also remove rows that have been deleted from the columnstore.

Limitações e restriçõesLimitations and restrictions

Índices rowstore com mais de 128 extensões são recriados em duas fases separadas: lógica e física.Rowstore indexes with more than 128 extents are rebuilt in two separate phases: logical and physical. Na fase lógica, as unidades de alocação existentes usadas pelo índice são marcadas para desalocação, as linhas de dados são copiadas, ordenadas e, depois, movidas para novas unidades de alocação criadas para armazenar o índice recriado.In the logical phase, the existing allocation units used by the index are marked for deallocation, the data rows are copied and sorted, then moved to new allocation units created to store the rebuilt index. Na fase física, as unidades de alocação previamente marcadas para desalocação são fisicamente canceladas em transações curtas que ocorrem em segundo plano e que não exigem muitos bloqueios.In the physical phase, the allocation units previously marked for deallocation are physically dropped in short transactions that happen in the background, and do not require many locks. Para obter mais informações sobre as extensões, consulte o Guia de arquitetura de páginas e extensões.For more information about extents, refer to the Pages and Extents Architecture Guide.

A instrução ALTER INDEX REORGANIZE exige que o arquivo de dados que contém o índice tenha espaço disponível, pois a operação só pode alocar páginas de trabalho temporárias no mesmo arquivo, não em outro arquivo no grupo de arquivos.The ALTER INDEX REORGANIZE statement requires the data file containing the index to have space available, because the operation can only allocate temporary work pages on the same file, not another file within the filegroup. Portanto, embora o grupo de arquivos possa ter páginas livres disponíveis, o usuário ainda poderá se deparar com o erro 1105:Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.So although the filegroup might have free pages available, the user can still encounter error 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Aviso

É 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 nonaligned 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 Microsoft recomenda usar índices alinhados apenas quando o número de partições for maior que 1.000.Microsoft recommends using only aligned indexes when the number of partitions exceed 1,000.

Um índice não poderá ser reorganizado ou recriado se o grupo de arquivos no qual ele está localizado estiver offline ou definido como somente leitura.An index cannot be reorganized or rebuilt if the filegroup in which it is located is offline or set to read-only. Quando a palavra-chave ALL for especificada e um ou mais índices estiver em um grupo de arquivos offline ou somente leitura, a instrução falhará.When the keyword ALL is specified and one or more indexes are in an offline or read-only filegroup, the statement fails.

Quando um índice for criado ou reconstruído no SQL ServerSQL Server, as estatísticas serão criadas ou atualizadas por meio do exame de todas as linhas da tabela.When an index is created or rebuilt in SQL ServerSQL Server, statistics are created or updated by scanning all the rows in the table. No entanto, começando com o SQL Server 2012 (11.x)SQL Server 2012 (11.x), as estatísticas não são criadas nem atualizadas por meio do exame de todas as linhas da tabela quando um índice particionado é criado ou reconstruído.However, starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), statistics are not created or updated 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 essas estatísticas.Instead, the Query Optimizer uses the default sampling algorithm to generate these statistics. 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.

Quando um índice é reorganizado em SQL ServerSQL Server, as estatísticas não são atualizadas.When an index is reorganized in SQL ServerSQL Server, statistics are not updated.

Um índice não pode ser reorganizado quando ALLOW_PAGE_LOCKS está definido como OFF.An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF.

Até SQL Server 2017 (14.x)SQL Server 2017 (14.x), a recriação de um índice columnstore clusterizado é uma operação offline.Up to SQL Server 2017 (14.x)SQL Server 2017 (14.x), rebuilding a clustered columnstore index is an offline operation. O Mecanismo de Banco de DadosDatabase Engine tem que adquirir um bloqueio exclusivo na tabela ou na partição durante a recompilação.The Mecanismo de Banco de DadosDatabase Engine has to acquire an exclusive lock on the table or partition while the rebuild occurs. Os dados estão offline e não estão disponíveis durante a recompilação mesmo ao usar NOLOCK, RCSI (isolamento de instantâneo com leitura confirmada) ou isolamento de instantâneo.The data is offline and unavailable during the rebuild even when using NOLOCK, Read-committed Snapshot Isolation (RCSI), or Snapshot Isolation. A partir do SQL Server 2019 (15.x)SQL Server 2019 (15.x),um índice columnstore clusterizado pode ser recompilado usando a opção ONLINE=ON.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), a clustered columnstore index can be rebuilt using the ONLINE=ON option.

Para uma tabela do SQL Data Warehouse do Azure com um índice columnstore clusterizado ordenado, ALTER INDEX REBUILD reclassificará os dados usando TempDB.For an Azure SQL Data Warehouse table with an ordered clustered columnstore index, ALTER INDEX REBUILD will re-sort the data using TempDB. Monitore o TempDB durante operações de recompilação.Monitor TempDB during rebuild operations. Se você precisar de mais espaço de TempDB, aumente o data warehouse.If you need more TempDB space, scale up the data warehouse. Diminua quando a recompilação do índice for concluída.Scale back down once the index rebuild is complete.

Para uma tabela do SQL Data Warehouse do Azure com um índice columnstore clusterizado ordenado, o ALTER INDEX REORGANIZE não reclassificará os dados.For an Azure SQL Data Warehouse table with an ordered clustered columnstore index, ALTER INDEX REORGANIZE does not re-sort the data. Para reclassificar os dados, use ALTER INDEX REBUILD.To resort the data use ALTER INDEX REBUILD.

Segurança

Security

Permissões

Permissions:

Requer a permissão ALTER na tabela ou exibição.Requires ALTER permission on the table or view. O usuário deve ser um membro de pelo menos uma das seguintes funções:User must be a member of at least one of the following roles:

  • Função de banco de dados db_ddladmin1 db_ddladmin database role 1
  • Função de banco de dados db_owner db_owner database role
  • função de servidor sysadmin sysadmin server role

1A função de banco de dados db_ddladmin é a menos privilegiada. 1 db_ddladmin database role is the least privileged.

Verificar a fragmentação de índice usando o SQL Server Management StudioSQL Server Management Studio

Check index fragmentation using

Observação

Management StudioManagement Studio não pode ser usado para computar a fragmentação de índices columnstore no SQL Server e não pode ser usado para computar a fragmentação de nenhum índice no Banco de Dados SQL do Azure.cannot be used to compute fragmentation of columnstore indexes in SQL Server and cannot be used to compute fragmentation of any indexes in Azure SQL Database. Use o exemplo Transact-SQLTransact-SQLa seguir.Use the example below.

  1. No Pesquisador de Objetos, expanda o banco de dados que contém a tabela na qual você deseja verificar a fragmentação de um índice.In Object Explorer, Expand the database that contains the table on which you want to check an index's fragmentation.
  2. Expanda a pasta Tabelas .Expand the Tables folder.
  3. Expanda a tabela na qual você deseja verificar a fragmentação de um índice.Expand the table on which you want to check an index's fragmentation.
  4. Expanda a pasta Índices .Expand the Indexes folder.
  5. Clique com o botão direito do mouse no índice cuja fragmentação você deseja verificar e selecione Propriedades.Right-click the index of which you want to check the fragmentation and select Properties.
  6. Em Selecione uma página, selecione Fragmentação.Under Select a page, select Fragmentation.

As informações a seguir estão disponíveis na página Fragmentação :The following information is available on the Fragmentation page:

ValorValue DESCRIÇÃODescription
Preenchimento da página
Page fullness Indica o preenchimento médio das páginas do índice, como uma porcentagem.Indicates average fullness of the index pages, as a percentage. 100% significa que as páginas de índice estão completamente preenchidas.100% means the index pages are completely full. 50% significa que, em média, cada página do índice está preenchida pela metade.50% means that, on average, each index page is half full.
Fragmentação total
Total fragmentation A porcentagem de fragmentação lógica.The logical fragmentation percentage. Isso indica o número de páginas em um índice que não estão armazenadas em ordem.This indicates the number of pages in an index that are not stored in order.
Tamanho médio da linha
Average row size O tamanho médio de uma linha de nível folha.The average size of a leaf-level row.
Profundidade
Depth O número de níveis no índice, inclusive o nível folha.The number of levels in the index, including the leaf-level.
Registros encaminhados
Forwarded records O número de registros em um heap com ponteiros encaminhados a outro local de dadosThe number of records in a heap that have forward pointers to another data location. (Esse estado ocorre durante uma atualização, quando não há espaço suficiente para armazenar a nova linha no local original.)(This state occurs during an update, when there is not enough room to store the new row in the original location.)
Linhas fantasmas
Ghost rows O número de linhas que estão marcadas como excluídas, mas ainda não foram removidas.The number of rows that are marked as deleted but not yet removed. Essas linhas serão removidas por um thread de limpeza, quando o servidor não estiver ocupado.These rows will be removed by a clean-up thread, when the server is not busy. Esse valor não inclui linhas que estejam sendo retidas devido a uma transação de isolamento de instantâneo pendente.This value does not include rows that are being retained due to an outstanding snapshot isolation transaction.
Tipo de índice
Index type O tipo do índice.The type of index. Os valores possíveis são Índice cluster, Índice não clustere XML Primário.Possible values are Clustered index, Nonclustered index, and Primary XML. As tabelas também podem ser armazenadas como um heap (sem-índices), mas nesse caso a página Propriedades do Índice não pode ser aberta.Tables can also be stored as a heap (without indexes), but then this Index Properties page cannot be opened.
Linhas em nível folha
Leaf-level rows O número de linhas em nível folha.The number of leaf-level rows.
Tamanho máximo da linha
Maximum row size O tamanho máximo da linha em nível folha.The maximum leaf-level row size.
Tamanho mínimo da linha
Minimum row size O tamanho mínimo da linha em nível folha.The minimum leaf-level row size.
PáginasPages O número total de páginas de dados.The total number of data pages.
Identificação da Partição
Partition ID A ID da partição da árvore b que contém o índice.The partition ID of the b-tree containing the index.
Linhas fantasmas de versão
Version ghost rows O número de registros fantasmas que estão sendo retidos devido a uma transação de isolamento de instantâneo pendente.The number of ghost records that are being retained due to an outstanding snapshot isolation transaction.

Verificar a fragmentação de índice usando o Transact-SQLTransact-SQL

Check index fragmentation using

Para verificar a fragmentação de um índice de rowstoreTo check the fragmentation of a rowstore index

O exemplo a seguir localiza a porcentagem de fragmentação média de todos os índices na tabela HumanResources.Employee no banco de dados AdventureWorks2016.The following example finds the average fragmentation percentage of all indexes in the HumanResources.Employee table in the AdventureWorks2016 database.

SELECT a.object_id, object_name(a.object_id) AS TableName,
    a.index_id, name AS IndedxName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
    (DB_ID (N'AdventureWorks2016_EXT')
        , OBJECT_ID(N'HumanResources.Employee')
        , NULL
        , NULL
        , NULL) AS a
INNER JOIN sys.indexes AS b
    ON a.object_id = b.object_id
    AND a.index_id = b.index_id;
GO

A instrução anterior retorna um conjunto de resultados semelhante ao que segue.The previous statement returns a result set similar to the following.

object_id   TableName    index_id    IndexName                                             avg_fragmentation_in_percent
----------- ------------ ----------- ----------------------------------------------------- ------------------------------
1557580587  Employee     1           PK_Employee_BusinessEntityID                          0
1557580587  Employee     2           IX_Employee_OrganizationalNode                        0
1557580587  Employee     3           IX_Employee_OrganizationalLevel_OrganizationalNode    0
1557580587  Employee     5           AK_Employee_LoginID                                   66.6666666666667
1557580587  Employee     6           AK_Employee_NationalIDNumber                          50
1557580587  Employee     7           AK_Employee_rowguid                                   0

(6 row(s) affected)

Para saber mais, confira sys.dm_db_index_physical_stats.For more information, see sys.dm_db_index_physical_stats.

Para verificar a fragmentação de um índice de columnstoreTo check the fragmentation of a columnstore index

O exemplo a seguir localiza a porcentagem de fragmentação média de todos os índices na tabela dbo.FactResellerSalesXL_CCI no banco de dados AdventureWorksDW2016.The following example finds the average fragmentation percentage of all indexes in the dbo.FactResellerSalesXL_CCI table in the AdventureWorksDW2016 database.

SELECT i.object_id,
    object_name(i.object_id) AS TableName,
    i.index_id,
    i.name AS IndexName,
    100*(ISNULL(SUM(CSRowGroups.deleted_rows),0))/NULLIF(SUM(CSRowGroups.total_rows),0) AS 'Fragmentation'
FROM sys.indexes AS i  
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups
    ON i.object_id = CSRowGroups.object_id
    AND i.index_id = CSRowGroups.index_id
WHERE object_name(i.object_id) = 'FactResellerSalesXL_CCI'
GROUP BY i.object_id, i.index_id, i.name
ORDER BY object_name(i.object_id), i.name;

A instrução anterior retorna um conjunto de resultados semelhante ao que segue.The previous statement returns a result set similar to the following.

object_id   TableName                   index_id    IndexName                       Fragmentation
----------- --------------------------- ----------- ------------------------------- ---------------
114099447   FactResellerSalesXL_CCI     1           IndFactResellerSalesXL_CCI      0

(1 row(s) affected)

Remover a fragmentação usando o SQL Server Management StudioSQL Server Management Studio

Remove fragmentation using

Para reorganizar ou recriar um índiceTo reorganize or rebuild an index

  1. No Pesquisador de Objetos, expanda o banco de dados que contém a tabela na qual você deseja reorganizar um índice.In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
  2. Expanda a pasta Tabelas .Expand the Tables folder.
  3. Expanda a tabela na qual você deseja reorganizar um índice.Expand the table on which you want to reorganize an index.
  4. Expanda a pasta Índices .Expand the Indexes folder.
  5. Clique com o botão direito do mouse no índice a ser reorganizado e selecione Reorganizar.Right-click the index you want to reorganize and select Reorganize.
  6. Na caixa de diálogo Reorganizar Índices , verifique se o índice correto está na grade Índices a serem reorganizados e clique em OK.In the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized grid and click OK.
  7. Marque a caixa de seleção Compactar dados de coluna de objeto grande para especificar que todas as páginas que contêm dados de objeto grande (LOB) também sejam compactadas.Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
  8. Clique em OK.Click OK.

Observação

A reorganização de um índice columnstore usando Management StudioManagement Studio combinará rowgroups COMPRESSED, mas não forçará a compactação de todos os rowgroup no columnstore.Reorganizing a columnstore index using Management StudioManagement Studio will combine COMPRESSED rowgroups together, but does not force all rowgroups to be compressed into the columnstore. Os rowgroups FECHADO serão compactados, mas os rowgroups ABERTOS não serão compactados no columnstore.CLOSED rowgroups will be compressed but OPEN rowgroups will not be compressed into the columnstore. Para compactar todos os rowgroups, use o exemplo Transact-SQLTransact-SQLa seguir.To compress all rowgroups, use the Transact-SQLTransact-SQL example below.

Para reorganizar todos os índices de uma tabelaTo reorganize all indexes in a table

  1. No Pesquisador de Objetos, expanda o banco de dados que contém a tabela na qual você deseja reorganizar os índices.In Object Explorer, Expand the database that contains the table on which you want to reorganize the indexes.
  2. Expanda a pasta Tabelas .Expand the Tables folder.
  3. Expanda a tabela na qual você deseja reorganizar os índices.Expand the table on which you want to reorganize the indexes.
  4. Clique com o botão direito do mouse na pasta Índices e selecione Reorganizar Tudo.Right-click the Indexes folder and select Reorganize All.
  5. Na caixa de diálogo Reorganizar Índices , verifique se os índices corretos estão na grade Índices a serem reorganizadose clique em OK.In the Reorganize Indexes dialog box, verify that the correct indexes are in the Indexes to be reorganized. Para remover um índice da grade Índices a serem reorganizados , selecione o índice e pressione a tecla Delete.To remove an index from the Indexes to be reorganized grid, select the index and then press the Delete key.
  6. Marque a caixa de seleção Compactar dados de coluna de objeto grande para especificar que todas as páginas que contêm dados de objeto grande (LOB) também sejam compactadas.Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
  7. Clique em OK.Click OK.

Para recriar um índiceTo rebuild an index

  1. No Pesquisador de Objetos, expanda o banco de dados que contém a tabela na qual você deseja reorganizar um índice.In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
  2. Expanda a pasta Tabelas .Expand the Tables folder.
  3. Expanda a tabela na qual você deseja reorganizar um índice.Expand the table on which you want to reorganize an index.
  4. Expanda a pasta Índices .Expand the Indexes folder.
  5. Clique com o botão direito do mouse no índice a ser reorganizado e selecione Recompilar.Right-click the index you want to reorganize and select Rebuild.
  6. Na caixa de diálogo Recriar Índices , verifique se o índice correto está na grade Índices a serem recriados e clique em OK.In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK.
  7. Marque a caixa de seleção Compactar dados de coluna de objeto grande para especificar que todas as páginas que contêm dados de objeto grande (LOB) também sejam compactadas.Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
  8. Clique em OK.Click OK.

Remover a fragmentação usando o Transact-SQLTransact-SQL

Remove fragmentation using

Observação

Para obter mais exemplos de como usar Transact-SQLTransact-SQL para recompilarou reorganizar índices, CONFIRA exemplos de ALTER INDEX: Índices columnstore e exemplos ALTER INDEX: Índices rowstore.For more examples about using Transact-SQLTransact-SQL to rebuild or reorganize indexes, see ALTER INDEX Examples: Columnstore Indexes and ALTER INDEX Examples: Rowstore Indexes.

Para reorganizar um índice fragmentadoTo reorganize a fragmented index

O exemplo a seguir reorganiza o índice IX_Employee_OrganizationalLevel_OrganizationalNode na tabela HumanResources.Employee do banco de dados AdventureWorks2016.The following example reorganizes the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table in the AdventureWorks2016 database.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

O exemplo a seguir reorganiza o índice columnstore IndFactResellerSalesXL_CCI na tabela dbo.FactResellerSalesXL_CCI do banco de dados AdventureWorksDW2016.The following example reorganizes the IndFactResellerSalesXL_CCI columnstore index on the dbo.FactResellerSalesXL_CCI table in the AdventureWorksDW2016 database.

-- This command will force all CLOSED and OPEN rowgroups into the columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Para reorganizar todos os índices de uma tabelaTo reorganize all indexes in a table

O exemplo a seguir reorganiza todos os índices na tabela HumanResources.Employee do banco de dados AdventureWorks2016.The following example reorganizes all indexes on the HumanResources.Employee table in the AdventureWorks2016 database.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Para recompilar um índice fragmentadoTo rebuild a fragmented index

O exemplo a seguir recompila um único índice na tabela Employee do banco de dados AdventureWorks2016.The following example rebuilds a single index on the Employee table in the AdventureWorks2016 database.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Para recriar todos os índices de uma tabelaTo rebuild all indexes in a table

O exemplo a seguir recria todos os índices associados à tabela no banco de dados AdventureWorks2016 usando a palavra-chave ALL.The following example rebuilds all indexes associated with the table in the AdventureWorks2016 database using the ALL keyword. Três opções são especificadas.Three options are specified.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

Para obter mais informações, consulte ALTER INDEX (Transact-SQL).For more information, see ALTER INDEX (Transact-SQL).)

Índice automático e gerenciamento de estatísticasAutomatic index and statistics management

Aproveite soluções como a Desfragmentação de índice adaptável para gerenciar automaticamente a desfragmentação de índice e as atualizações de estatísticas em um ou mais bancos de dados.Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. Este procedimento escolhe automaticamente se deve recompilar ou reorganizar um índice de acordo com seu nível de fragmentação, entre outros parâmetros, e atualizar as estatísticas com um limite linear.This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

Confira tambémSee also