Buffer pool Extension (Extension) practice

The SQL Server 2014 cache Pool Extension (Extension) feature allows you to extend the cache pool to faster SSD storage. Provides a new way to extend the memory-intensive system.

The Buffer Pool extension can provide the following benefits:

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.

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.

Below 2 pictures, C: Clean page D: Dirty page R: Random S: Order

1. Sequential writes are always written directly to the hard disk and are not written to SSDs
2. The random write of a clean page is written to the SSD because, since it is a clean page, there is already a copy on the hard disk.
3. When there is room 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 and a portion of the dirty page needs to be brushed onto the hard disk, it is in DW mode and the dirty pages are written to both the SSD and the hard disk.

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

Here are the steps for how to use the Cache pool extension:

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.

1  UseMaster2 GO 3 4 EXECsp_configure'Show advanced Options',15 RECONFIGURE  withOVERRIDE6 GO7 8  9 EXECsp_configure'max server memory (MB)'Ten GO  One  A /* -  - EXEC sp_configure ' max server memory (MB) ', [VALUE (MB)] the RECONFIGURE with OVERRIDE - GO - */

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

1  UseMaster2 GO 3 4 ALTERSERVER CONFIGURATION5 SETBUFFER POOL EXTENSIONOFF;6 GO7 8 ALTERSERVER CONFIGURATION9 SETBUFFER POOL EXTENSION on   Ten(FILENAME= 'F:\SSDCACHE\Example.BPE', SIZE=  -GB); One GO

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

1 --DMV:2 Select *  fromsys.dm_os_buffer_pool_extension_configuration3 Select *  fromSys.dm_os_buffer_descriptorswhereIs_in_bpool_extension= 14 5 --Perfmon Counter:6SQL server:buffer manager:extension Page Reads/sec7SQL Server:buffer manager:extension Page writes/sec8SQL Server:buffer Manager:page Reads/sec9SQL Server:buffer Manager:page writes/secTenSQL server:readahead Pages/Sec

Original link: http://blogs.msdn.com/b/apgcdsd/archive/2014/12/18/sql-2014-6-buffer-pool-extension.aspx

Buffer pool Extension (Extension) practice