As you know, SQL Server's data files are made up of 8KB pages, that are organized into 64KB Extents. There are several different page types, for Data, Indexes, LOB data and IAMs (Index Allocation Maps), which are the root pages for partition/allocation unit combinations, but there are also several other pages, that SQL Server maintains internally, in order to operate quickly and correctly.
The first of these page types, is the Database Boot Page. This is always Page 9, in file 1 of your database, and includes information such as the current and original database versions, the LSN (Log Sequence Number) of the last Checkpoint, and the Database Mirroring ID.
The second page type is the File Header Page, and this is always page 0 in every file. This also contains a raft of useful information, with some of the highlights being the auto growth size, the minimum file size, the HDD sector size and the LSNs/GUIDs required for restore operations.
Page 1 of every file is a PFS (Page Free Space) page, that is a byte map, detailing how full each page is. Obviously, there is a limited number of pages that can have there space recorded within an 8060-byte byte map, so this page is repeated on every 8088th page. The information stored for each page recorded in the byte-map includes:
The amount of free space on a page
Are there Ghost records (deleted rows) on the page?
Is the page an Index Allocation Map?
Is the page part of a mixed extent?
Has the page been allocated?
On pages 2 & 3 we begin the GAM Interval. This consists of a GAM (Global Allocation Map) and a SGAM (Shared Global Allocation Map) page. These two pages are bit-maps, that record the status of the following 64000 extents, which equates to around 4GB of data.
The GAM page is a record of which extents have been allocated. 0 = Allocated, 1 = Unallocated.
Then, the SGAM page is a record of if there are free pages within a mixed extent or not. 0 = The extent is either full of mixed pages, or is a uniform extent, 1 = There is one or more pages free in the mixed extent.
It is then possible for SQL to combine the bits from each of these bit-maps to determine if an extent is uniform, mixed or unallocated, so that it knows which mixed extents can be used for new or small objects and which extents are free to be assigned uniformly to larger objects.
Just taking a step back, SQL Server (by default) uses both mixed extents (where different pages are assigned to different objects) and uniform extents (where all pages within the extent are assigned to the same object). The rule it uses, is that when an object is first created, it will use a mixed extent, but once the object has grown to a point where it would fill an entire extent, it switches to uniform extents. It never switches back however, even if you truncate a table.
It is possible to change this behaviour, by turning on Trace Flag 1118. This is generally used to alleviate contention for system pages in TempDB, and will force SQL to only use uniform extents, even when a small object is first created.
Anyway, back to the point - The last two system pages of note, are the MLM and DCM pages. These pages can be found at pages 6 and 7 within a file, and contain information that helps with Disaster Recovery. Like the other bitmap pages, they are repeated every 511,230th page.
The MLM (Minimally Logged Map) page is a bitmap, that specifies whether a page has been modified by any minimally logged transactions, since the last transaction log backup. This helps SQL quickly determine what changes have occurred since the last transaction log backup.
Finally, the DCM (Differential Change Map) page is a bitmap that specifies if a page has changed since the last full backup. This means that when you take a Differential backup, SQL can easily determine which pages is needs to backup.
No comments:
Post a Comment