Reorganizando e recriando índices

Detectando a fragmentação

A primeira etapa para optar pelo método de fragmentação a ser usado é analisar o índice para determinar o grau de fragmentação. 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. Para índices particionados, sys.dm_db_index_physical_stats também fornece informações de fragmentação por partição.

O conjunto de resultados retornado pela função sys.dm_db_index_physical_stats inclui as colunas a seguir.

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.

* A recriação de um índice pode ser executada online ou offline. A reorganização de um índice sempre é executada online. Para atingir disponibilidade semelhante à opção de reorganização, recrie índices 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. Contudo, os valores reais podem variar de acordo com o caso. É importante que você experimente para poder determinar o melhor limite para um ambiente.

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.

Observação

Em geral, a fragmentação em índices pequenos não é freqüentemente controlável. As páginas de índices pequenos não são armazenadas em extensões mistas. 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. Para obter mais informações sobre eventos mistos, consulte Compreendendo páginas e extensões.

Exemplo

USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
     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

index_id    name                        avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1           PK_Product_ProductID        15.076923076923077
2           AK_Product_ProductNumber    50.0
3           AK_Product_Name             66.666666666666657
4           AK_Product_rowguid          50.0

(4 row(s) affected)

Reorganizando um índice

Para reorganizar um ou mais índices, use a instrução ALTER INDEX com a cláusula REORGANIZE. Essa instrução substitui a instrução DBCC INDEXDEFRAG. Para reorganizar uma única partição de um índice particionado, use a cláusula PARTITION de ALTER INDEX.

A reorganização de um índice 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 dos nós folha (da esquerda para a direita). Ter as páginas em ordem aprimora o desempenho do exame do índice. O índice é reorganizado dentro das páginas existentes alocadas; nenhuma página nova é alocada. Se o índice se estender por mais de um arquivo, os arquivos serão reorganizados um por vez. As páginas não migram entre arquivos.

A reorganização também compacta as páginas de índice. Todas as páginas vazias criadas por essa compactação são removidas, o que fornece espaço adicional disponível em disco. A compactação é baseada no valor do fator de preenchimento da exibição do catálogo sys.indexes.

O processo de reorganização utiliza recursos mínimos de sistema. Além disso, a reorganização é executada automaticamente online. O processo não mantém bloqueios de longo prazo; por isso, não bloqueará as atualizações nem as consultas em execução.

Reorganize um índice quando ele não estiver excessivamente fragmentado. Consulte a tabela anterior para obter as diretrizes de fragmentação. Contudo, se o índice estiver excessivamente fragmentado, você obterá resultados melhores recriando-o.

Compactação de tipo de dados de objeto grande

Recriando um índice

A recriação de um índice cancela o índice e cria outro. Quando isso é feito, a fragmentação é removida, o espaço em disco é recuperado pela compactação das páginas que usam a configuração do fator de preenchimento especificado ou existente e as linhas do índice são reordenadas em páginas contíguas (alocando novas páginas, se necessário). Isso pode melhorar o desempenho do disco reduzindo o número de leituras de página necessário à obtenção dos dados solicitados.

Os seguintes métodos podem ser usados para recriar índices clusterizados e não clusterizados:

• ALTER INDEX com a cláusula REBUILD. Essa instrução substitui a instrução DBCC DBREINDEX.

• CREATE INDEX com a cláusula DROP_EXISTING.

Todos os métodos realizam a mesma função; porém, há vantagens e desvantagens a serem consideradas, conforme mostrado na tabela a seguir.

* Um índice não clusterizado pode ser convertido em um tipo de índice clusterizado pela especificação de CLUSTERED na definição do índice. Essa operação precisa ser executada com a opção ONLINE definida como OFF. A conversão de clusterizado em não clusterizado não tem suporte, independentemente da configuração de ONLINE.

** Se o índice for recriado com o mesmo nome, colunas e ordem de classificação, a operação de classificação poderá ser omitida. A operação de recriação verifica se as linhas estão ordenadas durante a criação do índice.

Você também pode recriar um índice cancelando-o, em primeiro lugar, com a instrução DROP INDEX e recriando-o com uma instrução CREATE INDEX separada. A realização dessas operações como instruções separadas resulta em várias desvantagens e não é recomendada.

Desabilitando índices não clusterizados para conservar espaço em disco durante operações de recriação

Quando um índice não clusterizado é desabilitado, as linhas de dados do índice são excluídas, mas a definição do índice permanece nos metadados. O índice é habilitado durante a sua recriação. Quando o índice não clusterizado não é desabilitado, a operação de recriação exige espaço em disco temporário suficiente para armazenar o índice antigo e o novo. Porém, ao desabilitar e recriar um índice não-clusterizado em transações separadas, o espaço em disco disponível pela desabilitação do índice poderá ser reutilizado pela recriação subseqüente ou qualquer outra operação. Nenhum espaço adicional é exigido, exceto o espaço em disco temporário para classificação que, em geral, tem 20 por cento do tamanho do índice. Se o índice não clusterizado estiver na chave primária, qualquer índice ativo, que faça referência a restrições FOREIGN KEY, será desabilitado automaticamente. Essas restrições precisam ser habilitadas manualmente após a recriação do índice. Para obter mais informações, consulte Desabilitando índices e Diretrizes para habilitar índices e restrições.

Recriando índices grandes

Índices com mais de 128 extensões são recriados em duas fases separadas: lógica e física. 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. 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. Para obter mais informações, consulte Descartando e recriando objetos grandes.

Configurando opções de índice

As opções de índice não podem ser especificadas durante a reorganização de um índice. No entanto, as seguintes opções de índice podem ser definidas quando você recria um índice usando ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING:

Observação
Se uma operação de classificação não for necessária, ou se a classificação puder ser executada na memória, a opção SORT_IN_TEMPDB será ignorada.

Além disso, a cláusula SET da instrução ALTER INDEX permite definir as seguintes opções de índice sem recriar o índice:

Para obter mais informações, consulte Configurando opções de índice.

Para recriar ou reorganizar um índice

ALTER INDEX (Transact-SQL)

Para recriar um índice cancelando e recriando um índice em uma única etapa

CREATE INDEX (Transact-SQL)

Exemplos

A. Recriando um índice

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

B. Recriando todos os índices em uma tabela e especificando opções

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

C. Reorganizando um índice com compactação LOB

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

Consulte também

Referência

Conceitos

Outros recursos