Share via

SQL Server Buffer Pool

  • Article

                                                                https://msdnshared.blob.core.windows.net/media/2016/08/0841.NinjaAwardTinySilver.pngSilver Award Winner       

Source References / Credits

Buffer Pool

↑ Back to top

Is the place in system memory "that holds data and index pages read from disk". This has two purposes:

  • "To modify pages according to INSERT and UPDATE statements. Those pages are marked as "dirty" and are flushed to disk when a checkpoint is performed.
  • To increase response time when retrieving the same data." [Source: MSSQL Tips]

"The maximum size of the buffer pool extension file is also limited based on the edition of SQL Server:

  • Standard edition:the buffer pool extension file size can be up to 4 times current memory allocation
  • Enterprise edition:the buffer pool extension file size can be up to 32 times current memory allocation" [Source: Logicalread]

Block Diagram

↑ Back to top

Check PBE configuration

↑ Back to top

--Review current BPE configuration
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;
--Reduce SQL Server Max memory to restrict the BP and force the use of BPE
EXEC sys.sp_configure 'showadvanced options','1'

RECONFIGURE WITH  OVERRIDE;

GO

EXEC sys.sp_configure 'maxserver memory (MB)','2000';
GO

RECONFIGURE WITH OVERRIDE;

GO

EXEC sys.sp_configure 'showadvanced options','0'

RECONFIGURE WITH  OVERRIDE;


--look at  the file size  on disk right after  you run this

ALTER SERVERCONFIGURATION
SET
BUFFER
POOL EXTENSION ON
(FILENAME = 'F:\Temp\BP_Extension.BPE',SIZE= 2 GB);
--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;

--Turn BPE off. Go look in c:\temp to see what happens to the physical data file
ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION OFF;

Altering SQL Server Buffer Pool Extension Size:

↑ Back to top

USE master
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF; ALTER  SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON(FILENAME ='F:\Temp\BP_Extension1.B',SIZE= 5 GB);

--Put Max Server Memory back where it was
EXEC sys.sp_configure'show advanced options','1'
RECONFIGURE WITH  OVERRIDE;

GO

EXECsys.sp_configure 'max servermemory (MB)','3500';

GO

RECONFIGUREWITH OVERRIDE;

GO

EXECsys.sp_configure 'showadvanced options','0'
RECONFIGURE WITH  OVERRIDE;
GO

Critical Considerations for Use of Buffer Pool Extensions

↑ Back to top

From [Logicalread]

"Finally, a few critical considerations to keep in mind:

  1. Planning is more important for this feature because we can’t reduce the file size once its current size.
  2. Whilethis feature can be very useful, it is not a substitute of adding more RAM.
  3. This feature is very useful forOLTP workloads.
  4. We will get the mostbenefit from this feature when the extension file is on faster media." [Logicalread]

BPE was designed to address this issue by using the non-volatile memorytoextendthebuffer pool.If sql server restarted thenBP_Extension1.BPE will vanish from F:\temp directory.

Errors

1.create BPE without checking it

What happen we will create BPE without checking it. Msg 863, Level 16, State 1, Line 15

Attempt to enable buffer pool extension when in state BUFFER POOL EXTENSION CLEAN PAGE CACHING ENABLED is not allowed.

Solution

check already configuration is there.Make it off

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;

then configure Buffer pool EXTENSION

ALTER SERVER CONFIGURATION /code>SET BUFFER POOL EXTENSION On(FILENAME ='F:\Temp\BP_Extension1.BPE',SIZE==2 GB);

2. Server Stopped and directory removed

↑ Back to top

What happen if sql server is stopped , F:\temp directory removed where PBE file is present and start sql server ?

BPE is disabled on server.

Following error will show in sql error log.

Error: 5123, Severity: 16, State: 21.

2018-04-20 22:14:14.840 spid6s Error: 5123, Severity: 16, State: 21.

2018-04-20 22:14:14.840 spid6s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'F:\Temp\BP_Extension.BPE'.

Other Resources

Won silver award in april 2018