Turbocharging DBMS Buffer Pool Using SSDs<\/a> written by Do, J., Zhang, D., Patel, J., DeWitt, D., Naughton, J., and A. Halverson. I also listened to one of the interview of Evgeny Krivosheev (SQL Server Program Manager) about the buffer pool extension feature. I would like to precise that there are still blog posts on the web which claim some workloads work better with the buffer pool extension feature. Of course, the quick answer is yes for the reasons described above and this is not my goal here. This post is just an opportunity to share with you some of my investigations – but before, I have to warn you: We are still talking about CTP2 of SQL Server 2014 and some of the tools I used do not work perfectly. We will see this later in the post.<\/p>\nThe first thing I noticed is the decision made to store data in SSD at the granularity level of pages rather than tables or files. This point is important because we can retrieve different pages of a table either in the memory, or in the SSD, or in the disk. As mentioned above, only clean pages are written to the buffer pool extension. There is still an exception that consists of writing a modified page (dirty) both into the database mdf file and into the BPE. This is a kind of optimization called dual-write optimization.<\/p>\n
Let\u2019s start with my first test in which I used a custom AdventureWorks2012 database with two bigTransactionHistory tables. Here some details about the size and number of rows of each of those tables:<\/p>\n
The bigTransactionHistory table contains 31 billion of rows for a size of 2 GB approximately (data and indexes).<\/p>\n
USE AdventureWorks2012;<\/span><\/span><\/span><\/span><\/div>\nGO<\/span><\/div>\n\u00a0<\/span><\/div>\nEXEC sp_spaceused‘dbo.bigTransactionHistory’;<\/span><\/span><\/span><\/span><\/span><\/div>\nGO<\/span><\/div>\n![\"billet4_1_adventureworks_bigtransactionhistory_size\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/billet4_1_adventureworks_bigtransactionhistory_size.jpg\")
<\/p>\n
EXEC sp_spaceused‘dbo.bigTransactionHistory_2’;<\/span><\/span><\/span><\/span><\/span><\/div>\nGO<\/span><\/div>\n![\"billet4_2_adventureworks_bigtransactionhistory_2_size\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/billet4_2_adventureworks_bigtransactionhistory_2_size.jpg\")
<\/p>\n
In my test, the AdventureWorks2012 database is configured as follows:<\/p>\n
USE AdventureWorks2012;<\/span><\/span><\/span><\/span><\/div>\nGO<\/span><\/div>\n\u00a0<\/span><\/div>\nSELECT<\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name,<\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 physical_name<\/span><\/span><\/div>\nFROM sys.database_files;<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\nGO<\/span><\/div>\n![\"billet4_3_adventureworks_db_configuration\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/billet4_3_adventureworks_db_configuration.jpg\")
<\/p>\n
The mdf and ldf files are stored both on a slow drive (E:) which is a WDC USB disk (5200 RPM) with SATA\/300. This is not an optimal configuration but for this test it’s ok.
\nThe buffer pool maximum size on my SQL Server instance is voluntarily fixed to 700 MB.<\/p>\n
SELECT<\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name,<\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 value_in_use<\/span><\/span><\/div>\nFROM sys.configurations<\/span><\/span><\/span><\/span><\/span><\/div>\nWHERE name = ‘max server memory (MB)’<\/span><\/span><\/span><\/span><\/span><\/div>\n![\"billet4_5_bpe_configuration\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/billet4_5_bpe_configuration.jpg\")
<\/p>\n
Finally, I set up the maximum size of the buffer pool extension to 2 GB:<\/p>\n
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\nON<\/span><\/div>\n(<\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAssdbuffer_pool.bpe’,<\/span><\/span><\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SIZE = 2048 MB<\/span><\/span><\/span><\/span><\/div>\n);<\/span><\/div>\nGO<\/span><\/div>\n\u00a0<\/span><\/div>\nSELECT<\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [path],<\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 state_description,<\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 current_size_in_kb \/ 1024 AS current_size_mb<\/span><\/span><\/span><\/span><\/span><\/div>\nFROM sys.dm_os_buffer_pool_extension_configuration;<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n <\/p>\n
<\/p>\n
The buffer pool extension file is stored on fast solid-state drive (C:) which is a Samsung SSD 840 EVO with a SATA\/600<\/p>\n
At this point, we can start the first test by loading the entire table bigTransationHistory_2 in the buffer pool with a cold cache. Actually, data pages are stored only on the volatile memory as expected after clearing the buffer cache.<\/p>\n
— Clean buffer pool + BPE<\/span><\/div>\nDBCC DROPCLEANBUFFERS;<\/span><\/span><\/span><\/span><\/div>\nGO<\/span><\/div>\n\u00a0<\/span><\/div>\n— Check pages in BP for the AdventureWorks2012 database<\/span><\/div>\nSELECT<\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE is_in_bpool_extension<\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 1 THEN ‘SSD’<\/span><\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE ‘RAM’<\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END AS location,<\/span><\/span><\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*) AS nb_pages,<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*) * 8 \/ 1024 AS size_in_mb,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*) * 100. \/(SELECT COUNT(*) FROM sys.dm_os_buffer_descriptors) AS percent_<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\nFROM sys.dm_os_buffer_descriptors<\/span><\/span><\/span><\/span><\/span><\/div>\nWHERE database_id = DB_ID(‘AdventureWorks2012’)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\nGROUP BY is_in_bpool_extension;<\/span><\/span><\/span><\/span><\/span><\/div>\n <\/p>\n
![\"billet4_6_bpe_configuration\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/billet4_6_bpe_configuration.jpg\")
<\/p>\n
In the same time before the loading, I created an extended event session in order to track the page movement between the non-volatile memory (SSD) and the volatile memory (RAM) for the dynamic pool cache. During my test, I found out that some events are not tracked correctly (for example sqlserver.buffer_pool_extension_pages_read), probably because we are still in CTP2 of SQL Server 2014 \u2026<\/p>\n
CREATE EVENT SESSION [buffer_pool_extension]<\/span><\/span><\/span><\/span><\/span><\/div>\nON SERVER<\/span><\/span><\/span><\/div>\nADD EVENT sqlserver.buffer_pool_eviction_thresholds_recalculated,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\nADD EVENT sqlserver.buffer_pool_extension_pages_evicted,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\nADD EVENT sqlserver.buffer_pool_extension_pages_read,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\nADD
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg\")
<\/p>\n