I am getting confused with choosing Index Reorganize/Rebuilding of indexes based on avg_fragmentation_in_percent returned by sys.dm_db_index_physical_stats
dm function.
Msdn
says:
And also, avg_fragmentation_in_percent
value says percentage of logical fragmentation (This is the percentage of out-of-order pages in the leaf pages of an index) existed in index.
Reorganize of index
will always fixes physical ordering of pages. so even avg_fragmentation_in_percent is greater than 30% also I can consider Reorganize of index. It will be decreasing the percentage of fragmentation in index.
Please look below example
SELECT
database_id, object_id,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
page_count,
avg_fragment_size_in_pages
FROM
SYS.Dm_db_index_physical_stats (Db_id('BPIGTN_GAL_APP_TST'), Object_id('NM_PPA_PROJECTION_MASTER'), NULL, NULL, 'SAMPLED')
Result
Database_id Object_id avg_fragmentation_in_percent avg_page_space_used_in_percent page_count avg_fragment_size_in_pages
37 913490383 99.36 60.15 314 1.003
In above example, We can see 99 % of average fragmentation
it means index is having more logical fragmentation(Unordering of pages is more).
So I am going to reorganizing of index which we have seen in above image.
Please look below example for further.
ALTER INDEX PK_NM_PPA_PROJECTION_MASTER_PROJECTION_DETAILS_SID_RS_CONTRACT_SID ON NM_PPA_PROJECTION_MASTER REORGANIZE
GO
SELECT database_id,
object_id,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
page_count,
avg_fragment_size_in_pages
FROM SYS.Dm_db_index_physical_stats (Db_id('BPIGTN_GAL_APP_TST'), Object_id('NM_PPA_PROJECTION_MASTER'), NULL, NULL, 'SAMPLED')
Result
Database_id Object_id avg_fragmentation_in_percent avg_page_space_used_in_percent page_count avg_fragment_size_in_pages
37 913490383 4.18 98.91 191 9.55
Above, we have re-organized
the index so after reorganize average_fragmentation
reduced to 4 percent
and also space used is increased from 60 percent to 98 percent
.
Msdn Suggesting Avg_Fragmentation_in_percent value to be consider for Reorganize/Rebuild
of index.
But Avg_Fragmentation_In_Percent value says only logical fragmentation not amount of space which is not used in index.
So, I am a little bit confused about choosing between Re-organize/Re-build of index.
Can we consider both Avg_fragmentation_in_percent and Avg_page_space_used_in_percent values for choosing Rebuild an index or Reorganize an index?
Any one please suggest me the exact parameters to be consider for choosing Rebuild or Re-Organize of an index?