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
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?