sqlskills-logo-2015-white.png

Survey: cores vs. MAXDOP

I have a few surveys to editorialize but I'd like to kick off another one where you have to run a bit of code and send the results.

For this one I'm interested in how you have your system configured for parallelism, given the number of processor cores and NUMA nodes configured.

For systems using SQL Server 2005 onwards, the following code will work:

SELECT
    [value_in_use] AS [MAXDOP],
    os.*
FROM
    sys.configurations,
    (SELECT
        COUNT (DISTINCT [parent_node_id]) AS [Nodes],
        COUNT (*) AS [Cores]
     FROM
        sys.dm_os_schedulers 
     WHERE
        [status] = 'VISIBLE ONLINE') AS os
WHERE
    [name] = 'max degree of parallelism';
GO

Shoot me the results in email, spreadsheet, or as a comment on the blog. Comment #7 below has a Powershell script from Aaron Nelson (b|t) for all your servers – thanks!

If you have any info on the general workload/workload mix on the server, that would be helpful too.

Thanks!

61 thoughts on “Survey: cores vs. MAXDOP”

  3. MAXDOP Nodes Cores
    0 1 4
    2 1 4
    4 1 8
    6 2 12
    12 2 16
    4 2 8
    0 1 4
    0 1 8
    8 1 16
    4 1 8
    8 1 16
    0 1 4
    0 1 2
    6 1 8
    1 1 8
    8 1 16
    0 2 8
    4 1 8
    0 1 4
    0 1 16
    0 1 4
    4 2 8
    0 1 4
    2 1 4
    6 2 12
    4 1 16
    12 2 16
    1 1 1
    8 2 8
    6 1 8
    8 1 16
    4 1 8
    2 1 4
    1 1 1
    6 2 12
    0 1 4
    6 1 8
    4 2 8
    1 1 2
    8 2 12
    4 2 8
    2 1 4
    2 1 4
    6 1 8
    0 1 4
    0 2 16
    0 1 2
    2 1 4
    8 1 16

    Reply

  10. I’ve always gone with the thought of leaving it uncapped and control it at the query level. Look forward to reading your blog about it.

    MAXDOP Nodes Cores
    0 1 1
    0 1 2
    0 1 1
    0 1 1
    0 1 2
    0 1 4
    0 1 2
    0 1 2
    0 1 8
    0 1 2
    0 1 2
    0 1 4
    0 1 4
    0 1 4
    0 1 1
    0 1 8
    0 2 16
    0 2 16
    0 1 8
    0 1 8
    0 1 1
    0 1 4
    0 1 2
    0 1 1
    0 4 8
    0 1 8
    0 1 8
    0 1 1
    0 1 4
    0 1 4
    0 1 16
    0 1 4
    0 1 4
    8 2 16
    0 1 8
    0 1 2
    0 2 16
    0 2 4
    0 1 2
    0 1 2
    0 2 16
    8 2 16

    Reply

  13. 0 1 4
    6 2 12
    0 1 1
    0 2 8
    0 2 24
    0 2 24
    0 2 24
    0 1 1
    0 1 4
    0 1 2
    0 1 4
    0 1 1
    8 2 16
    0 1 2
    0 1 1
    0 1 4
    0 1 4
    0 1 2
    0 1 8
    0 1 2
    0 1 1

    Reply

  14. 0 1 4
    0 1 2
    0 1 4
    0 1 8
    0 1 2
    0 1 1
    0 1 4
    0 1 4
    0 1 1
    0 1 8
    0 1 4
    0 1 4
    0 1 4
    0 1 8
    0 2 16
    0 1 4
    0 1 4
    0 1 4

    Reply

  19. MAXDOP Nodes Cores
    0 2 8

    Single instance server with two applications running on it. No more than 40 users, and almost never any problems.

    Reply

  22. $ServerList = dir -recurse SQLSERVER:SQLRegistration’Database Engine Server Group’ | where {$_.Mode -ne "d"}

    foreach ($RegisteredSQLs in $ServerList )
    {
    $S++;
    $dt+=invoke-sqlcmd2 -ServerInstance $RegisteredSQLs.ServerName -database master -Query "SELECT
    [value_in_use] AS [MAXDOP],
    os.*
    FROM
    sys.configurations,
    (SELECT
    COUNT (DISTINCT [parent_node_id]) AS [Nodes],
    COUNT (*) AS [Cores]
    FROM
    sys.dm_os_schedulers
    WHERE
    [status] = ‘VISIBLE ONLINE’) AS os
    WHERE
    [name] = ‘max degree of parallelism’;" -As ‘DataTable’
    }

    $MultipleResults = $dt | ConvertTo-Html -Property MAXDOP, Nodes, Cores | Out-String;

    Send-MailMessage -To paul@SQLskills.com `
    -Subject "tempdb Survey Results" `
    –From YourEmailAddress@GoesHere.com `
    -SmtpServer YourSMTPServerGoesHere `
    -Body $MultipleResults -BodyAsHtml

    Reply

  23. Paul,
    That block of code I uploaded is just a copy/paste/change-some-things of the last one I did: http://sqlvariant.com/wordpress/index.php/2011/02/emailing-tempdb-query-results-to-paul-randal-with-powershell/
    In fact I just noticed that I forgot to change the subject line of the email. oops. Guess you can just edit that part of the comment. :-)

    It requires the SqlServerProviderSnapin100 snapin (standard) and invoke-sqlcmd2 (nonstandard but Liquid-Awesome http://gallery.technet.microsoft.com/ScriptCenter/en-us/7985b7ef-ed89-4dfd-b02a-433cc4e30894) just the same as the previous post.

    Reply

  26. MAXDOP Nodes Cores
    ———– ———– ———–
    16 2 48
    16 8 48
    16 8 48
    0 4 24
    0 4 24
    0 4 24
    0 8 48
    0 8 48
    0 4 24

    Reply

  27. Hello Paul,

    here the figures for our SAP OLTP System.

    1 8 128

    The SAP recommends the maxDOP Value for their software.

    Reply

  30. MAXDOP Nodes Cores
    32 4 64

    Heavy client-server OLTP, 300+ client connections. Had to reduce MAXDOP to the number of actual cores (not HT’d) because for some frequent queries parallelism management costed too much (CPU up to 100% for long periods of time). Now it is about 5-15%% on average with narrow peaks up to 50% :)

    Reply

  31. 0 2 16
    0 1 4
    0 2 4
    0 1 2
    0 1 2
    0 2 16
    0 2 4
    0 2 4
    0 1 4
    0 2 16
    0 4 8
    4 4 24
    8 4 32
    4 4 4
    4 4 4
    0 2 32
    4 2 32

    Reply

  40. SERVER NAME DOP NODES CORES WORKLOAD
    SERVER10INST08 0 1 16 OLTP
    SERVER11INST09 1 1 16 MIXED (TEST)
    SERVER09INST07 0 1 16 OLTP
    SERVER06INST04 2 1 16 OLTP
    SERVER05INST03 0 1 16 OLTP
    SERVER04INST02 0 1 16 OLTP
    SERVER03INST01 4 1 16 OLTP
    SERVER07INST05 1 1 16 OLTP
    SERVER31INST01 1 1 16 OLAP
    SERVER32INST02 0 1 24 OLAP(TEST)

    Reply

  46. ERP MS Dynamics NAV Database
    ============================
    MAXDOP Nodes Cores
    0 1 24
    ****************************
    BI Data Warehouse
    ============================
    MAXDOP Nodes Cores
    0 1 8

    Reply

  47. IF object_id(‘tempdb..#MyDOP’) IS NOT NULL
    BEGIN
    DROP TABLE #MyDOP
    END
    GO

    CREATE TABLE #MyDOP
    (
    MxDop INT NOT NULL,
    Nodes INT NOT NULL,
    Cores INT NOT NULL,
    )

    INSERT INTO #MyDOP
    SELECT 0, 1, 8
    UNION
    SELECT 0, 2, 24
    UNION
    SELECT 0, 2, 24
    UNION
    SELECT 0, 1, 8
    UNION
    SELECT 0, 1, 8
    UNION
    SELECT 0, 1, 1
    UNION
    SELECT 0, 1, 4
    UNION
    SELECT 0, 1, 2
    UNION
    SELECT 0, 1, 4
    GO

    Reply

  57. Hi Paul,

    Load is mostly OLTP

    Maxdop/Nodes/Cores
    1 2 16
    1 2 16
    1 2 16
    1 4 16
    1 2 16
    1 1 8
    1 1 8
    1 1 8
    1 1 8
    4 2 16
    1 1 8
    1 4 16
    1 1 8
    1 1 8
    1 1 8
    1 4 16
    1 1 4

    Rgds,

    Reply

  58. MAXDOP Nodes Cores
    4 2 16
    0 2 16
    0 1 8
    0 2 16
    0 1 8
    0 2 16
    0 2 16
    0 2 16
    0 1 8
    0 2 16
    0 4 8
    0 1 4
    0 1 2
    0 1 4
    0 1 8
    2 1 8
    0 1 2
    0 1 4

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course