One of the SQL Server settings with critical impact that is often misconfigured is the maximum memory setting. SQL Server will attempt to take as much memory as possible and is not always eager to release it. This results in the operating system being forced to start actively using the page file. While SQL Server might seem perfectly happy and show good page life and buffer hit ratios, the operating system might in fact be suffering.

One of the signs that this is happening are entries in the SQL Server logs that look like this:

[Something] is marked for unload due to memory pressure.

This doesn’t necessarily mean that your machine doesn’t have enough memory, but it does mean that your operating system isn’t getting its fair share. Ironically, the more resources you put into your server, the more resources your operating system needs to work well. There are some older rules residing on the internet telling you to reserve 4 GB for the operating system and to allocate the rest to the SQL Server. This worked like a charm up to approximately 16 to 20 GB, but with current machines having way over 512 GB this rule has been overruled. The same happened with the page file: if your SQL needs a page file bigger than 16 GB, you’re already in trouble.

Why is this so problematic?

First of all, paging out means writing to the page file, which dramatically increases access latencies and makes both your server and response times unreliable. Secondly, when your operating system starts to page itself out, the system becomes sluggish and this could cause your server to become unstable. SQL Server still needs your operating system for all the I/O and network operations, which means that if the operating system becomes slower, so do your transactions.

Things get really problematic when your host bus adapters don’t have enough memory to do their job, causing the I/O to freeze at intervals. As always, this problem only gets bigger if you’re virtualizing with memory overcommitment or running multiple instances on one machine. Especially if you’ve enabled the ‘lock pages in memory’ policy for your SQL Server. Of course, this changes if you use SSD or memory-based disks for your page file, but why would you use such expensive resources when adding memory is much cheaper and more reliable/durable? What people tend to forget is that SSDs require a lot of memory themselves to function properly.

How do we determine what memory your operating system actually needs?

The only good answer is: by iteration. But unfortunately this isn’t applicable in large environments where we have tens or even hundreds of SQL instances running. With a stable server you should always try to get stable RAM management, with the usage of the page file limited and the RING_BUFFER_RESOURCE_MONITOR showing few changes in memory distribution.

Some practical advice

Using a modified version of Jonathan Kehayias’s formula described in his free ebook “Troubleshooting SQL Server: A Guide for the Accidental DBA“, we can calculate a good starting point. This book was written in 2011, when servers with terabytes of RAM were still fairly uncommon, so I’ve adjusted it a bit to take the bigger machines into consideration.

Prerequisites/Disclaimer

My script takes into account multiple instances, but doesn’t take into account data warehouses or SSAS/SSRS/SSIS. For these types of machines a different kind of calculation is needed. And as always, this isn’t a one-size-fits-all solution. But it should give you a starting point for checking if your currently defined setting is in the range of the expected value and a better value than the defaults. If you’re already tuning this setting, you’ll probably want to look at your ring buffers and then use the query.

Let the scripting begin

Parameters

DECLARE @NumberOfInStances tinyint=0 -- 0 is the default, taking all discoverable instances
DECLARE @EvenlyDevided bit=1
DECLARE @Instance01Percentage tinyint=50
DECLARE @MaxPercentCompliance tinyint=85
DECLARE @MaxCalculated float
DECLARE @CurrentlyAllocated float

Definition of the parameters

@NumberofInstances: If you have several instances installed but some are disabled or only run in disaster scenarios, you may want to manually adjust this figure to the number of active instances.
@EvenlyDivided: If you set this to 0, not all instances will be treated equally. For example, you might have an SQL Express installed. You will probably also want to change this setting if you’ve changed the @NumberOfInstances parameter.
@Instance01Percentage: Gives the percentage of memory allocated to the current instance, not taking into account the settings of the other instances.
@MaxPercentCompliance: Indicates the compliance between your own setting and the calculated value.

Define or get the number of installed instances:

IF @NumberOfInStances=0
BEGIN
DECLARE @GetInstances TABLE
( Value nvarchar(100),
InstanceNames nvarchar(100),
Data nvarchar(100))

Insert into @GetInstances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'
  Select @NumberOfInStances=COUNT(*) from @GetInstances 
END

Get the currently allocated amount of memory:

