Better SQL Server memory defaults in 2019

In 2016 I created the Max Server Memory Matrix as a guide for configuring the maximum amount of memory that should be assigned to SQL Server, using an algorithm developed by Jonathan Kehayias.

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. Last week we looked at MaxDOP, and this post will specifically look at the Memory tab.

Note that there are two options, plus a checkbox:

• Min Server Memory (MB)
• Max Server Memory (MB)
• Click here to accept the recommended memory configurations for the SQL Server Database Engine

The value in Max Server Memory that we see is based on Microsoft’s own documented guidance. This particular virtual machine has 7 GB RAM available. While the calculation is not quite the same as my Max Server Memory Matrix, it is a lot better than the default value of 2 petabytes.

For the record, I never change the Min Server Memory setting.

Regular readers of this blog will recall that I wrote a post about Max Server Memory recently. I am extremely happy when one of my posts 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 page:

• Firstly, the default settings are set to Default (in the screenshot above, I had already clicked on the Recommended radio button).
• Secondly, there is a lot of text under the two settings. Let’s take a look at what it says:

The displayed recommended values were calculated by Setup based on your system configuration and edition, unless these were explicitly specified in the Setup command line using the /SQLMINMEMORY and /SQLMAXMEMORY parameters.

• Thirdly, there is a checkbox which requires acceptance of the change to the Recommended settings.

Unless you specifically navigate to this tab and click on the Recommended option and then check the box, there is no change in behaviour from previous versions of SQL Server. This follows Microsoft’s long-standing tradition of backward compatibility. That said, if you normally configure SQL Server from the command line, these two memory options are now available as well.

What about Linux?

In the aforementioned post about Max Server Memory, I wrote this tidbit:

[T]here is a physical memory limit setting specific to SQL Server on Linux, which can be configured using mssql-conf. It is set to 80% of the total physical RAM by default. I do not advise changing this value, but you may have to tweak your max server memory setting to take this into account.

There is no need to change any setup options for a Linux install from the defaults, because the memory.memorylimitmb setting will limit SQL Server to 80% of the available physical RAM by default.

Share your thoughts in the comments below.

Photo by Joshua Newton on Unsplash.