SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has added to SQL Server Setup, on the Windows version.
On the Database Engine Configuration screen are two new tabs, called MaxDOP and Memory. These are both new configuration options for SQL Server 2019. This post will specifically look at the MaxDOP tab, and we’ll look at Memory next week.
There is just one option here to set, and a lot of text to explain things (which most people won’t read).
The value in Maximum Degree Of Parallelism (MaxDOP) that we see is set automatically, based on Microsoft’s own documented guidance. The virtual machine in this screenshot has two virtual CPU cores. Microsoft recommends setting this to a value based on the number of cores in a NUMA node from 0 to a maximum of 8. If you want to know what NUMA is, I wrote about it in Cost Threshold for Parallelism recently, in the section on Max Degree of Parallelism.
I am extremely happy when something I’ve written becomes redundant, because it’s one less thing to worry about when managing and monitoring a SQL Server instance.
Considerations
There are a few things for us to think about on this setup tab:
- The default setting will be based on the number of CPU cores available during setup. If you are someone who likes to set up large virtual machines initially, and then scale them down once everything is installed, please ensure that this setting takes the scaled down VM into account.
- By the same token, if you need to scale up the size of the VM later, you may have to adjust this setting again anyway.
- If you normally configure SQL Server from the command line (and you should), this option is now available there too.
- Most importantly, this is a change in behaviour from previous versions of SQL Server.
What about Linux?
If you need to configure Max Degree Of Parallelism on Linux, you will continue to do so using the sp_configure method once SQL Server is installed.
Share your thoughts in the comments below.
Photo by Mike Enerio on Unsplash.