SQL Server Performance

IndexRebuliding

Discussion in 'Getting Started' started by rashmi_1234, Feb 13, 2011.

  1. rashmi_1234 New Member

    What is the reason for fragmentation to increase even after rebuilding.
  2. FrankKalis Moderator

    Welcome to the forum!
    Users working with the database? [:)]
    Every data modification operation that has to be reflected in an index might contribute to fragmentation. So, as soon as you have finished your rebuilding and users start working with the database, modify this and that, fragmentation is likely to increase over time again.
  3. satya Moderator

    Welcome to the forums.
    As Frank referred, the usage of data will also lead to fragmentation and when the data is inserted/deleted/updated all the indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. All leaf pages of an index contain pointers to the next and the previous pages in the index. The fragmentation occurs on physical disk too in addition to database pages.
    See the other related links on this toipc:
    http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx
    http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx
  4. mmarovic Active Member

    Besides normal users/application activity, heavy fragmentation can be caused by (auto)shrinking database/data files.
  5. rashmi_1234 New Member

    Hi
    Thanks for the replies. But the question that i asked was:-
    We ran index rebuilding jobs and that gave status of fragmentation.
    And when we get the status many times it shows that just before index rebuilding the fragmentation was less and just after index rebuilding fragmentation increased.I have to explain the reaon to the users.What is the actual problem???
  6. FrankKalis Moderator

    Most likely you'll observe this for objects with only a few pages. Check this answer from Paul Randal why it is not worth it to care about index maintenance for such objects.
  7. satya Moderator

    Can you get those object names where the higher fragmentation or miscalculated values are shown?
  8. MikaS New Member

    Hi,Does your table have a clustered index, or is it heap table? As far as I know the only way to remove fragmentation from heap table is to build a clustered index in it and then drop it. While you can use rebuild and reorganize on heap table, it doesn't do much with there being no logical order for the data.

Share This Page