Quando devo reconstruir índices?

Correndo o risco de ser muito geral em minha resposta, direi que você deve executar um processo de manutenção de índice regularmente. No entanto, seu processo de manutenção de índice deve apenas reconstruir/reorganizar os índices que o exigem especificamente.

Isso apresenta a questão: quando um índice precisa ser reconstruído ou reorganizado? Rolando tocou bem nisso. Mais uma vez, corro o risco de ser extremamente amplo. Um índice requer manutenção quando o nível de fragmentação afeta negativamente o desempenho. Esse nível de fragmentação pode variar com base no tamanho e na composição do índice.

Falando em SQL Server, costumo escolher um tamanho de índice e um nível de fragmentação de índice no qual começo a realizar a manutenção do índice. Se um índice contiver menos de 100 páginas, não farei manutenção.

Se um índice estiver entre 10% e 30% fragmentado, vou fazer REORGANIZEo índice e UPDATEas estatísticas. Se um índice estiver mais de 30% fragmentado, eu irei REBUILDo índice - sem UPDATE STATISTICS, pois isso é cuidado pelo REBUILD. Lembre-se, porém, de que uma reconstrução atualiza apenas o objeto de estatísticas diretamente associado ao índice. Outras estatísticas de coluna precisarão ser mantidas separadamente.

Esta resposta é realmente apenas um longo caminho para dizer: Sim, você deve fazer manutenção de índice de rotina, mas apenas nos índices que precisam dela.

Quando devo reconstruir os índices em meu banco de dados relacional (por exemplo, SQL Server)?

Você deve reconstruir os índices quando eles se tornarem altamente fragmentados por eventos especiais. Por exemplo, você executa um grande carregamento em massa de dados em uma tabela indexada.

Existe um caso para reconstruir índices regularmente?

E daí que seus índices estão se fragmentando regularmente devido à atividade regular? Você deve agendar reconstruções regulares? Com que frequência eles devem correr?

Tom Kyte , neste tópico clássico do Ask Tom , recomenda:

O intervalo de tempo entre as recompilações de índice deve ser aproximadamente PARA SEMPRE.

...

Não sei como dizer melhor - o índice quer ser grande e gordo com espaço extra. Está em uma coluna que você atualiza -- movendo a entrada do índice de um lugar para outro no índice. Um dia a linha tem um código de "A", no dia seguinte o código é "G", depois "Z" depois "H" e assim por diante. Portanto, a entrada de índice para a linha se move de um lugar para outro no índice. Ao fazê-lo, ele precisa de espaço -- vai, se o espaço não estiver lá, dividimos o bloco em dois -- e criamos espaço. Agora o índice está ficando gordo. Com o tempo, o índice é 2-3x o tamanho que tinha quando você começou e está "meio ou mais vazio" Mas tudo bem, já que você move as linhas. Agora, quando movemos as linhas, não precisamos mais dividir os blocos para abrir espaço - o espaço já está disponível.

Então você vem e reconstrói ou descarta e recria o índice (que tem os mesmos efeitos - apenas a reconstrução é "mais segura" - não tem chance de perder o índice e pode ser mais rápido, pois o índice pode ser reconstruído por escanear o índice existente em vez de escanear a tabela e ordenar e construir um novo índice). Agora, todo aquele espaço agradável se foi. Começamos o processo de dividir os blocos novamente -- nos levando de volta ao ponto de partida.

Você não economizou espaço.

O índice está de volta do jeito que era.

Você estaria apenas perdendo seu tempo para reconstruí-lo novamente, fazendo com que esse ciclo vicioso se repetisse.

A lógica aqui é boa, mas é tendenciosa contra um perfil de carga pesada de leitura.

Um índice "gordo" (ou seja, um com muitas lacunas) de fato mantém uma boa quantidade de espaço para linhas novas e movidas, reduzindo assim as divisões de página e mantendo suas gravações rápidas. No entanto, quando você lê esse índice gordo, terá que ler mais páginas para obter os mesmos dados, porque agora está vasculhando mais espaço vazio. Isso atrasa suas leituras.

Portanto, em bancos de dados de leitura intensa, você deseja reconstruir ou reorganizar regularmente seus índices. (Com que frequência e sob quais condições? Matt M já tem uma resposta concreta para essa pergunta.) Em bancos de dados que experimentam atividade de leitura e gravação aproximadamente equivalente, ou em bancos de dados com muita gravação, você provavelmente está prejudicando o desempenho do banco de dados ao reconstruir índices regularmente.

A maioria das pessoas os reconstrói regularmente para que nunca fiquem fragmentados. Quando você precisa reconstruí-los é baseado na rapidez com que eles são fragmentados. Alguns índices precisarão ser reconstruídos com frequência, outros basicamente nunca. Confira o script que o SQLFool montou que lida com muitas coisas para você.

Conforme observado na resposta aceita de Matt M, uma regra prática comum é que os índices com mais de 30% de fragmentação devem ser reconstruídos.

Esta consulta irá ajudá-lo a descobrir quantos índices você tem que estão mais de 30% fragmentados (quando você tem alguns, você deve reconstruí-los):

SELECT DB_NAME() AS DBName,
       OBJECT_NAME(ind.object_id) AS TableName,
       ind.name AS IndexName,
       indexstats.index_type_desc AS IndexType,
       indexstats.avg_fragmentation_in_percent,
       indexstats.fragment_count,
       indexstats.avg_fragment_size_in_pages,
       SUM(p.rows) AS Rows 
  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
         INNER JOIN sys.indexes AS ind ON (    ind.object_id = indexstats.object_id
                                           AND ind.index_id = indexstats.index_id)
         INNER JOIN sys.partitions AS p ON (    ind.object_id = p.object_id
                                            AND ind.index_id = p.index_id)
 WHERE indexstats.avg_fragmentation_in_percent > 30
 GROUP BY
       OBJECT_NAME(ind.object_id),
       ind.name,
       indexstats.index_type_desc,
       indexstats.avg_fragmentation_in_percent,
       indexstats.fragment_count,
       indexstats.avg_fragment_size_in_pages 
 ORDER BY indexstats.avg_fragmentation_in_percent DESC

Quando devo reconstruir índices?

Quando a porcentagem de fragmentação do índice for superior a 30%.

Existe um caso para reconstruir índices regularmente?

Não existe esse caso, mas em geral, fazer a Manutenção do Índice uma vez por semana, no fim de semana, é a melhor prática para manter o ambiente estável.

Eu recomendaria usar scripts de manutenção de Ola Hallengren (melhores scripts de manutenção), personalizar os scripts com base no seu ambiente e agendá-los para serem executados no fim de semana.

https://ola.hallengren.com/

Nota: Não se esqueça de atualizar as estatísticas após reconstruir os índices, porque a reconstrução dos índices não atualiza todas as estatísticas.

Como a maioria das coisas em TI, depende. Qual problema você está tentando corrigir reconstruindo índices? Você pode mostrar que isso realmente resolve o problema? Nesse caso, ajuste os números até encontrar a menor quantidade de manutenção necessária para corrigir o problema.

Se isso não resolver o problema, ou o motivo pelo qual você está fazendo isso é apenas para apaziguar alguma métrica que você monitora porque pode melhorar as coisas, tudo o que você está fazendo é queimar CPU e E/S e possivelmente piorar seu problema.

Há um argumento de que corrigir a fragmentação não fará nenhuma diferença para o seu servidor, então vale a pena fazer isso regularmente?

https://www.brentozar.com/archive/2017/12/index-maintenance-madness/

http://brentozar.com/go/defrag