Reorganizar e recriar índicesReorganize and Rebuild Indexes

ESTE TÓPICO APLICA-SE A: simSQL Server (a partir de 2008)simBanco de Dados SQL do Microsoft AzuresimAzure SQL Data Warehouse simParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Para ver o conteúdo relacionado a versões anteriores do SQL Server, consulte Reorganizar e recompilar índices.For content related to previous versions of SQL Server, see Reorganize and Rebuild Indexes.

Este tópico descreve como reorganizar ou recompilar índice fragmentado no SQL Server 2017SQL Server 2017 usando o SQL Server Management StudioSQL Server Management Studio ou o Transact-SQLTransact-SQL.This topic describes how to reorganize or rebuild a fragmented index in SQL Server 2017SQL Server 2017 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 mantém 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 maintains 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. Índices com fragmentação pesada podem degradar o desempenho da consulta e causar lentidão de resposta do aplicativo.Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

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. A recriação de um índice descarta e recria o índice.Rebuilding an index drops and re-creates the index. 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.This 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. A reorganização de um índice utiliza recursos mínimos do sistema.Reorganizing an index uses minimal system resources. 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.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.

Antes de começar Before You Begin

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. 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_percentavg_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_countfragment_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_pagesavg_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)ALTER INDEX REBUILD WITH (ONLINE = ON)

* A recriação de um índice pode ser executada online ou offline.* 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.

Esses valores fornecem uma diretriz elementar para a determinação do ponto em que se deve alternar 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. 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 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.

Observação

Em geral, a fragmentação em índices pequenos não é frequentemente controlável.In general, fragmentation on small indexes is often not controllable. 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. As 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 do índice.Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

Limitações e restrições Limitations and Restrictions

  • Índices com mais de 128 extensões são recriados em duas fases separadas: lógica e física.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.

  • As opções de índice não podem ser especificadas durante a reorganização de um índice.Index options cannot be specified when reorganizing an index.

  • 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á receber o erro 1105 “Não foi possível alocar espaço ao objeto <index name>.<table name> no banco de dados <database name>, pois o grupo de arquivos 'PRIMARY' está cheio”.So although the filegroup might have free pages available, the user can still encounter error 1105 "Could not allocate space for object <index name>.<table name> in database <database name> because the 'PRIMARY' filegroup is full."

  • É 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.

Observação

Começando com o SQL Server 2012SQL Server 2012, 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 2012SQL Server 2012, 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. Para obter estatísticas em índices particionados por meio do exame de 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.

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 membro da função de servidor fixa sysadmin ou das funções de banco de dados fixas db_ddladmin e db_owner .User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Usando o SQL Server Management Studio Using SQL Server Management Studio

Para verificar a fragmentação de um índiceTo check the fragmentation of an index

  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:

    Preenchimento da páginaPage 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 totalTotal 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 linhaAverage row size
    O tamanho médio de uma linha de nível folha.The average size of a leaf level row.

    ProfundidadeDepth
    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 encaminhadosForwarded 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 fantasmasGhost 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 índiceIndex 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 folhaLeaf-level rows
    O número de linhas em nível folha.The number of leaf level rows.

    Tamanho máximo da linhaMaximum row size
    O tamanho máximo da linha em nível folha.The maximum leaf-level row size.

    Tamanho mínimo da linhaMinimum 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.

    Partition IDPartition 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ãoVersion 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.

Usando Transact-SQL Using Transact-SQL

Para verificar a fragmentação de um índiceTo check the fragmentation of an index

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de DadosDatabase Engine.In Object Explorer, connect to an instance of Mecanismo de Banco de DadosDatabase Engine.

  2. Na barra Padrão, clique em Nova Consulta.On the Standard bar, click New Query.

  3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    -- Find the average fragmentation percentage of all indexes  
    -- in the HumanResources.Employee table.   
    SELECT a.index_id, name, avg_fragmentation_in_percent  
    FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), 
          OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a  
        JOIN sys.indexes AS b 
          ON a.object_id = b.object_id AND a.index_id = b.index_id;   
    GO  
    

    A instrução acima poderia retornar um conjunto de resultados semelhante ao que segue.The statement above might return a result set similar to the following.

    index_id    name                                                  avg_fragmentation_in_percent  
    ----------- ----------------------------------------------------- ----------------------------  
    1           PK_Employee_BusinessEntityID                          0  
    2           IX_Employee_OrganizationalNode                        0  
    3           IX_Employee_OrganizationalLevel_OrganizationalNode    0  
    5           AK_Employee_LoginID                                   66.6666666666667  
    6           AK_Employee_NationalIDNumber                          50  
    7           AK_Employee_rowguid                                   0  
    
    (6 row(s) affected)  
    

    Para obter mais informações, veja sys.dm_db_index_physical_stats (Transact-SQL).For more information, see sys.dm_db_index_physical_stats (Transact-SQL).

Usando o SQL Server Management Studio Using SQL Server Management Studio

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.

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.

Usando Transact-SQL Using Transact-SQL

Para reorganizar um índice desfragmentadoTo reorganize a defragmented index

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de DadosDatabase Engine.In Object Explorer, connect to an instance of Mecanismo de Banco de DadosDatabase Engine.

  2. Na barra Padrão, clique em Nova Consulta.On the Standard bar, click New Query.

  3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;   
    GO  
    -- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode 
    -- index on the HumanResources.Employee table.   
    
    ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode 
      ON HumanResources.Employee  
    REORGANIZE ;   
    GO  
    

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

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de DadosDatabase Engine.In Object Explorer, connect to an instance of Mecanismo de Banco de DadosDatabase Engine.

  2. Na barra Padrão, clique em Nova Consulta.On the Standard bar, click New Query.

  3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;   
    GO  
    -- Reorganize all indexes on the HumanResources.Employee table.  
    ALTER INDEX ALL ON HumanResources.Employee  
    REORGANIZE ;   
    GO  
    

Para recriar um índice desfragmentadoTo rebuild a defragmented index

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de DadosDatabase Engine.In Object Explorer, connect to an instance of Mecanismo de Banco de DadosDatabase Engine.

  2. Na barra Padrão, clique em Nova Consulta.On the Standard bar, click New Query.

  3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.Copy and paste the following example into the query window and click Execute. O exemplo recria um único índice na tabela Employee .The example rebuilds a single index on the Employee table.

    USE AdventureWorks2012;
    GO
    ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
    REBUILD;
    GO
    
    USE AdventureWorks2012;
    GO
    ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
    REBUILD;
    GO
    

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

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de DadosDatabase Engine.In Object Explorer, connect to an instance of Mecanismo de Banco de DadosDatabase Engine.

  2. Na barra Padrão, clique em Nova Consulta.On the Standard bar, click New Query.

  3. Copie e cole o exemplo a seguir na consulta. O exemplo especifica a palavra-chave ALL.Copy and paste the following example into the query The example specifies the keyword ALL. Isso recria todos os índices associados à tabela.This rebuilds all indexes associated with the table. Três opções são especificadas.Three options are specified.

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

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

Consulte tambémSee Also

Guia de criação de índice do SQL ServerSQL Server Index Design Guide