Sql-server – Why is a max degree of parallelism 1 recommended and used for Sharepoint in SQL Server

configurationsharepointsql serversql server 2014

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

Best 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.

Related Question