SharePoint Tidbit - Optimizing SQL for SharePoint on-prem

  • Article

Hello All,

First of all, these steps will be true for both SharePoint 2013 or SharePoint 2016 unless mentioned otherwise, as well the steps will be true for all supported versions of SQL unless stated otherwise.

I’m sure you realize this but let it be said SQL is the heart, brain, and body of SharePoint this means if SQL is not performing well then your users will not be happy, so here are some of the things I would recommend:

  1. Set Max Degree of Parallelism (MAXDOP) to 1, due to the way that SharePoint works this is the only acceptable value.
  2. Set AUTO_UPDATE_STATISTICS & AUTO_CREATE_STATISTICS to disable for all databases
  3. Do not ignore the Temp DB, Optimize the database this means several things.
    1. Pre grow database
    2. Split database and transaction logs across multiple disks (NOTE: The faster the drive the better for these files)
    3. Create multiple files for database, one file for each CPU up to max 8 files (NOTE: Files should be the same size)
    4. Set recovery model to simple for this database
  4. Perform SQL Database performance!   Recommend following the recommendations in this article https://www.microsoft.com/en-us/download/details.aspx?id=24282
  5. Split Databases and Transaction logs to separate disks

The following steps will help with performance, by are more complicated:

  1. Split search database and transaction logs to there own Fast disks
  2. For all databases that will not be restored in case of Disaster recovery (ie Search and Configuration) set recovery model to simple
  3. You can further improve Content Database performance by doing the following
    1. Pre grow databases
    2. Split database across multiple files
    3. Place files on fast disks

As a final thought remember to work closely with your DBA, they will be able to help you to get this right.

Pax