0

We are running SP2013 on SQL Server 2014 (setup with a Available Group) and we use a tool called SQL spotlight to monitor the health of our SQL servers. It constantly complains that many of the indexes in WSS_Logging are heavily (up to 100%) fragmented with page counts ~2000. The other DBs are fine. From reading the documentation I thought SharePoint handled re-indexing itself with timer jobs. Should I be creating a maintenance plan to handle this or not worry about it?

1

The rules used by the Timer job:

Sampled mode
Page count >24 and avg fragmentation in percent >5
Or
Page count >8 avg page space used in percent < fill_factor * 0.9 (Fill Factor in SharePoint 2013 varies from 80 to 100 depending on the index, it is important not to adjust index fill factors)

I have seen cases where the indexes are not automatically managed by the rule and require a manual defragmentation with a Full Scan, instead of Sampled. Once the Full Scan defrag completed, the timer job started handling the index fragmentation automatically.

Source - https://social.technet.microsoft.com/Forums/windowsserver/en-US/474ab125-da2d-478d-8ee2-3ac9453c20f8/noticing-a-lot-of-database-index-fragmentation-yet-no-health-analyzer-alerts-best-practice-for?forum=sharepointadmin

  • In this case we have ~25 indexes with >75% fragmentation. The Avg page count is ~2000. – Aaron Mar 17 '15 at 14:13
  • Reading more from the source you gave, it seems SharePoint ignores WSS_Logging when defragging indexes. he said I wouldn't be concerned with it as the data is transient and not read by SharePoint, only written. – Aaron Mar 17 '15 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.