Estou otimizando alguma tarefa de aplicativo de servidor que usa banco de dados (consultas, cálculos complexos, inserções de dados...). A execução desta tarefa gasta cerca de 16 minutos (testei mais 3 vezes) e é um tempo previsível para fazê-lo.
Então eu executei o script:
ALTER INDEX ALL ON dbo.'+ @TableName +' REBUILD
para cada tabela em um banco de dados. E o que vejo agora. O tempo de execução da minha tarefa é aumentado para 24 minutos. O que está acontecendo se não houver influências externas nesta tarefa? Eu estava esperando pelo aumento de desempenho (devido à reconstrução de índices fragmentados), mas obtive degradação.
É porque quando você reconstrói índices, você também necessariamente reconstrói as estatísticas de cada índice à medida que isso acontece. Isso significa que o SQL Server reconstrói "tabelas" internas de dados sobre a distribuição de dados em seus próprios índices. A distribuição de dados indicada por suas estatísticas determina como o otimizador de consulta baseado em custo escolhe criar planos para suas consultas.
Em essência, isso significa que o SQL Server provavelmente está executando suas consultas usando um plano diferente do anterior porque a distribuição de seus dados mudou.
Não há uma solução simples para isso. Envolve um verdadeiro trabalho de detetive. Comece com o SQL Server Profiler e tenha uma ideia de quais consultas estão realmente sendo executadas neste trabalho. Analise cada plano de consulta em detalhes e descubra quais partes da consulta estão levando mais tempo e ajuste sua consulta, esquema de indexação ou até mesmo seu esquema de acordo.
Além das alterações no plano de consulta, você também pode observar um desempenho de inserção ruim devido a divisões de página que não estavam ocorrendo antes da reconstrução do índice. Pode valer a pena olhar para fillfactor.
Basicamente, sugiro examinar qual parte da tarefa é lenta e isso deve fornecer algumas pistas.