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)'

Sources:

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

Last modified: May 31, 2019

Author

Comments

Write a Reply or Comment