Setting MAXDOP During SQL Server 2019 Setup

SQL Server 2019

Just a short note – in SQL Server 2019, the setup process has a new MAXDOP tab:

The hyperlink about MAXDOP configuration points here.

I did a few quick test installs:

  • 4 cores gave me a MAXDOP 4
  • 8 cores offered MAXDOP 8
  • 12 cores offered MAXDOP 6

Historically, the 0 default meant unlimited cores. I like this setting instead because if somebody amps up the VM’s core count later, MAXDOP won’t automatically rise to match. If you jump from, say, 4 cores to 8, then I still kinda like the 4 setting better.

In vaguely-related-news, this week’s CTP 3.0 adds a lot of diagnostics for Azure SQL DB Hyperscale, plus a new sp_configure option for hybrid_buffer_pool (no trace flag required.)

Previous Post
My classes have really sucked lately, and I apologize.
Next Post
5 Questions to Ask When You Upgrade SQL Server

19 Comments. Leave new

  • Hello There, I Agree with you, Our server has 8 cores pyhsical box, i’ve tried setting the MAXDOP to 6 and tried 4 but i noticed some slowness so reverted back to Zero.
    I would love to hear from you about setting the cost threshold knowing that i have an 8 core box with 56 GB RAM
    Im fully aware that setting this option is not a standard and the results varry based on each environment and so on, Im keeping it at 5 at the moment, i tried setting it to 25 last week but i faced some slow downs.

  • Kevin Fries
    May 23, 2019 9:34 am

    That’s great news for VM with multiple DBs.

  • Love that Microsoft is bringing a lot of the post-install things into the installer over the last few versions! Was this in a particular CTP, or has it been in all of them? Don’t recall seeing it in 2.5 but I may have just flown past it.

  • Any insight on why it drops from 8 to 6 when you increase cores?

  • Andrea Caldarone
    May 24, 2019 12:50 am

    they should consider NUMA Node size, parallel execution must be limited into a single NUMA node. They should also consider that in virtual environments where the majority of nowdays SQL Servers run, what the operating system sees about NUMA topology doesn’t always match with the physical one.

  • So I read the link in the post and the different guidelines for 2016 and above, but I still cannot under why 12 cores offered MAXDOP 6. Unless you have a multiple NUMA node server? 6 cores per node?

  • What MAXDOPS do you recommend for Sharepoint? I ask as their installation recommends setting it to 1

  • Andrea Caldarone
    May 25, 2019 9:19 am

    Anyway no RAC-like feature not even this time…

  • Kannan Chandrasekaran
    July 1, 2019 3:38 am

    This should confuse the people and reduce the performance, A logical processor is perceived by Windows as a processor, and each logical processor is capable of executing its own stream of instructions simultaneously, to which the OS can in turn assign simultaneous independent units of work. Windows Server enables each core to appear as a logical processor. Microsoft should leave the value as 0. There are multiple areas in SQL server should require improvements. Microsoft should consider those areas instead of such items.


Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.