First published on TECHNET on Apr 10, 2018 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:
Set Max Degree of Parallelism (MAXDOP) to 1, due to the way that SharePoint works this is the only acceptable value.
Set AUTO_UPDATE_STATISTICS & AUTO_CREATE_STATISTICS to disable for all databases
Do not ignore the Temp DB, Optimize the database this means several things.
Pre grow database
Split database and transaction logs across multiple disks (NOTE: The faster the drive the better for these files)
Create multiple files for database, one file for each CPU up to max 8 files (NOTE: Files should be the same size)