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.


Comments