SQL Server Specific Rebuild Reorg

SQL Server Specific Rebuild Reorg

-- If avg_fragmentation_in_percent  > 5% and < = 30%  ALTER INDEX REORGANIZE 
-- If avg_fragmentation_in_percent  > 30%  ALTER INDEX REBUILD WITH (ONLINE = ON)   

--Check Fragmentation Level  
SELECT object_id                    AS objectid, 
       index_id                     AS indexid, 
       partition_number             AS partitionnum, 
       avg_fragmentation_in_percent AS frag 
FROM   sys.Dm_db_index_physical_stats (Db_id(N'<Database Name>'), 
              Object_id(N'<Table Name>'), NULL, NULL, 'LIMITED') 
WHERE  avg_fragmentation_in_percent > 10.0 
       AND index_id > 0; 

-----------------------------------------------------------------------

--Check Fragmentation Level 
SELECT * 
FROM   sys.Dm_db_index_physical_stats (Db_id(N'<Database Name>'), 
              Object_id(N'<Table Name>'), NULL, NULL, NULL); 

-----------------------------------------------------------------------

--Index Rebuild : This process drops the existing Index and Recreates the index. 
USE <Database Name>; 

go 

ALTER INDEX ALL ON dbo.<TableName> rebuild 

go 


-----------------------------------------------------------------------


--Index Reorganize : This process physically reorganizes the leaf nodes of the index. 
USE <TableName>; 

go 

ALTER INDEX ALL ON dbo.<TableName> reorganize 

go 
-----------------------------------------------------------------------