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:
Then we can enable the buffer pool extension feature:
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:
I have also 3 big tables in this database: dbo.bigTransactionHistory_rs1 (2.2GB), dbo.bigTransactionHistory_rs2 (2.1 GB) and BigTransactionHistory (1.2GB)
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:
Is it possible to find some pages in the buffer pool extension part that concerns the table bigTransactionHistory_rs1?
I chose the first page 195426 and I finally corrupted it
DBCC WRITEPAGE(AdventureWorks2012, 1, 195426, 0, 2, 0x0000);
Then, let’s take a look at the page with ID 195426 to see if it still remains in the BPE:
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;
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:
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):
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.
By David Barbarin