Depois de pesquisar na internet não consegui encontrar o motivo
Por que precisamos reconstruir e reorganizar índices no SQL Server?
o que acontece internamente quando reconstruímos e reorganizamos?
Um artigo em um site diz:
O índice deve ser reconstruído quando a fragmentação do índice for maior que 40%. O índice deve ser reorganizado quando a fragmentação do índice estiver entre 10% e 40%. O processo de reconstrução de índice usa mais CPU e bloqueia os recursos do banco de dados. A versão de desenvolvimento do SQL Server e a versão Enterprise possuem a opção ONLINE, que pode ser ativada quando o Index é reconstruído. A opção ONLINE manterá o índice disponível durante a reconstrução.
Não consegui entender isso, embora diga que WHENpara fazer isso, mas gostaria de saber WHYse precisamos reconstruir e reorganizar os índices?
À medida que você executa atualizações e exclusões de inserções, seus índices ficarão fragmentados interna e externamente.
A fragmentação interna é que você tem uma alta porcentagem de espaço livre em suas páginas de índice, o que significa que o SQL Server precisa ler mais páginas ao verificar o índice.
A fragmentação externa é quando as páginas do índice não estão mais em ordem, então o SQL Server tem que fazer mais trabalho, especialmente em termos de E/S para ler o índice.
Se seus índices se tornarem muito fragmentados, na melhor das hipóteses, suas consultas serão menos eficientes, mas na pior das hipóteses, o SQL Server simplesmente deixará de usar os índices todos juntos, o que significa que praticamente todas as consultas teriam que executar uma verificação de tabela ou verificação de índice clusterizado. Isso vai prejudicar muito o seu desempenho!
Quando você reorganiza um índice, o SQL Server usa as páginas de índice existentes e apenas embaralha os dados nessas idades. Isso aliviará a fragmentação interna e também pode remover uma pequena quantidade de fragmentação externa. É uma operação mais leve do que a reconstrução e está sempre online.
Quando você recria um índice, o SQL Server, na verdade, recorre aos dados do índice e usa um novo conjunto de páginas de índice. Isso obviamente aliviará a fragmentação interna e externa, mas é uma operação mais pesada e, por padrão, faz com que o índice fique offline, embora possa ser executado como uma operação online, dependendo da versão e das configurações do SQL Server.
No entanto, não espere ter 0 fragmentação após uma reconstrução. A menos que você use uma dica de consulta MAXDOP, o SQL Server paralelizará a operação de reconstrução e quanto mais processadores envolvidos, mais fragmentação haverá, porque cada processador ou núcleo reconstruirá sua seção ou fragmento do índice individualmente, sem considerar uns aos outros. Essa é uma compensação entre os melhores níveis de fragmentação e o tempo necessário para reconstruir o índice. Para fragmentação próxima de 0, use MAXDOP 1 e classifique os resultados em TempDB.
Para remover a fragmentação que causa problemas como lentidão no nível do banco de dados/consultas de longa duração etc.
Para obter uma compreensão detalhada sobre a fragmentação e como reconstruir e reorganizar índices (ou reorganizar páginas de índice) para isso, consulte o link abaixo: https://www.idera.com/productssolutions/sqlserver/sqldefragmanager/what-is-fragmentation