Differential Changed Map & Bulk Changed Map

SQL Server uses two internal data structures to track extents modified by bulk copy operations and extents modified since the last full backup. Like the Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM) pages, these structures are bitmaps in which each bit represents a single extent.

Differential Changed Map (DCM)
This tracks the extents that have changed since the last BACKUP DATABASE statement. If the bit for an extent is 1, the extent has been modified since the last BACKUP DATABASE statement. If the bit is 0, the extent has not been modified.

Differential backups read just the DCM pages to determine which extents have been modified. This greatly reduces the number of pages that a differential backup must scan.

Bulk Changed Map (BCM)
This tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement. If the bit for an extent is 1, the extent has been modified by a bulk logged operation after the last BACKUP LOG statement. If the bit is 0, the extent has not been modified by bulk logged operations.

Although BCM pages appear in all databases, they are only relevant when the database is using the bulk-logged recovery model. In this recovery model, when a BACKUP LOG is performed, the backup process scans the BCMs for extents that have been modified. It then includes those extents in the log backup. This lets the bulk logged operations be recovered if the database is restored from a database backup and a sequence of transaction log backups. BCM pages are not relevant in a database that is using the simple recovery model, because no bulk logged operations are logged. They are not relevant in a database that is using the full recovery model, because that recovery model treats bulk logged operations as fully logged operations.

The interval between DCM pages and BCM pages is the same as the interval between GAM and SGAM page, 64,000 extents. The DCM and BCM pages are located behind the GAM and SGAM pages in a physical file.

Its always good to explain things by example and so, we will use the Page database again to see how DCM and BCM behave. Let's first concentrate on DCM.

=> Check the status of the PFS page. As can be seen below PFS page shows that currenlty only 183 pages have been allocated. I have only taken the partial output

DBCC TRACEON(3604);
DBCC PAGE(Page, 1, 1, 3);
----Output----
(1:170) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:171) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:172) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:173) - (1:175) = NOT ALLOCATED 0_PCT_FULL
(1:176) - (1:181) = ALLOCATED 0_PCT_FULL
(1:182) - (1:383) = NOT ALLOCATED 0_PCT_FULL

=> Check the status of GAM page. As shown below this also confirms that the last allocated extent is 176th page to 183rd page. Again I am pasting only the partial output

DBCC TRACEON(3604)
DBCC PAGE(Page, 1, 1, 3)
----Output----
GAM: Header @0x000000000D96A064 Slot 0, Offset 96
status = 0x0
GAM: Extent Alloc Status @0x000000000D96A0C2
(1:0) - (1:176) = ALLOCATED
(1:184) - (1:376) = NOT ALLOCATED

=> Finally check the status of the DCM page. This also confirms that no extent after page 183 has changed since the last BACKUP DATABASE statement. Note that the DCM page is page 6.

DBCC TRACEON(3604)

DBCC PAGE(Page, 1, 1, 3)

----Output----
DIFF_MAP: Header @0x000000000C11A064 Slot 0, Offset 96

status = 0x0
DIFF_MAP: Extent Alloc Status @0x000000000C11A0C2
(1:0) - (1:176) = CHANGED
(1:184) - (1:376) = NOT CHANGED

=> Now I will create a table Test in this database and populate some data so that the extent allocation goes beyond page 183 using the script below

CREATE TABLE Test(col1 int, col2 nvarchar(900))
GO
DECLARE @i int= 0;
WHILE @i < 20
BEGIN
INSERT INTO Test VALUES (1,REPLICATE('a',900));
INSERT INTO Test VALUES (1,REPLICATE('b',900));
INSERT INTO Test VALUES (1,REPLICATE('c',900));
INSERT INTO Test VALUES (1,REPLICATE('d',900));
INSERT INTO Test VALUES (1,REPLICATE('e',900));
INSERT INTO Test VALUES (1,REPLICATE('f',900));
INSERT INTO Test VALUES (1,REPLICATE('g',900));
INSERT INTO Test VALUES (1,REPLICATE('h',900));

SET @i = @i + 1;
END

