dsccommunity / dsccommunity/SqlServerDsc

SqlSetup: Incorrect calculation logic for xSQLServerMaxDop

Open
#809 0 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

enhancement help wanted
Dominant language
PowerShell
Stars
385
Forks
224
PR merge metrics
No merged PRs in 30d

Description

Details of the scenario you tried and the problem that is occurring:
In the Get-SqlDscDynamicMaxDop function, the logic there seems to assume that numProcs is referring to the number of physical CPUs from the calculation that's being done, when in facts it counts "logical CPUs" - that is the number of processors that the OS reports ( equal to twice the number of cores in a HT environment):

    if ($numProcs -eq 1)
    {
        $dynamicMaxDop = [Math]::Round($numCores / 2, [System.MidpointRounding]::AwayFromZero)
    }

In this branch - dynamicMaxDop will always be 1 - as numProcs is actually the number of logical processors ( twice the number of cores in a hyper-threaded environment).
This branch would also only be entered if this script runs on a single-core, non-hyperthreaded machine (where numCores will also be 1).

Also, in the event of a machine with 2 sockets (NUMA nodes) with 2 cores each, according to the very latest version of the code in the repo, numCores will be 4 and, according to this branch:

else
    {
        $dynamicMaxDop = $numCores
    }

dynamicMaxDop would be set to 4, which is incorrect. In this case, it should actually be set to 2.

The Microsoft KB article on this topic lists the following logic:
https://support.microsoft.com/en-ie/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

Server type Processors MAXDOP setting
Server with single NUMA node Less than 8 logical processors Keep MAXDOP at or below # of logical processors
Server with single NUMA node Greater than 8 logical processors Keep MAXDOP at 8
Server with multiple NUMA nodes Less than 8 logical processors per NUMA node Keep MAXDOP at or below # of logical processors per NUMA node
Server with multiple NUMA nodes Greater than 8 logical processors per NUMA node Keep MAXDOP at 8

Suggestion
Loop through the NUMA nodes - pick the one with the lowest number of cores and use that as a the MAXDOP number if lower than 8, and 8 if equal to that or higher.
I don't know if there are any setups out there with a different number of cores per NUMA node, but the above should take care of that instance.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Locate Get-SqlDscDynamicMaxDop and trace how numProcs, numCores, and NUMA nodes are gathered for xSQLServerMaxDop. Compare the calculation with the linked Microsoft MAXDOP guidance, including single-node and multi-node scenarios. Done means the calculation uses the lowest logical-processor count per NUMA node, capped at 8, with coverage for the reported cases.

Written by the indexing model from the issue text.

Assessment

Tech stack
powershell, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.