locked
Fragmentation issues when migrating from SQL 2005 to 2008 RRS feed

  • Question

  • After we have migrated from 2005 to 2008 we have very high fragmentation. If we rebuild the indexs the fragmentation is fine, however, after about 10 days the fragmention is 90-98% which degrades performance greatly.

    The way we use the databse has not changed and we did not see these issues with 2005. What would cause the fragmentation to increase so quickly in 2008?

    Let me know what information you would want to help resolve this and I can post it.

    Thanks.

    Wednesday, March 23, 2011 3:33 PM

Answers

  • How frequently did you rebuild indexes in sql 2005 compared to sql 2008? If you have many insert/update/delete operations your indexes can get fragmented. You could create a job  and to check for fragmented indexes and run a rebuild or reorganize based on fragmentation. You could run the job every day if you have low database activity.

    What edition of SQL 2008 are you using? If you have enterprise edition you can rebuild indexes online.

    Thanks

     

    • Proposed as answer by WeiLin Qiao Saturday, March 26, 2011 9:20 AM
    • Marked as answer by WeiLin Qiao Monday, April 4, 2011 1:47 AM
    Wednesday, March 23, 2011 3:48 PM

All replies

  • How frequently did you rebuild indexes in sql 2005 compared to sql 2008? If you have many insert/update/delete operations your indexes can get fragmented. You could create a job  and to check for fragmented indexes and run a rebuild or reorganize based on fragmentation. You could run the job every day if you have low database activity.

    What edition of SQL 2008 are you using? If you have enterprise edition you can rebuild indexes online.

    Thanks

     

    • Proposed as answer by WeiLin Qiao Saturday, March 26, 2011 9:20 AM
    • Marked as answer by WeiLin Qiao Monday, April 4, 2011 1:47 AM
    Wednesday, March 23, 2011 3:48 PM
  • What frequency were your maintenance jobs in 2005? do you migrate them to  2008 or not?

    Nothing to worry too much about the fragmentation, just do regular update/rebuild.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    http://twitter.com/7Kn1ghts
    Wednesday, March 23, 2011 4:01 PM
  • You might need to change your frequency and re-schedule maitenance plan to rebuild / reindexes your table.

    Worth to check what is your indexing fillfactor (Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0. )

    for more please check here http://technet.microsoft.com/en-us/library/ms188388.aspx

    Wednesday, March 23, 2011 4:56 PM