SQL Server 2014 new features-buffer pool extension (Extension)

SQL Server 2014 new features-buffer pool extension (Extension)

Supported versions

Buffer pool Extension This feature is available only for 64-bit SQL Server enterprise/business intelligence/standard versions.

Terminology interpretation

Buffer

In SQL Server, a buffer is a 8-KB memory page that is the same size as a data page or index page. Therefore, the buffer cache is divided into multiple 8KB pages. A page is retained in the buffer cache until the buffer manager needs to read more data into the buffer. Data is only re-written to disk after it has been modified. These in-memory modified pages are called dirty pages. When a page is identical to its database image on disk, the page is a clean page. The data in the buffer cache can be modified several times before it is written back to disk.

Buffer pool

Also known as buffer cache. A buffer pool is a global resource that is shared by all databases and is used to hold its cached data pages. The maximum and minimum size of the buffer pool cache is determined during startup or when the instance of SQL Server is dynamically reconfigured using sp_configure. This size determines the maximum number of pages in the buffer pool that can be slowed at any time in the running instance.

Benefits of Buffer pool expansion

1. Increase the throughput of random I/O

2. Reduce I/O latency

3. Increase throughput for processing transactions per unit of time

4. Significantly improved read performance

5. Software for customers to achieve a similar effect on the hybrid drive.

Buffer Pool Extension Details

SSD storage is used as an extension of the memory subsystem rather than as an extension of the disk storage subsystem. That is, with buffer pool extension files, the buffer pool manager can use DRAM and NAND flash memory to maintain a much larger hot paged pool in non-volatile random access memories supported by the SSD. This creates a multilevel cache hierarchy on the SSD, Level 1 (L1) as DRAM, Level 2 (L2) as the buffer pool extension file. Only clean pages are written to the L2 cache to help ensure data security. The Buffer Manager handles clean page movement between the L1 and L2 caches.

Shows a high-level architecture overview of the buffer pool relative to other SQL Server components.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/5d/f1/wkiom1unm-hbxj63aafmb0drioc670.jpg "border=" 0 "height=" 338 "/>

When buffer pool expansion is enabled, this feature specifies the size and file path of the buffer pool cache file on the SSD. This file is a contiguous storage range on the SSD that is statically configured during the start of the SQL Server instance. You can modify the configuration parameters for this file only if the buffer pool extension feature is disabled. When the buffer pool extension is disabled, all related configuration settings are removed from the registry. When the instance of SQL Server is closed, the buffer pool extension file is deleted.

The cache pool extension supports the following two modes

CW: Writes a clean page to the SSD only.

DW: Double write, that is, write dirty pages to both SSD and hard disk simultaneously.

1) Sequential writes are always written directly to the hard disk and are not written to SSD

2) The random write of the clean page is written to the SSD, since it is a clean page and there is already a copy on the hard disk.

3) When there is still space in the cache pool, the cache pool extension is in CW mode and only writes clean pages to the SSD.

4) when the cache pool space is full, you need to brush a portion of the dirty page to the hard disk, it is DW mode, dirty pages will be written to both SSD and hard disk.

Application Scenarios

In general, the cache pool extension is useful for reading a much higher-than-written OLTP system, which is not helpful for the following environments:

1. Data Warehouse. You should consider columnstore instead of cache pool extension

2. Write more OLTP.

3. A server with more than 64GB of memory

Best practices

We recommend that you follow these best practices.

1) The buffer pool expansion size can be up to 32 times times the Max_server_memory value. We recommend that the ratio of physical memory (max_server_memory) to the size of the buffer pool extension should not exceed 1:16. The ratio between 1:4 and 1:8 is the best. For information about setting the Max_server_memory option, see Server memory Server configuration options.

2) You should thoroughly test the buffer pool extension before you implement it in a production environment. When you are in production, avoid making configuration changes to the file or turning off the feature. Because the buffer pool size is greatly reduced when this feature is disabled, these activities can adversely affect server performance. When disabled, memory that is used to support this feature is not recycled until an instance of SQL Server is restarted. However, if you re-enable the feature, memory will be reused without restarting the instance.

How to use cache pool extensions for specific steps

First check the settings for SQL Server Max memory. It is recommended that the size of the cache pool extension be set to 16 times times or less of the maximum memory. Please do a full test before implementing into the production environment.

Use Mastergo exec sp_configure ' show advanced options ', 1RECONFIGURE with overridego exec sp_configure ' max server memory (MB) ' GO/*exec sp_configure ' max server memory (MB) ', [VALUE (MB)]reconfigure with overridego*/

The following script enables the cache pool extension to 10GB:

Use the Mastergo ALTER SERVER configurationset BUFFER POOL EXTENSION on (FILENAME = ' E:\DB_Cache\SQL2014. BPE ', SIZE = ten GB); GO The following script closes the cache pool extension: use Mastergo ALTER SERVER CONFIGURATION SET BUFFER pool EXTENSION off; GO

The following script changes the size of the cache pool extension:

Use Mastergo ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF; GO ALTER SERVER configurationset BUFFER POOL EXTENSION on (FILENAME = ' F:\SSDCACHE\Example.BPE ', SIZE = GB); GO

The following are the DMV and perfmon counter associated with cache pool expansion, which can be used to check settings and monitoring:

--dmv:select * from Sys.dm_os_buffer_pool_extension_configurationselect * from Sys.dm_os_buffer_descriptors where is_ in_bpool_extension = 1--perfmon counter:sql server:buffer manager:extension page reads/secsql Server:Buffer Manager: Extension page writes/secsql server:buffer manager:page reads/secsql server:buffer manager:page writes/secSQL Server: Readahead pages/sec

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1631022

SQL Server 2014 new features-buffer pool extension (Extension)