SET @CurrentlyAllocated=(SELECT CAST(value_in_use as float) FROM sys.configurations
WHERE name like 'max server memory%')

Check the parameters:

--Check Params
IF (@EvenlyDevided = 0 AND NOT @Instance01Percentage between 1 AND 100)
BEGIN
      PRINT 'Input not supported, switching to EvenlyDevided'
      SET @EvenlyDevided = 1
END

Calculate the max server memory for these conditions:

SET @MaxCalculated = (SELECT CASE WHEN @NumberOfInStances=1 THEN
            FLOOR(CEILING(CEILING(physical_memory_in_bytes/1073741824.0)-(1+CASE
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 16 THEN FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/4.0)
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 32 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-16)/8.0)+4
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 64 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-32)/8.0)+6
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 128 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-64)/16.0)+8
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 256 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-128)/32.0)+12
                  ELSE FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/64.0)+16
            END))*1024.0) 
            WHEN @EvenlyDevided=1 THEN 
                  FLOOR(CEILING(CEILING(physical_memory_in_bytes/1073741824.0)-(1+CASE
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 16 THEN FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/4.0)
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 32 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-16)/8.0)+4
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 64 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-32)/8.0)+6
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 128 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-64)/16.0)+8
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 256 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-128)/32.0)+12
                  ELSE FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/64.0)+16
                  END))/@NumberOfInStances*1024.0)
            WHEN @EvenlyDevided=0 THEN 
                  FLOOR(CEILING(CEILING(physical_memory_in_bytes/1073741824.0)-(1+CASE
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 16 THEN FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/4.0)
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 32 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-16)/8.0)+4
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 64 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-32)/8.0)+6
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 128 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-64)/16.0)+8
                  WHEN CEILING(physical_memory_in_bytes/1073741824.0) <= 256 THEN FLOOR(CEILING((physical_memory_in_bytes/1073741824.0)-128)/32.0)+12
                  ELSE FLOOR(CEILING(physical_memory_in_bytes/1073741824.0)/64.0)+16
                  END))*@Instance01Percentage*10.240)
            ELSE 0
      END
FROM sys.dm_os_sys_info )

Plot the results:

PRINT 'Starting calculation with the following parameters: '
PRINT '@Evenlydevided : '+CAST(@Evenlydevided as varchar(4))
PRINT '@Instance01Percentage : '+CAST(@Instance01Percentage as varchar(4))
PRINT '@MaxPercentDerivation : '+CAST(@MaxPercentDerivation as varchar(4))
PRINT '@NumberOfInStances: '+CAST(@NumberOfInStances as varchar(4))
PRINT '@CurrentlyAllocated: '+CAST(@CurrentlyAllocated as varchar(20))
PRINT 'Calculated Max Memory to be: '+CAST(@MaxCalculated as varchar(20))
PRINT 'Start checking existing parameters'

Report if there is an issue (and also generate a configure script):

SELECT 
      CEILING(physical_memory_in_bytes/1048576.0) as [Physical Memory_MB], 
      CEILING(physical_memory_in_bytes/1073741824.0) as [Physical Memory_GB], 
      CEILING(virtual_memory_in_bytes/1073741824.0) as [Virtual Memory GB] ,
      @CurrentlyAllocated as CurrentlyAllocated,
      @MaxCalculated as Max_SQL_Memory,
      'EXEC sys.sp_configure N''max server memory (MB)'', N'''+
      CAST(@MaxCalculated as varchar(20)) +'''
GO
RECONFIGURE WITH OVERRIDE
GO' as command,
CASE WHEN (@CurrentlyAllocated between (@MaxCalculated * (@MaxPercentCompliance/100.0)) AND (@MaxCalculated * 1+((100-@MaxPercentCompliance)/100.0))) THEN 0
ELSE 1 END as warninglevel
FROM sys.dm_os_sys_info 
 
Example of a result set:
 
Starting calculation with the following parameters: 
@EvenlyDivided: 1
@Instance01Percentage: 50
@MaxPercentDerivation: 85
@NumberOfInstances: 3
@CurrentlyAllocated: 19968
Calculated Max Memory to be: 13312

Start checking existing parameters