Buffer Pool Extension – New feature in SQL Server 2014

Before diving into Buffer Pool Extension (BPE) details, it is worth taking a look at the Buffer Management to refresh your knowledge. You may find buffer management details @ http://technet.microsoft.com/en-us/library/aa337525(v=sql.105).aspx

Buffer Pool Extension (BPE) can use nonvolatile storage devices (Ex: SSD) for increasing amount of memory available for buffer pool (Buffer Cache), BPE acts as Second level of Cache. When a page is removed from Memory it will be written into BPE which can be easily pulled in future (This reduces the number of page reads from physical drive, this is the primary reason for performance gain). If the page being removed from memory is a Dirty Page, it will be written both to Physical Drive (.mdf) and BPE (SSD).

FYI:
Buffer_Pool_Extension_Architecture

Notes on the BPE size:
• Buffer Pool Extension (BPE) needs to be greater than max_server_memory configured or RAM size.
• Maximum allowed BPE is 32 times than max_server_memory configured
• Recommendation of BPE size is less than 16 times of max_server_memory configured
• Optimal BPE size is 4 to 8 times of max_server_memory configured
• Useful DMVs

select * from sys.dm_os_buffer_pool_extension_configuration 
select * from sys.dm_os_buffer_descriptors

Let’s try Demo!

-- Check existing configuration of Max Server Memory, store it to revert back the changes after the Demo
exec sys.sp_configure N'max server memory (MB)'
-- 4096


-- If you get error, enable Advanced Options and try again
EXEC sys.sp_configure N'show advanced options', N'1'  
GO
RECONFIGURE WITH OVERRIDE
GO


sys.sp_configure N'max server memory (MB)'
-- 4096 (This is my configuration)


-- Change Max Server Memory to 256 MB, Easy to fill 256 MB and make use of Buffer Pool Extension 
EXEC sys.sp_configure N'max server memory (MB)', N'4096'
GO
RECONFIGURE WITH OVERRIDE -- -- This will enable configured value to be pushed into Run-time 
GO



-- Configure Buffer Pool Extension with 2 GB (It should be greater than configured Max Server Memory)
ALTER SERVER CONFIGURATION 
	SET BUFFER POOL EXTENSION 
	ON ( FILENAME = 'D:\SQL\BPE\BPE.bpe' ,SIZE = 2 GB) -- a File will be created on this location with specified size 


-- Check Buffer Pool extension configuration 
select * from sys.dm_os_buffer_pool_extension_configuration -- Location and Size 



-- See if any pages are available in Buffer Pool Extension  (None at this stage)
select db_name(database_id) as DatabaseName, * from sys.dm_os_buffer_descriptors
	where is_in_bpool_extension = 1 


-- For demo purpose, I select all all rows from all tables of AdventureWorks Database, you may select from different databases to see if Buffer Pool extension is being used 
use AdventureWorks2012
select 'select * from ' + schema_name(schema_id)+'.['+name + ']' as ExecuteThisQuerySattements from sys.tables 

select * from Production.ScrapReason
select * from HumanResources.Shift
select * from Production.ProductCategory
select * from Purchasing.ShipMethod
--...


-- Now, you may see multiple pages being loaded into Buffer Pool extension
select db_name(database_id) as DatabaseName, * from sys.dm_os_buffer_descriptors
	where is_in_bpool_extension = 1 



-- Depending on fetching the data from Actual Memory/ Buffer Pool Extension (SSD)/ Hard Disk, time taken to pull data varies. 


-- Counters to see usage of Buffer Manager
select * from sys.dm_os_performance_counters
	where object_name like '%buffer Manager%'


-- -- ROLLBACK Changes 
-- Revert back Max Server memory to previous value 
EXEC sys.sp_configure N'max server memory (MB)', N'4096'
GO
RECONFIGURE WITH OVERRIDE
GO


-- Revert back change, disable Buffer Pool Extension 
ALTER SERVER CONFIGURATION
	SET BUFFER POOL EXTENSION OFF

Other Observations:
When you enable IO STATISTICS, you will see the reads from Buffer Pool extension as Physical Reads!

Reference:
Buffer pool Extension: http://msdn.microsoft.com/en-us/library/dn133176.aspx
Whitepaper: http://gsl.azurewebsites.net/Portals/0/Users/Projects/SSD/sigmod2010.pdf

Advertisements
This entry was posted in SQL, SQL Query, sql2014 and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s