check index fragmentation in sql server | Index Fragmentation Sql Query | Get Index Fragmentation Report

hi below is the code to get the fragmentation report in a database.

SELECT OBJECT_NAME(sindex.OBJECT_ID) AS TableName
,sindex.NAME AS CreatedIndexName
,ips.index_type_desc AS IndexType
,sindex.fill_factor AS Fill_Factor
,ips.fragment_count AS fragment_count
,ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ips
INNER JOIN sys.indexes sindex ON sindex.object_id = ips.object_id
AND sindex.index_id = ips.index_id
ORDER BY ips.avg_fragmentation_in_percent DESC


For fragmentation more than 30 use Rebuild Index and for fragmentation between 5 to 30 use Reorganize index.

Examples:
ALTER INDEX ALL ON NORTHWND.dbo.Customers
REORGANIZE ; 
GO

ALTER INDEX ALL ON NORTHWND.dbo.Customers
REBUILD;
GO

ALTER INDEX ALL ON NORTHWND.dbo.Customers
REBUILD WITH (FILLFACTOR = 80);
GO

Note:
1. Rebuilding index happens online and offline. During Offline the database resources gets locked.
2. Reorganizing index always happens online.