Implementing Buffer Pool Extension in SQL Server

Figure: The Buffer pool and the buffer pool Extension

--Check current BP configuration

SELECT * FROM sys.configurations

WHERE name = 'Max Server Memory (MB)';

SELECT * FROM sys.configurations

WHERE name = 'show advanced options';

Also, you can check the Max Server Memory Graphically

SELECT [path], state_description, current_size_in_kb,

CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]

FROM sys.dm_os_buffer_pool_extension_configuration;

--To Demonstrate BPE Use, Reduce SQL Server Max memory to restrict the BP and force the use of BPE

EXEC sys.sp_configure 'show advanced options', '1' RECONFIGURE WITH OVERRIDE;

GO

EXEC sys.sp_configure 'max server memory (MB)', '1000';

GO

RECONFIGURE WITH OVERRIDE;

GO

EXEC sys.sp_configure 'show advanced options', '0' RECONFIGURE WITH OVERRIDE;

GO

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Configuration option 'max server memory (MB)' changed from 2147483647 to 1000. Run the RECONFIGURE statement to install.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

-- Note: WE HAVE TO RESTART SQL SERVER FOR BPE TO SEE TO RAM CHANGE

SELECT * FROM sys.configurations

WHERE name = 'Max Server Memory (MB)';

--Enable BPE

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON (FILENAME = 'D:\BP_Extension\BP_Extension.BPE', SIZE = 2 GB);

Command(s) completed successfully.

--Now that it is enabled, Let’s check the configuration again

SELECT [path], state_description, current_size_in_kb,

CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]

FROM sys.dm_os_buffer_pool_extension_configuration;

-- Create Large table by generating some random data so we can fill up buffer pool by querying the data

use nazmul

CREATE Table tblAuthors

(

Id int identity primary key,

Author_name nvarchar(50),

country nvarchar(50)

)

CREATE Table tblBooks

(

Id int identity primary key,

Auhthor_id int foreign key references tblAuthors(Id),

Price int,

Edition int

)

use nazmul

Declare @Id int

Set @Id = 1

While @Id <= 120000

Begin

Insert Into tblAuthors values ('Author - ' + CAST(@Id as nvarchar(10)),

'Country - ' + CAST(@Id as nvarchar(10)) + ' name')

Print @Id

Set @Id = @Id + 1

End

--Try to read enough data to fill BP and start using BPE

use nazmul

select * from [dbo].[tblAuthors];

--Let's see what went to BPE. If there are no results then go query more data.

SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],

CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],

AVG(read_microsec) AS [Avg Read Time (microseconds)]

FROM sys.dm_os_buffer_descriptors

WHERE database_id <> 32767

AND is_in_bpool_extension = 1

GROUP BY DB_NAME(database_id)

ORDER BY [Buffer size(MB)] DESC;

-- Note: is_in_bpool_extension 1= Page is buffer pool extension. Is nullable.

--Roll back the change

--Turn BPE off

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION OFF;

Command(s) completed successfully.

-- If you check the folder now, Its empty

--Put Max Server Memory back where it was

EXEC sys.sp_configure 'show advanced options', '1' RECONFIGURE WITH OVERRIDE;

GO

EXEC sys.sp_configure 'max server memory (MB)', '2147483647';

GO

RECONFIGURE WITH OVERRIDE;

GO

EXEC sys.sp_configure 'show advanced options', '0' RECONFIGURE WITH OVERRIDE;

GO

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Configuration option 'max server memory (MB)' changed from 200 to 2147483647. Run the RECONFIGURE statement to install.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.