Script to find the Fragmentation in Indexes
In my previous articles, Fragmentation in SQL Server and Rebuild And Reorganization of Indexes , we talk in detail about Index fragmentation and rebuild or reorganize the indexes. Below is the SQL script which we can use to find the Fragmentation in Indexes created on the 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
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.
Re-commentated Articles On Indexes