SQL Server 2014 Buffer Pool Extension (BPE) on SSD

Adding SSD to a SQL Server

Setup of host machine

We have added 8 SSDs to the host, all drives are unallocated apart from disk 5 the boot drive

Add physical disks to VM

We then add the unallocated disks to the VMWare settings as physical disks

Create E: drive on VM

We then see the disk in disk manager within the VM and add a spanned volume of 528GB as the E: drive

Add SSD buffer pool extension to SQL Server

We make a folder and add a 50GB bufferpool extension on the E: drive, from the sql server errorlog we see this took no time at all to allocate.

We also see the entry in sys.dm_os_buffer_pool_extension_configuration giving the filename,state and size. NOTE the state is "CLEAN PAGE CACHING ENABLED"

Finally we also see that sys.dm_os_buffer_descriptors has a column is_in_bpool_extension which shows if the data page is in the buffer pool extension.

Maximum size of SSD buffer pool extension to SQL Server

Given we have max server memory set to default if we try to allocate more than 32 times available memory we get error 864 which is a documented limitation.

Server shutdown

On server shutdown the SSD .BPE file is deleted however no message is written to the errorlog:

2014-05-04 12:35:26.33 spid8s      Recovery is complete. This is an informational message only. No user action is required.
2014-05-04 12:35:58.25 spid27s     Service Broker manager has shut down.
2014-05-04 12:35:58.25 spid8s      .NET Framework runtime has been stopped.
2014-05-04 12:35:58.28 spid8s      SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
2014-05-04 12:35:58.29 spid8s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

On server startup the SSD .BPE file is reinitialized and a message is written to the errorlog

2014-05-04 12:39:01.12 spid8s      Starting up database 'master'.
2014-05-04 12:39:01.18 Server      CLR version v4.0.30319 loaded.
2014-05-04 12:39:01.24 spid8s      Buffer pool extension "E:\SSD1\Example.BPE" has been initialized successfully with size is 51200 MB.
2014-05-04 12:39:01.24 spid8s      Resource governor reconfiguration succeeded.
2014-05-04 12:39:01.24 spid8s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2014-05-04 12:39:01.24 spid8s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2014-05-04 12:39:01.28 spid8s      SQL Trace ID 1 was started by login "sa".

Turn off SSD extension

Finally we can turn off the SSD extension and the file is deleted.