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?
Add a comment
|
1 Answer
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.
answered Mar 17, 2015 at 14:07
-
In this case we have ~25 indexes with >75% fragmentation. The Avg page count is ~2000.– AaronCommented Mar 17, 2015 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.– AaronCommented Mar 17, 2015 at 14:27