Monday, September 29, 2014

0 Buffer Pool Extensions in SQL Server 2014

This is new feature for SQL Server 2014.  We can use SSD to extend the buffer pool size. The idea is using the faster SSD , in the event when the physical memory need more space, it will use SSD instead of physical disk.  However, for testing purpose, the SQL server can use any disk as extension, it does not limit to SSD only. Database engine would not have idea whether it is SSD or not.

--- Check buffer pool extension configuration 
select * from sys.dm_os_buffer_pool_extension_configuration

image

The size of the Buffer pool extension must be large than current MAX server memory otherwise, we will get the error when configure the buffer pool extension

Msg 868, Level 16, State 1, Line 6
Buffer pool extension size must be larger than the current memory allocation threshold 8191 MB. Buffer pool extension is not enabled.

image

--- Enable 
--- For testing purpose, reduce the MAX memory to 1GB
EXEC sys.sp_configure N'max server memory (MB)', N'256'
GO
RECONFIGURE WITH OVERRIDE
GO
Alter server configuration set buffer pool extension on ( FILENAME='C:\SSD\buffer.xd',size=2GB );
image

Check buffer pool extension usage

 
select 
        CASE is_in_bpool_extension 
        WHEN 1 THEN 'SSD'
        ELSE 'RAM'
        END AS Location,
        is_in_bpool_extension,
        COUNT(*) AS nb_pages,
        COUNT(*)*8/1024 AS size_in_mb,
        COUNT(*)*100/(SELECT COUNT(*) from sys.dm_os_buffer_descriptors) AS percent_
from sys.dm_os_buffer_descriptors
Group by is_in_bpool_extension

image

Disable Buffer pool extension

--- Check buffer pool extension configuration 
select * from sys.dm_os_buffer_pool_extension_configuration

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates