SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
FROM sys.dm_db_index_physical_stats (DB_ID( N’Database name’) , NULL, NULL, NULL , ‘SAMPLED’) ORDER BY avg_fragmentation_in_percent DESC
avg_fragmentation_in_percent represents logical fragmentation.
If this value is higher than 5% and less than 30%, then we should use
If this value is higher than 30%, then we should use
ALTER INDEX REBUILD WITH (ONLINE = ON)
Please note:- As per MSDN, rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.