SQL Server 2019 Installation Enhancements for MAXDOP and Max Memory

By: Aaron Bertrand | Comments (1) | Related: > SQL Server 2019

Problem

There are multiple settings that you tend to change immediately after installing or upgrading an instance of SQL Server, but they require manual steps after setup, and often require a restart of the service to take effect. Often it would be nice if there were a way to override these options before installation has completed, not only to avoid the restart, but also to ensure that none of these changes could be forgotten.

Solution

With each new version of SQL Server, more options are added to setup, for both the interactive UI and the command line. In this tip, I wanted to highlight what I think are the most important changes coming in SQL Server 2019, and tell you about my wishlist for future versions.

MAXDOP settings in SQL Server 2019 Setup

One of the long-standing defaults in SQL Server has been the maximum degree of parallelism (MAXDOP), which has always been 0, meaning use (up to) all cores when the engine believes that will help. For many OLTP workloads, 0 is not the optimal setting, and you may want to use a different number depending on the behavior of your workload. I don't want to belabor the thought process here, but this will be based on settings like the number of cores exposed to SQL Server, whether they are divided into NUMA nodes, and if there are other instances, applications, or services running on the same Windows Server. Microsoft's guidelines are published here.

In SQL Server 2019, there is a new command-line argument, /SQLMAXDOP, allowing you to specify this value for automated installs. If you don't use this parameter, the default will be derived according to the guidelines published by Microsoft. At the time of writing, for posterity, these are as follows:

Current Microsoft guidelines for MAXDOP.

There is also a new tab in interactive setup, on the Database Engine Configuration screen, called MAXDOP. You can see that, on my quad-core virtual machine, the recommendation is to use MAXDOP of 4:

There is a lot of additional context provided on this screen, including a link to those same published guidelines.

Memory settings in SQL Server 2019 Setup

Another long-standing pair of options that often have to get changed after setup – especially when an instance shares a host's resources with other instances or applications – are the min and max settings for server memory. The default has been 0 and 2 petabytes, respectively, for many versions now, and these settings have only added to the confusion of how memory management works in SQL Server. As with MAXDOP, Microsoft publishes guidelines about overriding the defaults. Again, for posterity, the core of the recommendation for max server memory is currently as follows:

Published guidelines for max server memory.

This is a little more convoluted than MAXDOP, and you should fully invest in understanding the options before overriding the defaults.

In SQL Server 2019, there are now two different ways you can override the defaults:

/SQLMINMEMORY and /SQLMAXMEMORY
These options take integer parameters specifying the number of Megabytes to use for their respective settings. You can use either or both of these options; if you leave either out, they will refer to defaults, as they always have – min server memory will be set to 0, and max server memory will default to 2 petabytes.
/USESQLRECOMMENDEDMEMORYLIMITS
This option, which cannot be used in combination with the /SQL…MEMORY arguments, tells setup to come up with recommended min and max server memory settings for you, based solely on the hardware. Keep in mind that, when calculating this recommendation, setup only knows about the underlying server and this brand new instance of SQL Server; it has no idea about any other resource constraints you may have, now or in the future.

These options are also exposed in the interactive component of setup, on a new Database Engine Configuration tab called Memory. On this machine, setup recommended just under 6 GB for max server memory, which seems roughly consistent with the guidelines for a VM with 8 GB of memory:

Min and Max server memory recommendations in setup.

There is a lot of helpful context here, too, including a link to the published guidelines. Note that I can override either value in the Recommended column, or check the box below that basically says, "I don't want to think about it; why don't you come up with the best recommendation for me?"

What's missing?

These are great additions to setup, after they added instant file initialization and sensible and myth-busting tempdb file recommendations in SQL Server 2016. There are a few other settings that I'd love to see considered at some point:

Lock pages in memory (LPIM) is still something you have to enable after setup, even though to me this falls into a similar category as instant file initialization. Allowing you to opt into this at installation time would be useful in many environments.
Cost threshold for parallelism is another that I think has an arbitrary limit that isn't correct for most workloads, but changing an arbitrary default that doesn't have an obvious, more optimal arbitrary default is one that will take a lot of effort and hard evidence to fight the inertia of, "if it ain't broke, don't fix it."
And in SQL Server 2019, it would be nice to enable Memory-Optimized TempDB Metadata during setup, to avoid the requisite restart, as would the ability to make Accelerated Database Recovery the default for all new databases.

Next Steps

Read on for related tips and other resources:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Monday, November 11, 2019 - 5:31:03 AM - Kenneth Igiri	Back To Top (83052)
Great Article. Very informative.