Query the Differential Changed Maps in SQL Server

Question

Differential backups use the Differential Changed Map pages to determine what pages to backup. I'd like to investigate a backup strategy involving differential backups. For that purpose I'd like to automatically track the amount of changed data over time for multiple databases.

I'd like to programmatically query the Differential Changed Map to determine how many pages are changed.

This seems to be possible with DBCC PAGE according to http://www.practicalsqldba.com/2013/07/sql-server-understanding-differential.html. Is there a cleaner or "more documented" way than this? It seems like this will be awkward and error-prone to implement.

Answer 1

SQL Server 2019 contains a new DMV sys.sys.dm_db_page_info() (docs).

The function returns one row that contains the header information from the page.

This header information includes

diff_status         bit             Bit to indicate if diff status is changed 
diff_status_desc    nvarchar(64)    Description of the diff status bit

The bit column is 0 for clean pages and 1 for dirty pages. The description is NOT CHANGED or CHANGED respectively.

Let's say we create a new table and add a row.

use Sandpit;
create table t(c int);
insert t(c) values(1);

We can find page ids in the traditional ways or using another DMV

select t.c, pr.*
from t
cross apply sys.fn_PageResCracker(%%physloc%%) as pr;

For me this happens to return file 1, page 240. Plugging this into the DMV we get

select diff_status, diff_status_desc
from sys.dm_db_page_info(db_id(N'Sandpit'), 1, 240, N'DETAILED');

Which returns

diff_status diff_status_desc
----------- ----------------
1           CHANGED

A full backup should reset these flags and, sure enough, it does

backup database Sandpit to disk = N'C:\Dummy\Sandpit.bak'

select diff_status, diff_status_desc
from sys.dm_db_page_info(db_id(N'Sandpit'), 1, 240, N'DETAILED');

diff_status diff_status_desc
----------- ----------------
0           NOT CHANGED