Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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;
RECONFIGURE
EXEC sp_configure 'hybrid', 0
RECONFIGURE

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

6 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Brent Ozar shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base