Como verificar e resolver a fragmentação de índices no SQL Server

Quando um banco de dados é atualizado com frequência por meio de instruções INSERT, UPDATE ou DELETE, podemos esperar que ele se torne fragmentado ao longo do tempo.
Se os índices do banco de dados estiverem fragmentados, o otimizador de consultas do SQL Server poderá escolher um plano de execução não ideal ao usar um índice para resolver uma consulta.
Isso afetará o desempenho geral da consulta e você poderá observar uma consulta se comportando mais lentamente do que o normal, existe casos em que uma consulta pode levar mais de 500% do tempo por causa de um plano de execução errado através de índice fragmentado.

Resolução
* Aviso: Podem ocorrer danos irreversíveis ao banco de dados. Esse procedimento deve ser executado apenas por usuários familiarizados com o SQL Server Management Studio. Deve-se fazer o backup dos bancos de dados antes de executar este procedimento. *

A seguir, uma consulta simples que listará todos os índices em todas as tabelas do banco de dados, ordenadas por porcentagem de fragmentação do índice, perceba que a query poderá levar alguns minutos para executar dependendo da quantidade de índices e a fragmentação dos mesmos:

 SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

-- adicionar clausula para buscar uma tabela em específico
-- WHERE indexstats.database_id = DB_ID() AND dbtables.[ name ] like '%%' 

Para reduzir a fragmentação, teremos que reorganizar ou reconstruir os índices. Escolher entre reorganizar (reorganize) ou reconstruir (rebuild) depende dos resultados da consulta. Para índices altamente fragmentados, é necessário um processo de reconstrução, caso contrário, a reorganização do índice deve ser suficiente.

A recompilaçã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. Veja o exemplo:

--reorganizar um índice específico
ALTER INDEX IX_NAME
  ON dbo.Employee  
REORGANIZE ;   
GO  

-- reorganizar todos os índices de uma tabela
ALTER INDEX ALL ON dbo.Employee  
REORGANIZE ;   
GO  

-- rebuild de um índice específico
ALTER INDEX PK_Employee_BusinessEntityID ON dbo.Employee
REBUILD;

-- rebuild de todos índice de uma tabela
ALTER INDEX ALL ON dbo.Employee
REBUILD;

Lembre-se que o comando rebuild por padrão gera lock na tabela e leva bem mais tempo do que o reorganize. Por isto tenha cuidado ao realizar estas operações dentro do horário produtivo do seu software.

Fonte: https://docs.microsoft.com/pt-br/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017

Um grande abraço a todos!