Analyzing sys.dm_db_index_physical_stats results.
Hi
In this post i am analyzing the "sys.dm_db_index_physical_stats" function results by with some production data.
By seeing this we can come to below conclusions.
First condition is
1) Focus on those rows which has page_count >1000 on those rows you can come to below conclusions.
In this post i am analyzing the "sys.dm_db_index_physical_stats" function results by with some production data.
By seeing this we can come to below conclusions.
First condition is
1) Focus on those rows which has page_count >1000 on those rows you can come to below conclusions.
If fragmentation(avg_fragmentation_in_percent) is less than 5 % (avg_fragmentation_in_percent)--> Leave as it is
If fragmentation(avg_fragmentation_in_percent) is more than 5 % and less than 30% - Reorganize the index
If fragmentation(avg_fragmentation_in_percent) is more than 30% - Rebuild index
and PageDencity we can caculated based on "avg_page_space_used_in_percent" column but here it is very good and it nearly 80 percent. You have to check for those columns which has page_count is >1000. the avg_fragmentation_in_percent value should be as close to zero as possible, and the avg_fragment_size_in_pages should be as high as possible. And also there are 2 more columns which we need to consider
- Avg_fragmentation_in_percent – depicts logical fragmentation
- Avg_page_space_used_in_percent – depicts internal fragmentation
Comments