Setting maxdop 1 vs setting the threshold for parallelism high

Question

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:

1. CRM has to be fast, period
2. 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?

Answer 1

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.

Answer 2

For Microsoft Dynamics CRM database server instance, you have to set MAXDOP 1.

If using Enterprise edition, above answer using Resource governor makes sense. You can also look into using query hint OPTION (MAXDOP N) where N is a sensible value calculated based on number of logical processors and NUMA nodes of your sql server instance.

Make sure that statistics are up-to-date, since the optimizer relies on statistics to generate good query plans. After you run your ETL, make sure to update stats.

For cost threshold for parallelism should be bumped up away from default of 5 to some good starting value - I tend to go with 45 (and then monitor), but YMMW.

Cost threshold of parallelism refers to what the minimum query cost has to be before Parallelism is considered by the optimizer.

Remenber that CXPACKET waits are just symptoms due to something being wrong related to query - outdated statistics or missing index resulting in a bad or different plan.

You can use sys.dm_exec_cached_plans and sys.dm_exec_query_plan DMV's to mine information from the plan cache as described in Tuning ‘cost threshold for parallelism’ from the Plan Cache by Jonathan and Cost Threshold for Parallelism.

There are other optimizations like (in no particular order):

• revisiting your tempdb configuration.
• Data and log files should be on separate physical LUNs or drives.
• Max memory is set away from default.
• Instant file initialization is enabled and autogrowth is set to a sensible value.

(If it is possible for you to install another instance for your ETL database, the it would be a much better choice. This way you can seperate your CRM workload (which has requirement of MAXDOP 1 instance wide) and your ETL workload which will benefit from parallelism)

Answer 3

There is no hidden impact in raising the cost threshold for parallelism. As low as it is, SQL Server ships with a default cost threshold of 5, which is always taken into account when the optimizer compiles query plans. So, in this regard, the amount of work for the optimizer is the same.

Whether raising the CTFP to a very high value is a good idea or not, this has to be validated against your workload. You can set that value in your test environment and replay a significant production workload against it.

Even if MS itself recommends MAXDOP 1 for CRM, it is not unlikely that some queries might benefit from parallel plans. As the article you linked states, in that case you're trading computing power spent on a single request for higher concurrency. It's up to deciding what has a higher value for you and your workload and, again, only a proper multithreaded workload replay can tell that for sure.

CTFP and MAXDOP can be tweaked in many ways and you could reasonably start investigating with CTFP=50 and MAXDOP=[number of logical processors in a NUMA node]. If you see too many CRM queries going parallel with negative effects on concurrency, you can raise CTFP and/or lower MAXDOP.

As other answers suggest, Resource Governor or a dedicated instance for your CRM database are completely valid (and desirable) options. If MS recommends setting MAXDOP to 1, they won't likely offer additional assistance if you don't do that in your instance.