BPE and max server memory setting.

Question

What is the right value for max server memory setting when BPE is enable. We have a sql server with 220G of RAM and BPE size of 512G. One of our dba read it to set max server memory value equivalent to size of BPE file , that is 512G in this case. Is this correct? and why is it set to this value or what is the optimal value for max server memory if BPE is enabled.

Answer 1

You do not change the "max_server_memory" based on the BPE size. This is only the RAM size and does not include BPE.

See:
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/buffer-pool-extension?view=sql-server-ver15#capacity-limitations

Answer 2

thanks for the clarification. We currently have 250G of physical memory on the server. One of my DBA did following changes

1. added BPE of size 512G
   2.set "max server memory" to 512G

will this cause any problem, because as per your point "max server memory" should be set to physical memory on the server. So far its been 3 days and we have not see any issues related to this. Any pointers will be helpful.

Answer 3

Hi SQLServerBro,

Agree with Tom.
Setting the value of max_server_memory too high may cause SQL server to consume memory from other processes or the operating system.

You can use the use the following generalized best practice approach to configure max_server_memory for a single instance:

• From the total OS memory, subtract the equivalent of potential SQL Server thread memory allocations outside the max server memory control, which is comprised of stack size * calculated max worker threads.
• Then subtract 25% for other memory allocations outside the max server memory control, such as backup buffers, extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers. This is a generic approximation, mileage may vary.
• What remains should be the max_server_memory setting for a single instance setup.

In addition, set the buffer pool extension so the ratio between the size of the physical memory (max_server_memory) and the size of the buffer pool extension of 1:16 or less. A lower ratio in the range of 1:4 to 1:8 may be optimal. Please refer to this doc which might be helpful.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Thank you for the response. I have couple of questions .

1.So now since the max server memory setting has been set to 512G which is more than the physical memory what kind of waits /error sql server may try to show in case of a problem?
2. by default max server memory is set at 2147483647 MB, so thats the same scenario as i have here. meaning if someone keeps the default setting for the server are they bound to run into issues? OR does sql server controls memory usage irrespective of max server memory settings

all two questions are assuming server is dedicate for sql server only with 1 instance running.

Answer 5

Hi SQLServerBro,
Thanks for your reply.

So now since the max server memory setting has been set to 512G which is more than the physical memory what kind of waits /error sql server may try to show in case of a problem?

SQL Server consumes memory from other processes or the OS, and keeps using more and more memory until there is no more memory left on the system. If the OS has no memory available, it will start using the page file instead of RAM. Using the paging file instead of memory can result in poor system performance - operations that should be fast and in-memory are constantly reading and writing to disk.

by default max server memory is set at 2147483647 MB, so thats the same scenario as i have here. meaning if someone keeps the default setting for the server are they bound to run into issues? OR does sql server controls memory usage irrespective of max server memory settings

By default, SQL Server can change its memory requirements dynamically based on available system resources. When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. Maintaining this free memory prevents the OS from paging. If less memory is free, SQL Server releases memory to the OS. If more memory is free, SQL Server may allocate more memory. SQL Server adds memory only when its workload requires more memory. Please check this doc for more details.

Best Regards,
Amelia