Indexes getting fragmented very frequently

  • Hello,

    we have sharepoint databases in sql server 2005.For particularly 2 databases indexes are getting fragment approaximately 30 mins.

    The databases are sharedservices_search and content. I have no idea how this share point search works?

    In this case how often we need reorganize or rebuild the databases?

    is there any script that continuously collects the fragmented indexes,statictics and based on the fragmentation level it automatically reorganizes or rebuild the indexex and updates the statistics?

    Index_nameTable_name LogicalScanFragmentation Pages size

    IX_AlertDocHistory dbo.MSSAlertDocHistory 73.83 % 1559 12.2 MB

    IX_Link dbo.MSSAnchorText 99.20 % 249 1.9 MB

    IX_MSSURLHistory_AccessHashdbo.MSSCrawlURL 99.26 % 136 1.1 MB

    IX_MSSURLHistory_DisplayHashdbo.MSSCrawlURL 99.26 % 136 1.1 MB

    IX_MSSCrawlURL_CompactHashdbo.MSSCrawlURL 97.06 % 170 1.3 MB

    thank you very much

  • Hello,

    Have you considered adjusting the Fill Factor for these Indexes? May be take a look at this article:

    http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1119108,00.html

    BTW – Do these Indexes include columns that contain (Non-Sequential) GUIDs?

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Is autoshrink on for that database?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • No, Autoshrink for the database is set false.all options are set to default values

  • What's the Fill Factor for these offending indexes?

    Either way, you may want to check the next note about how to defrag SharePoint indexes, here http://support.microsoft.com/kb/943345

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • It's been my experience that small files, such as the ones you are working with, will often have fragmentation based on their size. I have tested this out by trying to defrag a small index numerous times, and the fragmentation level stays the same.

    Unfortunately, I don't recall the URL where I initially read about that.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply