none
Why most of the index's avg_fragmentation_in_percent doesn't reduce after reorganize / rebuild is performed

    Question

  •  

    I am build up a maintenance plan to reorganize and rebuild the index of one database. After that maintenance plan is performed, i found that most of indexes's avg_fragmentation_in_percent doesn't reduce. Is that any efficiency way to reduce fragmentation of the indexes?
    Tuesday, September 18, 2007 5:59 AM

Answers

  • I think the cause of your constant fragmentation is fill factor option of the indexes. I quote from Books Online :

    "

    When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations. A correctly chosen fill factor value can reduce the potential for page splits by providing enough space for index expansion as data is added to the underlying table.

    "

    and

    "

    Although a low fill factor value, other than 0, may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance

    "

     

    So , i should make index rebuilding with a lower fill factor (at reorganizing it can not be changed).

     

     

     

     

    Tuesday, September 18, 2007 6:52 AM
  •  

    How many pages are in the index?  Check the page_count column in the sys.dm_db_index_physical_stats function.   If there are fewer than 1000 pages, rebuilding the index is not going to change the fragmentation percentage reported by the function, there just aren't enough pages.

     

    Regards,

    Gail

    Tuesday, November 06, 2007 11:25 PM
  • So is the fragmentation ok if the page count is low?
    Yes. Actually fragmentation on an index with 500 or 1000 page counts is quite less in cost as compared to what it would take for rebuilding them. 

    This posting is provided “AS IS” with no warranties, and confers no rights.

    If this reply answers your question, please mark it as Answered for others to find it easily.
    If this reply help you resolving the problem, please vote the post as Helpful.

    Wednesday, October 31, 2012 5:52 PM

All replies

  • I think the cause of your constant fragmentation is fill factor option of the indexes. I quote from Books Online :

    "

    When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations. A correctly chosen fill factor value can reduce the potential for page splits by providing enough space for index expansion as data is added to the underlying table.

    "

    and

    "

    Although a low fill factor value, other than 0, may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance

    "

     

    So , i should make index rebuilding with a lower fill factor (at reorganizing it can not be changed).

     

     

     

     

    Tuesday, September 18, 2007 6:52 AM
  • I am experiencing this after an index rebuild.  I don't believe its caused by page splits as I'm not changing the data in the table at all after the rebuild and yet "show avg_fragmentation_in_percent" remains at 50 percent???

    Tuesday, November 06, 2007 3:51 PM
  •  

    How many pages are in the index?  Check the page_count column in the sys.dm_db_index_physical_stats function.   If there are fewer than 1000 pages, rebuilding the index is not going to change the fragmentation percentage reported by the function, there just aren't enough pages.

     

    Regards,

    Gail

    Tuesday, November 06, 2007 11:25 PM
  • I agree with you
    Wednesday, July 25, 2012 6:07 AM
  • So is the fragmentation ok if the page count is low?
    Wednesday, October 31, 2012 3:32 PM
  • So is the fragmentation ok if the page count is low?
    Yes. Actually fragmentation on an index with 500 or 1000 page counts is quite less in cost as compared to what it would take for rebuilding them. 

    This posting is provided “AS IS” with no warranties, and confers no rights.

    If this reply answers your question, please mark it as Answered for others to find it easily.
    If this reply help you resolving the problem, please vote the post as Helpful.

    Wednesday, October 31, 2012 5:52 PM