RJ's SQL Server and MySQL Notes

Notes on SQL Server and MySQL

  • Advertisements

Size Does Matter

Posted by rjssqlservernotes on February 12, 2014

SQL Server 2014 with BPE (Buffer Pool Extension) allows, as its name implies, for the expansion of the working set onto SSDs essentially creating an enlarged buffer. The Buffer Manager will treat the configured SSDs as nonvolatile RAM (NvRAM) “significantly increasing I/O performance”.

Configuring the server to utilize the SSDs as BPE is accomplished by executing an ALTER SERVER command:

alter server configuration

set buffer pool extension

on (filename = ‘<your SSD drive letter>:<filename>.bpe’, size = 32GB)

The maximum size of the BPE is limited to [32 * (amount of primary memory)]; however, Microsoft recommends a ratio of 1:16 or less.

Verify the configuration with:

select * from sys.dm_os_buffer_pool_extension_configuration

In the past, I have recommended using solid state drives (SSD) when adding mechanical drives is not an option yet a client wants to increase read performance. Keep in mind that SSDs are not designed for write-intensive workloads and are classified by their Write Endurance Rating indicating the volume of writes before “failure”; therefore, careful consideration must be given to selecting the data to store on the SSD. Restructuring the data to segregate the read-only data for placement on the SSDs is trivial often significantly improving read performance; however, be forewarned this solution is not without its perils.

The SQL Server 2014 BPE feature is one of the most exciting new developments for performance optimization specialists allowing us to make our buffer bigger. This is a good solution when the server’ primary memory configuration is already maximized or there are budget constraints limiting the expansion of the server’s primary memory.

Remember, there are issues associated with using SSDs with a database whether as secondary or primary memory.


Sorry, the comment form is closed at this time.

%d bloggers like this: