SQL Server: Understanding DCM Pages

Original: SQL Server: Understanding DCM Pages

We've talked about various pages, including data pages, GAM and SGAM pages, PFS pages, and IAM pages. Today we'll look at the variance page (differential change MAP:DCM) and differential backup (differential backups).

Variance page (differential change MAP:DCM ) : SQL Server uses the diff change page to track areas that have been modified since the last full backup. The DCM page is the 6th page in the data file. The DCM page is used to track areas that have been modified since the full backup. DCM uses each bit for each tracked area to record. If this bit is set to 1, the area has been modified since the last full backup. If this bit is set to 0, the zone has not been modified since the last full backup. A DCM page can hold information about 64,000 areas. Every 511232 pages, DCM repeats one. A DCM page can track change information for 63,904 extents. A 2nd DCM page will appear on page No. 511238.

Differential backups identify which zones have been modified since the last full backup by reading the DCM page. This greatly reduces the number of differential backups scanned pages. Differential backups take longer to be proportional to the number of extents modified since the last full backup, regardless of the size of the entire database.

We create a new empty database, then fully back it up and view the DCM page information through the DBCC page.

 
 
1 CREATE DATABASE DCMdb
2 GO
3 BACKUP DATABASE DCMdb TO DISK=‘D:\DCMdb.bak‘
4 GO
5 DBCC TRACEON(3604)
6 DBCC PAGE(‘DCMdb‘,1,6,3)

As you can see, the area from page No. 0 to page 32nd has changed since the last full backup. This is not to say that every page of the 32 pages has changed. We can be sure that at least 4 pages have changed, one page at a zone. This may be due to the internal table change triggered by the full backup command.

We insert point data into the table and then use the DBCC PAGE command to see the information on the DCM page.

 
1 SELECT * INTO DCMdb..SalesOrderDetail FROM AdventureWorks2008R2.sales.SalesOrderDetail
2 DBCC PAGE(‘DCMdb‘,1,6,3)

through SELECT INTO we create a new table in the database and insert data into it, which triggers a lot of changes to the system tables. As a result we have seen many zones marked as changed since the last full backup. All the way up to page 1:167, where the changes are internal object modifications. The page changes from 1:184 to 1:1679 because we triggered the SELECT INTO statement. When we make a differential backup, SQL Server reads the DCM pages, which are flagged in the differential backup. For example, as we just mentioned, the page (1:0-1:24) is marked as a change page in the DCM page, and it is possible that only 4 pages have changed, but in a differential backup, all 32 pages are backed up.
We make a differential backup and look at the DCM page information via the DBCC page.

 
1 BACKUP DATABASE DCMdb TO DISK=‘D:\DCMdbifferential.bak‘ WITH DIFFERENTIAL
2 
3 DBCC PAGE(‘DCMdb‘,1,6,3)

You can see that the differential backup does not change the contents of the DCM page.

Let's calculate whether this backup size matches the size of the DCM change page.

((32-0) + (56-48) + (88-72) + (136-112) + (168-144) + (1680-184)) * 8=12800kb, basically close, where the gap is the file header information.

We will now make a full backup and then use the DBCC page to see the information on the DCM pages.

 
1 BACKUP DATABASE DCMdb TO DISK=‘D:\DCMdb2.bak‘
2 DBCC PAGE(‘DCMdb‘,1,6,3)

SQL Server now empties the DCM page. All pages are marked as unchanged except for the 4 partition 1:0-1:32. This backup will be used as a basis for subsequent differential backups. There is an option to set up a full backup without affecting the existing backup chain.

1 BACKUP DATABASE  to DISK = ' D:\DCMdb2_Copy.bak '  with Copy_only

In this scenario, SQL Server does not reset the DCM page. The Dcmdb2.bak file is still a full backup file and is not based on subsequent differential backups. This can be used when you want to make a full backup and don't want to affect the backup chain.
Summary: SQL Server tracks partitions through the DCM page to change confidence. When a differential backup is made, SQL Server backs up only the extents marked as changed in the DCM page. It helps SQL sever accelerate differential backup operations by not scanning all pages to see if there are any modifications (or since the last full backup has not changed). A differential backup does not empty the DCM page, it only backs up areas that have changed since the last full backup. When a full backup is made, SQL Server resets the bit state on the DCM page, marking them as unchanged.

SQL Server: Understanding DCM Pages