What is Buffer Pool (BP)?
The
Buffer Pool (also known as the buffer cache) is a shared memory area which
consists of data/index pages. A buffer page is 8KB in size and the Max Server Memory determines the total size of the Buffer Pool. For example, when we set max
server memory=16GB, it means we are allocating 16GB of memory to the Buffer Pool.
The plan cache is also the part of the Buffer Pool.
What is Buffer Pool Extension (BPE)?
This
is a new feature in SQL 2014 where we can extend the Buffer Pool beyond the
limitation of the maximum physical memory. This means that we can use a
persistent disk file system as part of the buffer. The file can be placed in
any disk including the network shared folder.
Suitability of BPE:
The
primary goal of BPE implementation is to improve Read performance of an OLTP
application where physical memory is less than 64GB (according MSDN blogs). The
application which is more READ intensive and less WRITE prone, implementing this feature
will enhance I/O performance greatly. However, this feature is not suitable for data warehouse
and WRITE intensive type workload.
Where to place the file?
The
SSD provides very low random and read access latency, thus the best place to
put the BPE file in a SSD. If it is placed in a normal disk system, there will
be a negative impact on I/O.
BPE size:
The
BPE size can be 32 times greater than Max Server’s Memory. So if we set Max Server
Memory to 4 GB, then BPE can be 4X32 GB = 128 GB. Microsoft recommends 4 to 8
times of the amount of Max Server Memory which will be an optimal size for overall
I/O performance.
What will be written in a
BPE file?
The
BPE file only holds “clean pages” in it. A “clean page” (aka un-modified page) is
a data or index page which is exact and equivalent to a disk page residing in
the data file. A BPE file will not hold any “dirty pages” at any time.
Good to know and good practices:
Before
taking advantage of the features of BPE, we should be sure that adding extra
physical memory is not an option due to hardware limitation. In addition to
this, we should have optimal T-SQL code and we should not have I/O overhead index
schema such as overlapping, duplicating and multi-column indexes.
1. Placing the BPE file in a high
performance SSD is recommended.
2. Make sure the disk alignment
is correct.
3. Consider formatting the disk
with 64K block (cluster) size.
4. Set the Max Server Memory
and leave enough memory for the OS.
5. “Lock pages in Memory” has
no interaction with BPE.
6. Before modifying the BPE, the
Buffer Pool Extension feature should be disabled first.
7. When the BPE is disabled,
all related configuration settings are removed automatically and the memory used
to support the feature is not reclaimed until the instance of SQL Server is
restarted. The Buffer Pool Extension file will be deleted once the instance of
SQL Server is shutdown.
8. BPE can be configured and
disabled at any time while the instance of SQL Server is running. If the
feature is re-enabled, the memory will be reused without restarting the
instance.
9. As BPE contains only clean
pages, data loss is out of question.
How to configure BPE:
Configuring
BPE is very straight forward and simple. Following are the steps we need to
follow to configure a 16GB BPE file when the Max Server Memory is set to 4GB.
Set
the “max server memory” to 4GB:
EXEC sp_configure 'max server memory (MB)', 4096
GO
RECONFIGURE
Configure
the Buffer Pool Extention to 16GB:
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME =
'E:\SQLBuffer\ExtendedBuffer.BUF', SIZE = 16 GB);
To see the configuration, execute following DMV query:
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF
Querying BPE utilization:
The
following query can be used to identify utilization of BPE:
SELECT CASE
WHEN database_id =
32767 THEN 'ms_resource_db'
ELSE DB_NAME(database_id)
END AS database_name,
COUNT(*) AS
cached_pages_count,
CONVERT(NUMERIC(25, 4), COUNT(row_count) * 8.00 / 1024.00) AS size_mb,
CONVERT(
NUMERIC(25, 4),
COUNT(row_count) * 8.00 / 1024.00 / 1024.00
) AS size_gb
FROM sys.dm_os_buffer_descriptors
WHERE
is_in_bpool_extension = 1
GROUP BY
database_id
Performance measurement of
BPE:
There
are some performance counters that have been introduced in SQL 2014 to measure
BPE performance. It can be monitored using Windows PerfMon or with T-SQL.
Following is the T-SQL query:
SELECT [object_name],
counter_name, instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] LIKE
'%Extension%'
References:
Buffer
Pool Extension
Buffer
Pool Extension to SSDs in SQL Server 2014
