Sql-server – Buffer Pool Extension

memorysql server

There were very good articles in MSDN and all over, but none explained about the BPE size or max size setting or growth.

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (FILENAME = 'C:\Temp\BP_Extension.BPE', SIZE = 2 GB);

This is going to add the file of 2GB, but what I understand was this is the minimum size of buffer pool file which it will be starting with, and this will be the max memory size setting on sql server?

and is there a way i can set how large the BPE file can grown… even though i plan the SSD drive to be 1:4 ratio ( 1 being maxmemory setting in sql).. Could some one please clarify.

Best Answer

According to the official documentation:

The buffer pool extension size can be up to 32 times the value of max_server_memory. We recommend a 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.

If you have, say, 64 GB of physical RAM, your Max Server Memory setting would be 54272 MB (53 GB), which means your BPE should be a maximum of 848 GB (53 * the ratio of 16) or less, with 424 GB or 212 GB recommended for optimal size.

When you configure the BPE size with ALTER SERVER CONFIGURATION, that is the size of the cache, and is how large it will be. It will not grow unless you change it.

So make sure you pick the right size to begin with, and make sure your SSD is fast enough.

In summary (from the comments):

  • The maximum file size of BPE should be set as a part of the initial file creation, and this file size should be in a 1:4 ratio of Max Server Memory to Buffer Pool Extension, as per Microsoft's recommendation.

  • In practice, the BPE size is approximately the size of your flash storage, less the size of existing TempDB on that drive. In other words, you would use the leftover space on your PCIe/NVMe flash disk after sizing TempDB.

Related Question