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.
SQL Server max memory should be set at 80-90%. Aim for 80% max memory.
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 GB of memory reserved for Operating System
- 1 GB each for every 4 GB of RAM after the initial 4 GB, up to 16 GB of RAM
- 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 GB, the minimum allocation
- + 3 GB, since 16 GB – 4 GB = 12 GB; 12 GB divided by 4 GB (each 4 GB gets 1 GB) is 3GB.
- + 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 Memory | Windows Memory | Recommended Max SQL Server Memory |
| 16 GB | 6 GB | 10 GB (10,240 MB) |
| 24 GB | 4 GB | 20 GB (20,480 MB) |
| 32 GB | 7 GB | 25 GB (25,600 MB) |
| 64 GB | 10 GB | 54 GB (55,296 MB) |
| 78 GB | 18 GB | 60 GB (61,440 MB) |
| 128 GB | 20 GB | 108 GB (110,592 MB) |
Note:
- Changing SQL Server’s Max Server memory is an online option – you don’t need to restart SQL Server.
- Windows OS will be very slow if SQL Server is using up all the memory.
Comments