Embedded Files
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.
Page updated
Google Sites
Report abuse