The new feature of SQL Server 2014, buffer pool extension is a server configuration that could potentially help us resolve I/O bottlenecks without the usual method of adding more DRAM.
In this could configuration we could have a seamless integration of solid-state drive (SSD) preferably or any nonvolatile storage to the database engine. The buffer pool extension feature extends the buffer pool cache by accommodating a larger database working set, which forces the paging of I/Os between RAM and the SSDs. This effectively offloads small random I/Os from mechanical disks to SSDs. Because of the lower latency and better random I/O performance of SSDs, the buffer pool extension significantly improves I/O throughput.
Let us see how we could set the buffer pool extension. Check if we already have the buffer pool first
ALTER DATABASE ContosoRetailDW SET COMPATIBILITY_LEVEL = 110; go use ContosoRetailDW go --check buffer pool extensions is enabled-- select * from sys.dm_os_buffer_pool_extension_configuration go
This confirms we don’t have any buffer pool set up for ContosoRetailDW database. Next let us see how much storage space we have before making changes
Since we are short on C: let us create on D: I have 50GB so let us use half of the space we have (24GB) there for buffer pool.
Now let us create the buffer pool using the following script
>--turn buffer pool extesion on-- alter server configuration set buffer pool extension on (filename = 'd:\bufferExtensions\dbandbiBufferCache.bpe',size = 24gb); go --Check if we created the buffer pool select * from sys.dm_os_buffer_pool_extension_configuration; go
--monitor buffer pool extensions-- select * from sys.dm_os_buffer_descriptors;
--or if you need more descriptive values you may use this select db_name(database_id) databaseName, file_name(file_id) fileName, CASE WHEN page_id = 0 THEN 'File Header Page m_type 15' WHEN page_id = 1 OR page_id % 8088 = 0 THEN 'PFS m_type 11' WHEN page_id = 2 OR page_id % 511232 = 0 THEN 'GAM m_type 8' WHEN page_id = 3 OR (page_id - 1) % 511232 = 0 THEN 'SGAM m_type 9' WHEN page_id = 6 OR (page_id - 6) % 511232 = 0 THEN 'DCM m_type 16' WHEN page_id = 7 OR (page_id - 7) % 511232 = 0 THEN 'BCM m_type 17' WHEN page_id = 9 AND file_id = 1 THEN 'Boot Page m_type 13' WHEN page_id = 10 AND DB_ID() = 1 THEN 'config page > sp_configure settings only present in master m_type 14' ELSE 'Other' END page_type, page_level, (select type_desc from sys.allocation_units where sys.allocation_units.allocation_unit_id = sys.dm_os_buffer_descriptors.allocation_unit_id) as allocation_type_desc, page_type, row_count, free_space_in_bytes, cast(free_space_in_bytes as numeric)/1024 as free_space_in_mega_bytes, is_modified, numa_node, read_microsec, is_in_bpool_extension from sys.dm_os_buffer_descriptors;
Finally to turn of the buffer pool extensions you may use the sql script blow
--to turn off the buffer pool extension-- alter server configuration set buffer pool extension off; go