sql-server,sql-server-2014 – SQL Server 2014 – Buffer Pool Extension (BPE)

sql serversql server 2014

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.

Best Answer

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:

  1. Using system Health session you can refer here

  2. 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.

  1. 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

Related Question