2

I find a high level of index fragmentation in many tables of the SharePoint 2013 database (greater than 75%). Documentation appears to indicate that SharePoint Timer should be running tasks to maintain indexes and statistics. I do not see the index fragmentation decreasing as a result of any timer job.

I would like to implement a maintenance plan in SQL Server, however, I do not want to place the SharePoint 2013 server in an unsupported state.

Question1: what steps are recommended/best practices for maintaining the SharePoint 2013 indexes? Here i'm looking for a formal document from MS (if it exists) that talks about SQL maint, not timer jobs.

Question2: I find no formal indication that the scripts provided by ola hallengren are safe for SharPoint 2013. Is it not safe to use these scripts? Here i'm looking for some kind of confirmation that these scripts do not put the SharePoint server in an unsupported state.

Question3: What command can I safely use to defrag the indexes for all indexes in all tables in all of the SharePoint 2013 databases and maintain the options of each index? Again, i'm looking for commands that do not put the server in an unsupported state.

  • SharePoint 2013 SP1
  • SQL Server 2014
  • running on Windows 2012 R2 Datacenter
Improve this question

1 Answer 1

Reset to default
1

Q1: None. Let the internal health rule which takes care of index fragmentation run. If running into a particular performance issue, Microsoft PSS will sometimes recommend a manual full reindex.

Q2: Generally you do not need to run anything for reindex/update stats. In 2016, auto update of stats have been enabled for most databases.

Are you seeing performance issues?

Improve this answer
5
  • Q1: i am letting health rule run, but no change in index frag. what steps do Microsoft PSS recommend for a manual full reindex? where do i find docs on this?
    – Bill
    Commented Feb 10, 2017 at 17:02
  • Contact PSS if you need an official statement, none exists. Unless you're experiencing performance issues, I wouldn't spend too much time on it.
    – user6024
    Commented Feb 10, 2017 at 17:02
  • i can't tell if i'm seeing perf issues. i see errors in workflows and sometimes the site is slow and there are errors in ULS, but this system is INCREDIBLY complex, so how can you know?
    – Bill
    Commented Feb 10, 2017 at 17:03
  • ok, but i do see many SQL server comments that index fragmentation above 30% is not optimal and leads to performance problems. so i'm not sold on the idea that it is something to ignore.
    – Bill
    Commented Feb 10, 2017 at 17:04
  • Act on the errors themselves. And yes, sometimes SharePoint is slow, but usually that is due to JIT'ing or you're just running complex queries (many queries in SP don't use index hints).
    – user6024
    Commented Feb 10, 2017 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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