"Maximum Degree of Parallellism" or MAXDOP is the amount of parallell threads a single statement can be split up into. You usually see recommendations for OLTP-Environments to change the default value of "0", which means dynamic, to recomended value of "1". This means a single thread, and my opinion on it is actually depends. I would recommend to set it to the half amount of available cores and then look at contention, and I look at physical cores, not the logical hyper-threaded ones. Some queries might suffer from parallellism, but not all. And for that you can hint the query instead, just add "OPTION (MAXDOP 1)" at the end of the statement.
"Cost Threshold for Parallellism" is the result of the Query-cost, and if the estimated cost exceeds the cost threshold the Query Optimizer tries to parallellize the execution plan. The defaulr value "5" was originally 5 seconds, today this is an calculation of cpu time, IO and Formulas depending of which optimizer method used. This means the default value is not valid anymore, and we need a new value. I usually recommend to start out with 50 and then adjust if it is to low or high for average queries.
And now over to the main subject. Did you know that when you run SharePoint there is a requirement to set "Maximum Degree of Parallellism" to "1". This is true for other systems as well, for example System Center and Dynamics. The SharePoint actually set this automatically for you. See this capture when creating a SharePoint Web Service Application:
IF (SELECT value FROM sys.configurations WHERE name = 'max degree of parallelism') <> 1
BEGIN
exec sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
exec sp_configure 'max degree of parallelism', 1;
RECONFIGURE WITH OVERRIDE;
END
Being a DBA this would freak me out. But this is done by design, and we have to live with it. Remember this if you are using your data warehouse server as a database host for SharePoint, or if you share databases for other uses with SharePoint. This is an obvious choice for another instance, have a specific, dedicated instance for SharePoint. And Let SharePoint do whatever SharePoint want to with that instance. The DBA can maintain the instance as any other SQL Server instance.
Remember that Resource Governor is a perfect choice for controlling resource usage on a scenario type of level, and in SQL Server 2014 it can not only manage the cpu and memory, it also can manage IOPS.
Have Fun,
Mattias
Sources
