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'; 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. |