1

I was looking at my client's UAT farm and found out that MAXDOP was set to 0 on SQL Server instance hosting SP databases. Before setting this value to 1, I measured response times of most commonly viewed pages along with heaviest pages.

To my surprise, after setting MAXDOP to 1 and restarting SQL Server service, performance was decreased. One heavy page was loading in 4.5 seconds before change, and 4.8 seconds after change. This was measured multiple times.

Did anyone experience a similar issue and how did you resolve it?

2

Setting MAXDOP value 1 will not decrease the performance rather it optimize the SQL Server Performance.

Rather it helps you to eliminate the blocking and Long wait. this will help you how MAXDOP will help http://blogs.msdn.com/b/grahamk/archive/2009/06/15/troubleshooting-sql-server-deadlocks-and-performance-when-running-sharepoint.aspx

When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number greater than 1 to restrict the maximum number of processors used by a single query execution.http://blogs.msdn.com/b/rmeure/archive/2011/12/23/optimizing-sql-for-sharepoint.aspx

Import Thing: This is prerequisite in SharePoint 2013, if you forget to set then you will not able to install SharePoint.

  • I pretty much already read about MAXDOP on MSDN. I wanted to see if someone else experienced such issue. – Slaven Semper Nov 17 '14 at 9:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.