Blitz Result: Memory Dangerously Low or Max Memory Too High
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. You can learn more about low memory conditions in this Microsoft blog post.
In our Sysadmin’s Guide to Microsoft SQL Server Memory, we explain why 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.
This part of our SQL Server sp_Blitz script compares the ‘max server memory’ setting in sys.configurations to the amount of memory the server actually has (as seen in sys.dm_os_sys_memory).
To Fix the Problem
Cap the amount of memory SQL Server can use in your environment. We explain the concepts in our Sysadmin’s Guide to SQL Memory, and for another take, check out Glenn Berry’s Suggested Max Memory Settings.
After this change, your server should be less vulnerable to paging to disk. If the system was already paging, then you should see increased performance. To double-check if you’re paging to disk, check Task Manager, Performance tab, and look at the Free Memory metric for Windows 2008. If it’s under 200, you’re in danger of swapping to disk.
