Analyzing Page_count and fragmentation
percentage of indexes
I described in my last blog how to get fragmentation level of any indexes and
rebuild or reorganize according to their fragmentation percentage. here I am
explaining Page_count and fragmentation percentage and showing relation between
them.
Run below query
select * from
sys.dm_db_index_physical_stats(db_id(),object_id('table_name'),null,null,null)
It will return you page_count and fragmentation level both. This column has is
important to access the fragmentation of the index. If Page_Count is less than
1000, you may ignore the avg_fragmentation_in_percent value.
The (general)rule is as follows:
If the page count is more than 1000
If fragmentation is less than 5 % - Leave as it is
If fragmentation is more than 5 % and less than 30% -
Reorganize the index
If fragmentation is more than 30% - Rebuild index
Else leave it
I think this is a good practice to use page_count and fragmentation percentage.