Below is the script to find the fragmentation of the indexes created on a database.
SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
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
ALTER INDEXREORGANIZE
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.
Read more about Fragmentation
Read more about Indexes
Read more about Reorganize and Rebuild Indexes
SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
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
ALTER INDEXREORGANIZE
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.
Read more about Fragmentation
Read more about Indexes
Read more about Reorganize and Rebuild Indexes
