Finally, two important SQL Server recommended settings have been introduced during the installation process. These are the settings that every DBA (should) change after every single installation. The settings are MaxDOP and Max Memory. This article looks at each of these settings and how they are presented during installation.

MaxDOP

When an instance of SQL Server runs on a computer that has more than one CPU logical core, it detects the best degree of parallelism for a query. That is to say, the number of processors employed to run a single task for each part of a parallel plan execution. MAXDOP specifies the maximum number of cores that the engine can use for a single task. Note that a query might spawn multiple tasks. You can read this Microsoft article if you want to know more about MaxDOP: Configure the max degree of parallelism Server Configuration Option

When I install SQL Server, the setup program will give me a recommendation and allow me to change this on a tab in the Database Engine Configuration screen. As you can see, setup is recommending me a MaxDOP of 4, on my machine that has 4 logical CPU cores.

If you run unattended setup, the parameter for the command line is /SQLMAXDOP. This is noted in the image above.

In the past, I used to check the recommended value with this query, which I found on the Microsoft site. I am assuming the logic behind the recommendation in setup is the same.

declare @hyperthreadingRatio bit
declare @logicalCPUs int
declare @HTEnabled int
declare @physicalCPU int
declare @SOCKET int
declare @logicalCPUPerNuma int
declare @NoOfNUMA int
select @logicalCPUs = cpu_count -- [Logical CPU Count]
    ,@hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio]
    ,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
    ,@HTEnabled = case 
        when cpu_count > hyperthread_ratio
            then 1
        else 0
        end -- HTEnabled
from sys.dm_os_sys_info
option (recompile);
select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
from sys.dm_os_schedulers
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64
group by parent_node_id
option (recompile);
select @NoOfNUMA = count(distinct parent_node_id)
from sys.dm_os_schedulers -- find NO OF NUMA Nodes 
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64
-- Report the recommendations ....
select
    --- 8 or less processors and NO HT enabled
    case 
        when @logicalCPUs < 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : ' + CAST(@logicalCPUs as varchar(3))
                --- 8 or more processors and NO HT enabled
        when @logicalCPUs >= 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : 8'
                --- 8 or more processors and HT enabled and NO NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA = 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
                --- 8 or more processors and HT enabled and NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA > 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
        else ''
        end as Recommendations

You likely will want to use the recommendation from setup unless you have evidence that it is wrong based on a previous workload.

There are a few places you might want to look for more information on this setting:

• • MAXDOP of Confusion (Dear SQL DBA Episode 8) (Kendra Little)
  • Five SQL Server Settings to Change (BrentOzar.com)
  • What MAXDOP setting should be used for SQL Server (MSSQLTips)

Max Server Memory

Another very important instance setting is the maximum memory that your server will use. You can read about minimum and maximum memory at Microsoft. Not setting this, or setting it too low, may lead to performance issues and may adversely affect your instance.

By default, max memory is 2147483647 (this value is in MB). If there are two instances on a the same host with the default memory setting, then one SQL Server instance will battle for memory with the other instance and this can cause performance issues.  Also, setting this value too low memory can lead to performance issue and memory pressure.

For a single instance, follow 70-30 rule [70% SQL Server and 30% Operating System]. For multiple hosts, you will need to still leave memory for the OS and then divide the remaining memory among your instances.

In setup, again on the Database Engine Configuration page, there is a Memory tab. You can see there is a checkbox for recommended and default memory. The recommended max memory (MB) is 5940 on my 8Gb machine, which is ~72% of the total memory.

As mentioned in the screenshot above, /SQLMINMEMORY and /SQLMAXMEMORY are the parameters for use in the command line.

There are a few references for setting max memory on your instances:

• Memory Dangerously Low or Max Memory Too High (BrentOzar.com)
• Max Memory Best Practices (StraightPath Solutions)

Always Check the Settings During Setup

In my eyes, SQL Server 2019 has introduced a very useful feature to add the recommended values on the installation page and allow you to change them. Now, even accidental DBAs can choose the recommended value while installing SQL Server and avoid creating unecessary performance issues.