Database Reference
In-Depth Information
Figure 1-15.
Page status byte in PFS page
The first three bits in the byte indicate the percent of used space on the page. SQL Server tracks the used space for
row-overflow and LOB data, as well as for in-row data in the heap tables, which we will discuss in Chapter 2, “Tables
and Indexes - Internal Structure and Access Patterns.” These are the only cases when amount of free space on the page
matters.
When you delete a data row from the table, SQL Server does not remove it from the data page but rather marks
the row as deleted. Bit 3 indicates if the page has logically deleted (ghosted) rows. We will talk about the deletion
process later in this chapter.
Bit 4 indicates if the page is an IAM page. Bit 5 indicates whether or not the page is in the mixed extent. Finally, bit
6 indicates if the page is allocated.
Every PFS page tracks 8,088 pages or about 64MB of data space. It is always the second page (page 1) in the file
and every 8,088 pages thereafter.
There are two more types of allocation map pages. The seventh page (page 6) in the file is called a
Differential
Changed Map (DCM)
.
These pages keep track of extents that have been modified since the last FULL database
backup. SQL Server uses DCM pages when it performs DIFFERENTIAL backups.
The last allocation map is called
Bulk Changed Map (BCM)
. It is the eighth page (page 7) in the file, and it
indicates what extents have been modified in minimally logged operations since the last transaction log backup. BCM
pages are used only with a BULK_LOGGED database recovery model.
■
We will discuss different types of backups and recovery models in Part 6 of this topic, “Inside the
Transaction Log.”
Note
Both, DCM and BCM pages are the bitmasks that cover 511,230 pages in the data file.
Data Modifications
SQL Server does not read or modify data rows directly on the disk. Every time you access data, SQL Server reads it into
memory.
