Sql Server 2014 - BPE I was wondering if there is any way to determine whether is convenient or not to enable Buffer Pool Extension at one of the servers I am administering.

I was thinking about some way to measure average memory usage, memory usage peaks and so on, in order to determine if available RAM is enough of not.

Thanks in advance.
Before concluding whether you should or should not enable BPE, its good to analyse the current usage of memory on the SQL server.

You can do this with help of system health session, various DMV's and using perfmon counters:


Using system Health session you can refer here
Various DMV's you can consider for monitoring memory usage:


select * from sys.dm_os_memory_clerks

select * from sys.dm_os_memory_objects
select * from sys.dm_os_memory_pools
select * from sys.dm_os_memory_nodes
select * from sys.dm_os_memory_cache_entries
select * from sys.dm_os_memory_cache_hash_tables


Refer here on how to use above dmv's and analyzing for memory usage.


For perfmon you can start analyzing with below counters:


Memory – Available MBytes

Paging File – % Usage

Physical Disk – Avg. Disk sec/Read

Physical Disk – Avg. Disk sec/Write

Physical Disk – Disk Reads/sec

Physical Disk – Disk Writes/sec

Processor – % Processor Time

SQLServer: General Statistics – User Connections

SQLServer: Memory Manager – Memory Grants Pending

SQLServer: SQL Statistics – Batch Requests/sec

SQLServer: SQL Statistics – Compilations/sec

SQLServer: SQL Statistics – Recompilations/sec

On how to use them , please refer here.

Now, once you have analyse the proper memory usage along with how much stays out there on Buffer pool as well and how much you think it lacks, can help you in deciding should you go for enabling BPE or not.

Note: In addition to above , make sure the MAX server memory settings are properly configured. Adding below info in case you want to go with BPE


  Buffer Pool Extensions are available for the SQL Server 2014
  Enterprise, Business Intelligence and Standard x64 editions. The size
  of the buffer pool extension can be up to 32 times the value of
  max_server_memory for the SQL Server Enterprise edition and up to 4
  times the max_server_memory value for Business Intelligence and
  Standard editions. Microsoft recommends 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 good starting point is 1:4.


A suggested read on using BPE along with its Best practices  Buffer Pool Extension