I am looking at a possible fringe case for parallelism settings.
I've long since followed the credence that maxdop 1 is a viable option for Microsoft Dynamics CRM databases. As the nature of the database is strictly OLTP, and parallelism has little to no benefit for a CRM instance, I've always set CRM databases to maxdop 1.
However, I have in my care now a database instance that has:
- Microsoft Dynamics CRM database
- An ETL database that is used primarily for OLAP purposes
I've asked our business partners and the way they see the performance importance is as follows:
- CRM has to be fast, period
- ETL imports should not be throttled, but come second to CRM performance
As such, I'm inclined to sacrifice parallelism on the OLAP database, and set maxdop 1 to benefit the CRM maximally.
Yet some middle ground might exist.
If I were to set the cost threshold for parallelism to a number which excludes everything that CRM throws at it, but according to my query plans might grab some of the OLAP transactions, would there be any adverse effects for the CRM?
What, if anything, is the impact of keeping a threshold so high, it is almost never reached, versus setting maxdop 1, am I incurring some hidden cost in making SQL Server take the threshold into account when calculating query plans?
Best Answer
There is an easy solution if you have Enterprise (expensive) Edition in use: Set maxdop at instance level as high as you want and use the resource governor to restrict all CRM users with their own workload group to maxdop 1.