Hybrid Buffer Pool using all conventional memory on SQL Server 2019 CTP 3.0 when trace flag 834 is enabled
In SQL Server 2019 CTP 3:
1. Set your max server memory so that SQL Server doesn't use all the memory on the box - in my case, I'm using a 16GB VM, and I set max memory to 10GB
2. Enable Locked Pages in Memory and trace flag 834 (large pages)
3. Restart the SQL Server. SQL Server uses the appropriate amount of memory, say 10GB.
4. Enable Hybrid Buffer Pool:
1. Enable hybrid buffer pool:
EXEC sp_configure 'show advanced', 1;
EXEC sp_configure 'hybrid', 0
And restart the SQL Server. SQL Server instantly uses all of the memory on the server, regardless of max server memory settings.
Query to show the memory: https://gist.github.com/BrentOzar/46571c5b1fc7e36d2a235b67e71a4ad9
Attached screenshots show the behavior when 834 is off/on.
Expected behavior: Hybrid Buffer Pool should only use PMEM devices, not the operating system's conventional RAM. (I don't have a PMEM device, just a VMware VM.)
Brent Ozar commented
Also, this may help - the error log is showing Hybrid Buffer Pool is proposing to use 6.1 terabytes of memory. Seems a little ambitious for a 16GB VM hosting 50GB of data.