I am new to Sharepoint, but I used to work with SQL Server a lot. I realized that Sharepoint changes some of the SQL Server settings to non default values. One is the max degree of parallelism, why doesn't sharepoint like parallel plan executions? Also why should I turn off "auto update statistics? I couldn't find answers to these two questions. I am on Sharepoint 2013
asked Oct 6, 2015 at 14:49
1 Answer
A busy SharePoint site is expected to have a lot of users which generate a huge volume of simple database requests.
If you had 8 cores and 200 users, you'd fill the queues parallelising just a few user's worth of workloads. By forcing each workload onto one core, you are ensuring a better "minimum standard" experience for everyone while reducing the time wasted due to context switching where it wasn't strictly necessary (as those queries were simple and would finish quickly enough on one core anyway).
That's my understanding of it.
Regarding the indexing, that's done by Sharepoint when it installs/upgrades and you don't need to touch it. As Shanky and Max posted, Sharepoint (and some other high-end Microsoft products like SCOM) manages maintenance on itself.
I still do weekly maintenance on top of that to keep it standard across my servers. It doesn't break anything.
auto update statisticsshould be turned off since the product automatically updates statistics when/as needed.maxdopshould be set to 1 to allow a larger number of simultaneous requests to be processed. If the server hosting the SharePoint databases has a large number of processors, you might see better individual query performance with amaxdop = 2, but naturally that will decrease the concurrency of the system.