SQL Check Frag Index

SQL Script to check index Fragmentation

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

  USE [yourDb]
  GO
  
  SELECT OBJECT_NAME(ips.OBJECT_ID) AS TableName
      ,i.NAME AS IndexName
      ,ips.index_id AS IndexId
      ,index_type_desc AS IndexType
      ,avg_fragmentation_in_percent AS FragPercent
      ,avg_page_space_used_in_percent AS AvgPageUsedPercent
      ,page_count AS PageCount
  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
  INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
  AND (ips.index_id = i.index_id)
  ORDER BY avg_fragmentation_in_percent DESC