Reorganizando e recriando índices
O Mecanismo de Banco de Dados do SQL Server mantém os índices automaticamente sempre que são realizadas operações de entrada, atualização ou exclusão nos dados subjacentes. No decorrer do tempo, essas modificações podem fazer com que as informações do índice sejam dispersadas pelo banco de dados (fragmentadas). 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. Índices com fragmentação pesada podem degradar o desempenho da consulta e causar lentidão de resposta do aplicativo. Para obter mais informações, consulte o site da Microsoft.
Você pode solucionar a fragmentação de índice reorganizando ou recriando um índice. 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.
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.
Coluna |
Descrição |
|---|---|
avg_fragmentation_in_percent |
Porcentagem de fragmentação lógica (páginas fora de ordem no índice). |
fragment_count |
Número de fragmentos (páginas folha fisicamente consecutivas) do índice. |
avg_fragment_size_in_pages |
Número médio de páginas em um fragmento de índice. |
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.
Valor avg_fragmentation_in_percent |
Instrução corretiva |
|---|---|
> 5% e < = 30% |
ALTER INDEX REORGANIZE |
> 30% |
ALTER INDEX REBUILD WITH (ONLINE = ON)* |
* 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.
.gif "Observação") 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
O exemplo a seguir consulta a função de gerenciamento dinâmico sys.dm_db_index_physical_stats para retornar a fragmentação média de todos os índices da tabela Production.Product. Usando a tabela anterior, a resolução indicada é reorganizar PK_Product_ProductID e recriar os outros índices.
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
A instrução poderia retornar um conjunto de resultados semelhante ao que segue.
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
Além de reorganizar um ou mais índices, os tipos de dados LOB (Objetos Grandes) contidos no índice clusterizado ou na tabela subjacente são compactados por padrão quando o índice é reorganizado. Os tipos de dados image, text, ntext, varchar(max), nvarchar(max), varbinary(max) e xml são tipos de dados de objetos grandes. A compactação desses dados pode resultar em um melhor uso do espaço em disco:
Reorganizar um índice clusterizado especificado compactará todas as colunas LOB contidas no nível folha (linhas de dados) do índice clusterizado.
Reorganizar um índice não clusterizado compactará todas as colunas LOB que são colunas não-chave (incluídas) no índice.
Quando ALL é especificado, todos os índices associados à tabela especificada ou exibição são reorganizados e todas as colunas LOB associadas ao índice clusterizado, à tabela subjacente ou ao índice não clusterizado com colunas incluídas são compactadas.
A cláusula LOB_COMPACTION é ignorada quando não há colunas LOB.
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.
Funcionalidade |
ALTER INDEX REBUILD |
CREATE INDEX WITH DROP_EXISTING |
|---|---|---|
A definição de índice pode ser alterada pela adição ou remoção de colunas de chave, pela alteração da ordem das colunas ou pela alteração da ordem de classificação da coluna.* |
Não |
Sim** |
As opções de índice podem ser definidas ou modificadas. |
Sim |
Sim |
Mais de um índice pode ser recriado em uma única transação. |
Sim |
Não |
A maioria dos tipos de índices pode ser recriada online sem que haja bloqueio de consultas ou atualizações em execução. |
Sim |
Sim |
Um índice particionado pode ser reparticionado. |
Não |
Sim |
Um índice pode ser movido para outro grupo de arquivos. |
Não |
Sim |
É necessário espaço adicional temporário em disco. |
Sim |
Sim |
A recriação de um índice clusterizado recria os índices não clusterizados associados. |
Não Exceto se a palavra-chave ALL for especificada. |
Não Exceto se houver alteração da definição do índice. |
Os índices podem ser recriados pela imposição das restrições PRIMARY KEY e UNIQUE, sem cancelar e recriar as restrições. |
Sim |
Sim |
Uma partição única de índice pode ser recriada. |
Sim |
Não |
* 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:
PAD_INDEX |
DROP_EXISTING (somente CREATE INDEX) |
FILLFACTOR |
ONLINE |
SORT_IN_TEMPDB |
ALLOW_ROW_LOCKS |
IGNORE_DUP_KEY |
ALLOW_PAGE_LOCKS |
STATISTICS_NORECOMPUTE |
MAXDOP |
| 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:
ALLOW_PAGE_LOCKS |
IGNORE_DUP_KEY |
ALLOW_ROW_LOCKS |
STATISTICS_NORECOMPUTE |
Para obter mais informações, consulte Configurando opções de índice.
Para recriar ou reorganizar um índice
Para recriar um índice cancelando e recriando um índice em uma única etapa
Exemplos
A. Recriando um índice
O exemplo a seguir recria um índice único.
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
O exemplo a seguir especifica a palavra-chave ALL. Isso recria todos os índices associados à tabela. Três opções são especificadas.
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
O exemplo a seguir reorganiza um índice clusterizado único. Como o índice contém um tipo de dados LOB no nível folha, a instrução também compacta todas as páginas que contêm dados de objeto grande. Observe que não é preciso especificar a opção WITH (LOB_Compaction), uma vez que o valor padrão é ON.
USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO
Consulte também