January 2, 2009 at 4:28 pm
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
January 2, 2009 at 11:33 pm
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
January 3, 2009 at 7:34 am
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
January 3, 2009 at 11:57 am
No, Autoshrink for the database is set false.all options are set to default values
January 3, 2009 at 12:09 pm
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.January 3, 2009 at 12:33 pm
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