USER DEFINED MEMORY MONITOR SERVER 2012 WINDOWS

We also check sys.dm_os_nodes to see if any NUMA nodes are reporting that memory is dangerously low, and can no longer create threads. We compare 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).

USER DEFINED MEMORY MONITOR SERVER 2012 WINDOWS

If we starve the OS for memory, then Windows itself may not get the memory it needs. However, that doesn’t mean we should leave SQL Server’s max memory set to unlimited. 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. You can learn more about low memory conditions in this Microsoft blog post. 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. If the operating system has no memory available, it will start using the page file instead of RAM.

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

User defined memory monitor server 2012