=> Now I will fire the DBCC PAGE command again for PFS, GAM and DCM. The output I get is as below.

------PFS--------

(1:170) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:171) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:172) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:173) - (1:175) = NOT ALLOCATED 0_PCT_FULL
(1:176) - (1:181) = ALLOCATED 0_PCT_FULL
(1:182) - (1:183) = NOT ALLOCATED 0_PCT_FULL
(1:184) - (1:191) = ALLOCATED 50_PCT_FULL
(1:192) - (1:233) = ALLOCATED 80_PCT_FULL
(1:234) - = ALLOCATED 50_PCT_FULL
(1:235) - (1:383) = NOT ALLOCATED 0_PCT_FULL
------GAM------

GAM: Extent Alloc Status @0x000000000C11A0C2
(1:0) - (1:232) = ALLOCATED
(1:240) - (1:376) = NOT ALLOCATED
-------DCM--------

DIFF_MAP: Extent Alloc Status @0x000000000F92C0C2
(1:0) - (1:232) = CHANGED
(1:240) - (1:376) = NOT CHANGED

Notice how DCM moved from page 184 to page 240. It says that since the last BACKUP DATABASE statement all extents till page 239 have been modified. I will now fire a BACKUP DATABASE statement and show you how DCM changes.

BACKUP DATABASE [Page] TO DISK=N'D:\Test.bak'

GO

Now let's fire DBCC PAGE on DCM again. Clearly shows that all pages after page 32 haven't changed since last BACKUP DATABASE statement. THe changes in the initial set of pages seems to be the metadat changes due to the BACKUP DATABASE statement.

DIFF_MAP: Extent Alloc Status @0x000000000EF2A0C2
(1:0) - (1:24) = CHANGED
(1:32) - (1:376) = NOT CHANGED

I will move on to BCM now. In order to see the working of BCM, I will create the page database again using the script in the Data File Secition above. The database has been created in the FULL recovery mode. When I fire the DBCC PAGE command for BCM, I get the following output. Note that BCM is page 7 and the output printed below is only partial.

DBCC TRACEON (3604);
DBCC PAGE (Page, 1, 7, 3);

ML_MAP: Extent Alloc Status @0x000000000C11A0C2
(1:0) - (1:376) = NOT MIN_LOGGED

I will now create the Test_1 table using the Test table created above and see if anything changed in the BCM page.

SELECT * INTO Test_1 FROM Test(nolock);

After creation of the table DBCC PAGE on BCM gives the following output. Basically no change.

ML_MAP: Extent Alloc Status @0x000000000EF2A0C2
(1:0) - (1:376) = NOT MIN_LOGGED

I will now change the recovery mode of the database to simple recorvery mode and drop the existing table Test.

ALTER DATABASE Page SET RECOVERY SIMPLE;

GO

DROP TABLE Test_1;

Let me check BCM page now.

ML_MAP: Extent Alloc Status @0x000000000D96A0C2
(1:0) - (1:376) = NOT MIN_LOGGED

I will create and populate the Test_1 table again using the script above. This time the database is in SIMPLE recovery mode. The output that I get for BCM after creation and population of the table is as below. Again it hasn't changed.

ML_MAP: Extent Alloc Status @0x000000000F49A0C2
(1:0) - (1:376) = NOT MIN_LOGGED

Finally I will change the database to BULK LOGGED mode and see the BCM page state before and after the Test_1 table creation.

ALTER DATABASE Page SET RECOVERY BULK_LOGGED;

GO

DROP TABLE Test_1;

Let me check the BCM page now.

ML_MAP: Extent Alloc Status @0x000000001385A0C2
(1:0) - (1:376) = NOT MIN_LOGGED

Again creating and populating the Test table changes the BCM page as follows

(1:0) - (1:80) = NOT MIN_LOGGED

(1:88) - = MIN_LOGGED

(1:96) - = NOT MIN_LOGGED

(1:104) - (1:120) = MIN_LOGGED

(1:128) - (1:256) = NOT MIN_LOGGED

(1:264) - (1:296) = MIN_LOGGED

(1:304) - (1:376) = NOT MIN_LOGGED