Fragmentation of Index occurring frequently RRS feed

  • Question

  • Hi,

    I have a set of tables (4 in precise) on which transactions happen frequently(DML). As a part of the performance enhancement, i have de-fragmented all the indexes on these tables. Thereafter my queries started running faster for few hours. Unfortunately, the indexes were again fragmented and average_fragmentation_in_percent got up-to 90%  by end of the same day.'What would be the reason?

    Please suggest.

    Note: We haven't shrinked the database.


    Friday, January 4, 2013 9:23 AM


All replies

  • You need to set an ideal value for the fill factor,  please read this link 



    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by Sri Charan Wednesday, January 9, 2013 5:13 AM
    Friday, January 4, 2013 9:28 AM
  • As mentioned, the fill factor may solve your issue.  However if you post your DDL and example DML then there may be other changes that can help. 
    Friday, January 4, 2013 10:05 AM
  • Reason is you table get very frequently the insert ,Delete commends so the table is very quickey get fragmented to over come this introduce fill factor. but fill factor also helps your for two to three days in your senerio. but your de-Fragmentation work will be minimized here

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, January 4, 2013 10:40 AM
  • Also ensure that you do not do any SHRINKFILE on the database post Index Rebuilding. By the way, what version of SQL are you using? If > 2005, are you using sys.dm_db_index_physical_stats to check for degragmentation and how are you rebuilding the indexes (ALTER INDEX/ DBCC Commands)
    Friday, January 4, 2013 10:41 AM
  • Thank you all for the responses.  I'm using SQL Server 2008 R2. I'm using ALTER INDEX --- REBUILD command to de-fragment the indexes. And i remember not  to use DBCC SHRINK operations post re-indexing.

    Friday, January 4, 2013 11:12 AM
  • Where is the defragementation. Is it at the leaf level or the intermediate levels?
    Friday, January 4, 2013 11:59 AM
  • Defragmented at the index 0 level, which is root level.
    Friday, January 4, 2013 12:38 PM
  • Just one off question. Is the clustering key a UNIQUEIDENTIFIER by any chance?
    Friday, January 4, 2013 12:40 PM
  • Nope...its a varchar column
    Friday, January 4, 2013 12:41 PM
  • Fragmentation happens when a new row or updated version of an existing row does not fit on the page on which it has to go.

    Fragmentation caused by inserts can happen if you have meaningless (semi)random index key, such as a uniqueidentifier. If this is the case, you could consider replacing with an Identity value with appropriate size (int or maybe bigint).

    Fragmentation caused by updates point to an inappropriate fill factor for the index, and can be countered by increasing the fill factor.

    Please not that (from a performance point of view) fragmentation is meaningless for tables with just a few hundred pages.


    • Proposed as answer by Naomi N Sunday, January 6, 2013 3:23 PM
    Sunday, January 6, 2013 9:49 AM