There is good general advice for how to configure Maximum Degree of Parallelism (MAXDOP). However, I am in a special circumstance. There are two instances on my box. If I have 16 cores, how should I divide MAXDOP between them? What metrics would reveal that I have made a bad choice?
I am on SQL Server 2019 and the standard advice for MAXDOP changed in 2016 so info from before June 2016 (the release date of SQL Server 2016) may not be applicable.
Stacked instances are a terrible mistake if you truly care about the performance of both servers, because they're going to both be fighting over the same resources unless they're somehow perfectly aligned workload-wise to have their busy periods on opposing schedules. Even so, the initial transition will be rocky from a memory perspective.
Avoiding CPU contention is more about setting up CPU Affinity so that each SQL Server utilizes a separate block of CPUs.
On StackedMistake1, you'd run:
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,2,4,6,8,10,12,14
GO
And on StackedMistake2, you'd run:
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 1,3,5,7,9,11,13,15
GO
Of course, now you need to hope that you have enough memory to fully support both of the StackedMistake's memory requirements for the buffer pool, memory grants, and other important clerks and caches.
If neither server is important, then what you set anything to is of little relevance, beyond following stock best practices. If one server is more important than the other, then use settings to nerf the less important one to a lower CPU and memory footprint to minimize resource contention.
