SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script

Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

INDEX Fragmentation Report - More than 60%

SELECT db_name(ps.database_id) AS DBName, S.name AS Schemaname,
object_name(ps.OBJECT_ID) AS Tablename,
Index_Description = CASE
WHEN ps.index_id = 1 THEN 'Clustered Index'
WHEN ps.index_id <> 1 THEN 'Non-Clustered Index'
END,
b.name AS Indexname,
ROUND(ps.avg_fragmentation_in_percent,0,1) AS 'Fragmentation%',
SUM(page_count*8) AS 'IndexSizeKB',
ps.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(),NULL,NULL,NULL,NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.object_id = b.object_id AND ps.index_id = b.index_id AND b.index_id <> 0 -- heap not required
INNER JOIN sys.objects AS O ON O.object_id=b.object_id AND O.type='U' AND O.is_ms_shipped=0 -- only user tables
INNER JOIN sys.schemas AS S ON S.schema_Id=O.schema_id
WHERE ps.database_id = DB_ID() AND ps.avg_fragmentation_in_percent > 60 -- Indexes having more than 60% fragmentation
GROUP BY db_name(ps.database_id),S.name,object_name(ps.OBJECT_ID),CASE WHEN ps.index_id = 1 THEN 'Clustered Index' WHEN ps.index_id <> 1 THEN 'Non-Clustered Index' END,b.name,ROUND(ps.avg_fragmentation_in_percent,0,1),ps.avg_fragmentation_in_percent,ps.page_count
ORDER BY ps.avg_fragmentation_in_percent DESC