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

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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.

  6. $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

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

  8. 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

  9. Hello Paul,

    here the figures for our SAP OLTP System.

    1 8 128

    The SAP recommends the maxDOP Value for their software.

  10. 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% :)

  11. 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

  12. 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)

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

  14. 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

  15. 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,

  16. 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

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.