locked
How to defragment database? RRS feed

  • Question

  • I have 3 ASP.NET websites hosted at www.serverintellect.com all sharing a common database, dbABC.

    I recently contacted them because according to www.loadimpact.com, all of the sites were taking an average of at least 1 minute to load if there were more than 25 web visitors at once.

    Server Intellect support said:

    "Hello,

    At this time, we have ran and attached a SQL Server database fragmentation report on your database "dbABC."

    We see that your following sites are currently utilizing this database:

    www,letsomebodyknow.com
    gertes.com
    [Our other site].com

    The report details numerous tables that are fragmented, and since this is the case, all 3 sites' performance will be decreased.

    We suggest contacting your database administrator to resolve the fragmentation in order to correct this issue.

    If you have any questions at all, please feel free to contact us.

    Regards,
    Technical Support"

    So my question is, how to I defragment the database? Having used MSAccess before, I used to just select "Compact" from the menu before and it did it. I haven't been able to find a similar menu item in SQL Server 2005 Express though. Any help much appreciated.

    Mark

    Thursday, March 8, 2012 9:50 PM

Answers

  • Hi,

    Do your tables have clustered indexes or are they heaps? To remove fragmentation you generally want to regularly run index rebuilds or index re-organisations depending on how heavily fragmented your indexes are. There is no menu/GUI option to do this.

    I would recommend using Ola's scripts for maintenance.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Naomi N Friday, March 9, 2012 3:51 AM
    • Marked as answer by Mark B 123 Tuesday, March 13, 2012 12:25 AM
    Thursday, March 8, 2012 10:33 PM
  • Please follow this link to have a clear idea about defragmentation.

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    • Marked as answer by Mark B 123 Tuesday, March 13, 2012 12:26 AM
    Friday, March 9, 2012 5:10 AM

All replies

  • Hi,

    Do your tables have clustered indexes or are they heaps? To remove fragmentation you generally want to regularly run index rebuilds or index re-organisations depending on how heavily fragmented your indexes are. There is no menu/GUI option to do this.

    I would recommend using Ola's scripts for maintenance.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Naomi N Friday, March 9, 2012 3:51 AM
    • Marked as answer by Mark B 123 Tuesday, March 13, 2012 12:25 AM
    Thursday, March 8, 2012 10:33 PM
  • Please follow this link to have a clear idea about defragmentation.

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    • Marked as answer by Mark B 123 Tuesday, March 13, 2012 12:26 AM
    Friday, March 9, 2012 5:10 AM