Lately I have been thinking about some of my latest work;mainly I have focused on an one day work shop with one of my colleagues, Amir, who is a SharePoint PFE.
Now I’d like to share with you some of the SQL Best Practices for SharePoint.
In this post, we will talk about MAXDOP and why MAXDOP in SharePoint needs to be =1.
When a query is executed in parallel, certainly, you’ll findthat one of the threads ends before the other and has to wait for anotherthread to do its work. In applications with short and fast transactions, thisis fine. In applications of large amounts of transactions, with a lot ofqueries executed in parallel (i.e. the case of SharePoint), this can lead to areduction in performance, since threads that need to do the actual work, haveto wait for other threads to be finished.
How MAXDOP works?
If your SQL serves onlySharePoint, you will need to change the MAXDOP option of the SQL Instance toMAXDOP=1. The default value of MAXDOP is 0, which means that SQL will use allits cores for parallelism. When MAXDOP=0, it can cause contentions and queries tohang. In this case, the value should be changed to 1.
How to configure the MAXDOP
EXEC sp_configure 'show advanced options', 1;RECONFIGURE WITH OVERRIDE;GO
EXEC sp_configure 'max degree of parallelism', 1;RECONFIGURE WITH OVERRIDE;GO