By default, SQL Server’s max memory is 2147483647 – a heck of a lot more than you actually have.  Trivia time – that’s the max number for a signed 32-bit integer.  SQL Server will just keep using more and more memory until there’s none left on the system.

If the operating system has no memory available, it will start using the page file instead of RAM. Using the page file in place of memory will result in poor system performance – operations that should be fast and in memory will read and write to disk constantly. 

SQL Server really does need as much memory as possible, and what it uses memory for.  However, that doesn’t mean we should leave SQL Server’s max memory set to unlimited.  If we starve the OS for memory, then Windows itself may not get the memory it needs.

The max memory setting in SQL Server is set in megabytes, not gigabytes. So if you intend to tell SQL Server to use 20GB as it’s max memory setting, you’d set that to 20480 (the equivalent in megabytes). 

Decide what to set your max server memory (MB) to. Our simple “starter” rule of thumb is to leave 4GB or 10% of total memory free, whichever is LARGER on your instance to start with, and adjust this as needed.

SELECT c.value, c.value_in_use
FROM sys.configurations c WHERE c.[name] = 'max server memory (MB)'

Recommendations and best practices

Let us now briefly look at the techniques to size the memory.

  1. 1 GB of memory reserved for Operating System
  2. 1 GB each for every 4 GB of RAM after the initial 4 GB, up to 16 GB of RAM
  3. 1 GB each for every 8 GB in more than 16 GB of RAM

For example, if you have a 32 GB RAM Database Server, then memory to be given to Operating System would be

  1. 1 GB, the minimum allocation
  2. + 3 GB, since 16 GB – 4 GB = 12 GB; 12 GB divided by 4 GB (each 4 GB gets 1 GB) is 3GB.
  3. + 2 GB, as 32 GB – 16 GB = 16 GB; 16 divided by 8 (each 8 GB after 16 GB gets 1 GB) is 2 GB

So, in total, for a server with 32 GB of RAM, 7 GB will be reserved for the Operating System. This is the maximum memory allocated to SQL Server should be 25 GB. Similarly, for a 64 GB Server, 10 GB should be reserved for the Operating System & 54 GB should be allocated for SQL Server.

Example:

1 GB X 1024 = 1024 MB

Server MemoryWindows MemoryRecommended Max SQL Server Memory
16 GB6 GB10 GB (10,240 MB)
24 GB 4 GB20 GB (20,480 MB)
32 GB7 GB25 GB (25,600 MB)
64 GB10 GB54 GB (55,296 MB)

Sources:

https://www.brentozar.com/blitz/max-memory/

Last modified: October 15, 2020

Author

Comments

Write a Reply or Comment