It appears that the answer is yes, even though SQL Server 2014 (RTM and CU1 at the time of this blog) allows you to create the Buffer Pool Extension (BPE) file on non-SSD media.
The current documentation indicates that SSDs are required to use the Buffer Pool Extension feature in SQL Server 2014 i.e.
‘Introduced in SQL Server 2014, the buffer pool extension provides the seamless integration of a nonvolatile random access memory (that is, solid-state drive) extension to the Database Engine buffer pool to significantly improve I/O throughput.’
As a simple test, I downloaded AdventureWorks2012-Full Database Backup.zip and restored it onto my local SQL Server 2014 test instance.
I ran the below to reduce the memory allocated to my SQL Server 2014 instance and to create a BPE file on my local non-SSD C drive.
sp_configure 'show advanced options',1; GO RECONFIGURE; GO EXEC sp_configure 'max server memory (MB)', 512; GO RECONFIGURE; GO ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'C:\BPE\20GB.BPE', SIZE = 20 GB); GO
We can see that a 20GB file was created.
Note: This file is removed when SQL Server is shutdown or BPE is disabled.
The following DMV also shows the BFE as being enabled:
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration
Next I confirmed that the file was not being used:
SELECT * FROM sys.sysperfinfo WHERE 1=1 AND counter_name LIKE 'Extension%'
The following query was run against the restored sample AdventureWorks2012 database:
USE AdventureWorks2012 GO EXEC sp_MSforeachtable 'SELECT * FROM ?' GO
When the performance counters were rechecked, we can see the BPE file was not being used:
As another test, when the BPE was created on a USB flash drive it was also not used.
What is interesting is that when I ran the exact same test with a BPE file on an Azure VM’s C drive and temporary storage D drive, the BPE was used and therefore it would indicate that Azure VMs make use of SSDs.
