SQL'de İsme Göre Index Fragmentation Kontrolü

Aşağıdaki sorgu ile isme göre Index kontrolü sağlayabilirsiniz. Sorgu içerisindeki alanı düzenleyerek aramak istediğiniz index ismini yazabilirsiniz.

Tablo Bazında Index Fragmentationu

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 avg_fragmentation_in_percent BETWEEN 30 AND 100

AND dbindexes.[name] LIKE '%ORFLINE%' – Kontrol Edilen Tablo

--WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent desc