Jonathan had a client issue recently where SQL Server’s buffer pool had been forced down to a ridiculously small size, only a few hundred MB, but the OS also showed basically no free memory. Page Life Expectancy was zero! What was going on?
From investigating SQL Server’s memory usage, the memory manager showed that target and total memory were the same, at only 1.2GB, and lock pages in memory was correctly set. The next thing to check was for ballooning in VMware – a common cause of memory issues – but this wasn’t the problem either.
Next, querying sys.dm_os_sys_memory showed