lcristiano,
Gostaria de dar minha contribuição:
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.
Identificando a fragmentação dos índices
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.
Veja este código abaixo, ele vai lhe ajudar a identificar índices fragmentados:
SELECT
OBJECT_NAME(B.object_id) AS TableName,
B.name AS IndexName,
A.index_type_desc AS IndexType,
A.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') A
INNER JOIN sys.indexes B WITH(NOLOCK) ON B.object_id = A.object_id AND B.index_id = A.index_id
WHERE
A.avg_fragmentation_in_percent > 30
AND OBJECT_NAME(B.object_id) NOT LIKE '[_]%'
AND A.index_type_desc != 'HEAP'
ORDER BY
A.avg_fragmentation_in_percent DESC
Este outro exemplo, vai lhe ajudar no processo de desfragmentação:
Desfragmentando um índice fragmentado (REORGANIZE)
ALTER INDEX SK01 ON dbo.Logins REORGANIZE
Desfragmentando todos os índices da tabela (REORGANIZE)
ALTER INDEX ALL ON dbo.Logins REORGANIZE
Desfragmentando todos os índices da tabela (REBUILD)
ALTER INDEX ALL ON dbo.Logins REBUILD
Desfragmentando todos os índices da tabela (REBUILD – COMPLETO)
ALTER INDEX ALL ON dbo.Logins
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON, ONLINE = ON)
Este outro exemplo mostra realmente quais índices estão sendo utilizados:
SELECT
ObjectName = OBJECT_SCHEMA_NAME(idx.object_id) + '.' + OBJECT_NAME(idx.object_id),
IndexName = idx.name,
IndexType = CASE WHEN is_unique = 1 THEN 'UNIQUE ' ELSE '' END + idx.type_desc,
User_Seeks = us.user_seeks,
User_Scans = us.user_scans,
User_Lookups = us.user_lookups,
User_Updates = us.user_updates
FROM
sys.indexes idx
LEFT JOIN sys.dm_db_index_usage_stats us ON idx.object_id = us.object_id AND idx.index_id = us.index_id AND us.database_id = DB_ID()
WHERE
OBJECT_SCHEMA_NAME(idx.object_id) != 'sys'
ORDER BY
us.user_seeks + us.user_scans + us.user_lookups DESC
Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
