When installing SQL Server, there are two important settings that every DBA should consider changing: MaxDOP and Max Memory. These settings can greatly impact the performance and efficiency of your SQL Server instance.

MaxDOP

MaxDOP, or Maximum Degree of Parallelism, determines the maximum number of CPU cores that the SQL Server engine can use for a single task. When running on a computer with multiple CPU logical cores, SQL Server automatically detects the best degree of parallelism for a query. This means that it determines the optimal number of processors to use for each part of a parallel plan execution.

During the installation process, SQL Server setup provides a recommendation for the MaxDOP setting based on the number of logical CPU cores on your machine. For example, if you have 4 logical CPU cores, the setup program may recommend a MaxDOP of 4. However, you have the option to change this recommendation if needed.

It is important to note that a query can spawn multiple tasks, so the MaxDOP setting applies to each individual task. To determine the recommended MaxDOP setting, you can use a query similar to the one provided by Microsoft:

declare @logicalCPUs int
declare @HTEnabled int
declare @physicalCPU int

select @logicalCPUs = cpu_count -- [Logical CPU Count]
    ,@HTEnabled = case 
        when cpu_count > hyperthread_ratio
            then 1
        else 0
        end -- HTEnabled
from sys.dm_os_sys_info
option (recompile);

select
    case 
        when @logicalCPUs < 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : ' + CAST(@logicalCPUs as varchar(3))
        when @logicalCPUs >= 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : 8'
        else ''
    end as Recommendations

By following the recommended MaxDOP setting, you can optimize the parallel execution of queries and improve overall performance.

Max Memory

Another important setting to consider is the maximum memory that your SQL Server instance will use. By default, SQL Server sets the max memory to 2147483647 MB (or 2,147,483,647 MB). However, not setting this value or setting it too low can lead to performance issues and memory pressure.

During the installation process, SQL Server setup provides a recommended max memory value based on the total memory available on your machine. For example, if you have 8GB of memory, the setup program may recommend a max memory of 5940 MB, which is approximately 72% of the total memory.

It is important to allocate memory appropriately, especially when multiple SQL Server instances are running on the same host. In such cases, you should follow the 70-30 rule, allocating 70% of the memory to SQL Server and 30% to the operating system.

To set the max memory value, you can use the /SQLMAXMEMORY parameter in the command line during setup.

Conclusion

SQL Server 2019 has introduced a useful feature that provides recommended values for MaxDOP and max memory during the installation process. This allows even accidental DBAs to choose the recommended values and avoid unnecessary performance issues.

By understanding and adjusting these recommended settings, you can optimize the performance and efficiency of your SQL Server instance.