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 find
that one of the threads ends before the other and has to wait for another
thread to do its work. In applications with short and fast transactions, this
is fine. In applications of large amounts of transactions, with a lot of
queries executed in parallel (i.e. the case of SharePoint), this can lead to a
reduction in performance, since threads that need to do the actual work, have
to wait for other threads to be finished.

How MAXDOP works?

If your SQL serves only
SharePoint, you will need to change the MAXDOP option of the SQL Instance to
MAXDOP=1. The default value of MAXDOP is 0, which means that SQL will use all
its cores for parallelism. When MAXDOP=0, it can cause contentions and queries to
hang. 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

EXEC sp_configure;
GO