I had the opportunity to attend Paul Randal’s session on advanced data recovery techniques at the Pass Summit. During this session one attendee asked Paul if a page that has just been corrupted can remain in the buffer pool extension (BPE). As you probably know, BPE only deals with clean pages. Paul hesitated a lot and asked us to test and this is exactly what I will do in the blog post.

First, let’s start by limiting the maximum memory that can be used by the buffer pool:

— Configure SQL Server max memory to 1024 MB
EXEC sp_configure‘show advanced options’, 1;
GO
RECONFIGURE;
EXEC sp_configure‘max server memory (MB)’, 1024;
GO
RECONFIGURE;
GO

Then we can enable the buffer pool extension feature:

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION
ON
(
          — change the path if necessary
       FILENAME = N’E:SQLSERVERMSSQLSERVERDATAssdbuffer_pool.bpe’,
       SIZE = 4096 MB
);

I configured a buffer pool extension size with 4X the max memory value for the buffer cache

At this point I need a database with a big size in order to have a chance to retrieve some data pages in the buffer pool extension part. My AdventureWorks2012 database will fit this purpose:

USE AdventureWorks2012;
GO
 
EXEC sp_spaceused;

 

blog_23_-_1_-_adventureworks2012_size

I have also 3 big tables in this database: dbo.bigTransactionHistory_rs1 (2.2GB), dbo.bigTransactionHistory_rs2 (2.1 GB) and BigTransactionHistory (1.2GB)

blog_23_-_2_-_adventureworks2012_top_tables_size

I have a good chance to find out some pages related on these tables in the BPE, if I perform a big operation like a DBCC CHECKDB on the AdventureWorks2012 database.

After performing a complete integrity check of this database and executing some queries as well, here it is the picture of my buffer pool:

SELECT
       CASE is_in_bpool_extension
             WHEN 1 THEN ‘SSD’
             ELSE ‘RAM’
       END AS location,
       COUNT(*) AS nb_pages,
       COUNT(*) * 8 / 1024 AS size_in_mb,
       COUNT(*) * 100. /(SELECT COUNT(*) FROM sys.dm_os_buffer_descriptors(nolock)) AS percent_
FROM sys.dm_os_buffer_descriptors(nolock)
GROUP BY is_in_bpool_extension
ORDER BY location;

 

blog_23_-_21_-_buffer_pool_overview

Is it possible to find some pages in the buffer pool extension part that concerns the table bigTransactionHistory_rs1?

SELECT
       bd.page_id, da.page_type, bd.is_modified
FROM sys.dm_os_buffer_descriptors AS bd
       JOIN sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks2012’), OBJECT_ID(‘dbo.bigTransactionHistory_rs1’), NULL, NULL, DEFAULT) AS da
             ON bd.database_id = da.database_id
                    AND bd.file_id = da.allocated_page_file_id
                           AND bd.page_id = da.allocated_page_page_id
WHERE bd.database_id = DB_ID(‘AdventureWorks2012’)
                    AND bd.is_in_bpool_extension = 1
                           AND da.page_type IS NULL

 

blog_23_-_3_-_bigTransactionHistory_rs1_pages

I chose the first page 195426 and I finally corrupted it

DBCC WRITEPAGE(AdventureWorks2012, 1, 195426, 0, 2, 0x0000);

blog_23_-_4_-_corrupt_page

Then, let’s take a look at the page with ID 195426 to see if it still remains in the BPE:

SELECT
       page_id,
       is_in_bpool_extension,
       is_modified
FROM sys.dm_os_buffer_descriptors AS bd
WHERE bd.page_id = 195426

 

blog_23_-_5_-_check_location_page_after_corruption

Ok (fortunately) not 🙂 However we can notice that the page has not been tagged as “modified” by looking at the sys.dm_os_buffer_descriptors DMV. Hum my guess at this point is that using DBCC WRITEPAGE is not a classic process for modifying a page but in fact the process used by the BPE extension is not what we can imagine at the first sight.

Indeed, moving a page from BPE is almost orthogonal to the dirty nature of a page because the buffer manager will move a page into the memory because it becomes hot due to the access attempt. Modifying a page needs first access to the page (a particular thanks to Evgeny Krivosheev – SQL Server Program Manager – for this clarification).

We can verify if the page with ID 195426 is really corrupted (remember this page belongs to the bigTransactionHistory_rs1 table):

DBCC CHECKTABLE(bigTransactionHistory_rs1) WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS;

 

blog_23_-_6_-_check_corruption_for_data_page

Note some other corruptions but in this context it doesn’t matter because I performed some other corruption tests in this database 🙂
So the next question could be the following: Do you think a corrupted page can be moved from the buffer pool into the memory? … The following test will give us the response:

CHECKPOINT
GO
DBCC DROPCLEANBUFFERS;
GO
— Perform queries in order to full fill the buffer cache and its extension

We flush dirty pages to disk and the we clean the buffer cache. Afterward, I perform some others queries in order to populate the buffer cache (memory and BPE) with database pages. At this point we have only clean pages. A quick look at the buffer cache with the sys.dm_os_buffer_descriptor DMV give us the following picture (I recorded into a temporary table each time I found out the page ID 195426 into the buffer cache (either memory or BPE):

blog_23_-_7_-_find_out_a_corrupted_page_in_the_BPE

 

We can notice that a corrupted page can be part of the buffer pool extension and this is an expected behavior because the page ID 195426 is not dirty or modified but corrupted only at this point.

Enjoy!

By David Barbarin

Post Views: 906
Thumbnail [60x60]
by
Microsoft Team