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).
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
When you enable IO STATISTICS, you will see the reads from Buffer Pool extension as Physical Reads!
Buffer pool Extension: http://msdn.microsoft.com/en-us/library/dn133176.aspx