Are you setting up a SharePoint-farm? You better read this!

When we're talking about SQL Server there are two settings that are commonly debated. "Maximum Degree of Parallellism" and "Cost Threshold for Parallellism". Before we get to the reason of the subject and the main message I'd like to use the moment to debunk and explain these two settings, how to locate them, and how to configure them in normal setups. With that said, anything related to SQL together with SharePoint is or isn't normal, it's all up to you... ;)

"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

• http://technet.microsoft.com/en-us/library/hh292622(v=office.15).aspx
• http://technet.microsoft.com/en-us/library/ms181007(v=sql.105).aspx
• http://technet.microsoft.com/en-us/library/ms188603(v=sql.105).aspx

Ok, I want Power BI but I do not know which option?

That was the question I got, and there are actually two start scenarios. I base this on the current Power BI offering from Microsoft. Please see the official site for details, http://www.microsoft.com/en-us/powerBI/pricing.aspx.
* 40% off promotion for Office 365 E3 or E4 customers. Offered through December 31, 2014.

 

1) I have an Office 365 Enterprise E3 or E4 subscription, and want to add Power BI functionality.

• You can add Power BI functionality for the discounted* rate of $20USD per user and month, standard price is $33USD per user and month. On the Buy Now site you just choose "Add-on for E3/E4 Subscribers".

2) I do not have an Office365 E3 or E4 Enterprise subscription, or do not want to use the one I have.
You can choose between two options, "Standalone" or "Standalone + Office 365 ProPlus". It all comes down to if you have the "Office 365 ProPlus" or the "Office 2013 Suite" or not, this is what I call the client tools.

• With "Standalone" you get a SharePoint Online but you need your own Office-suite. Current offer is $40USD per user and month. 
• And if you need the client tools you chose the other, "Standalone + Office 365 ProPlus". Current offer under the discount is $52USD per user and month.

After this response I got a follow-up question.

?) Do I need a Power BI license for every user?
It all comes down to what the user needs to do. Once again there are two categories.

• "Publishers" are the users that create and share Power BI content, these users need a Power BI license.
• "Consumers" are the users that consume the shared reports, and they just need a license for the SharePoint Online portal.

Is this a full Picture? Nope, this is basically the view right now and it might change over time. If you are going to Power BI I strongly recommend a session with an Expert that can evaluate your current need and help you decide on what to get or not.