SQL Server: Understanding the data file structure

This time we talked a lot of pages and now we can look at how these pages are organized in the data file.

As we all know, SQL Server divides data files into 8k pages, which are the smallest operating units of IO. SQL Server marks page 1th in the data file as 0. In all database files, the previous 9 pages (to page number 8) are displayed in the same order as shown below, and the 10th page (page number 9) is the startup page that holds the metadata information for the database.

1th page, page number 0, is the file header (page Type 15). It holds the file header information. Each data file has only one file header page and is the location of page No. 0. The file header page holds data file information such as file size, minimum size, maximum size, and file growth mode.

The 2nd page, page number 1, is the first PFS page (page type 11). The PFS page is the 2nd page (page number 1) in the data file, immediately following the file header (page number 0). GAM and Sgam are used to track the zone assignment status, which is used by the PFS page to track page assignment levels. When assigning pages, the database engine uses GAM and SGAM to identify areas with empty pages. Once the database engine finds a zone with empty pages, it uses the PFS page to identify the amount of free space available in the zone. Free space is tracked only when you save LOB values (ie text/image, varchar (max), nvarchar (max), varbinary (max), row overflow data), or heap table pages. By default, LOB data is saved on a separate page, and a pointer to a separate page is saved on the original page. These are the empty pages where the data can be saved. For index pages, there is no need to use the PFS page because the order in which the data is saved is consistent with the index order. PFS Page repeats one per 8,088 pages. That means page 1th, page No. 8088, page 16,176th, No. 24264 page ... In each data file is a PFS page. SQL Server: Understanding the PFS page.

The 3rd page, page number 2, is the first GAM page (page type 8). The GAM page is used to track which areas are used. Each area corresponds to a bit of the GAM page. If the value of this bit is 1, the corresponding area is empty and usable, if the value of this bit is 0, the corresponding area is used as a unified or mixed zone. A GAM page can hold information for close to 64,000 extents. That is to say, a GAM page can be saved (64000 * 8 * 8)/1024 = 4000 MB of information. In short, a 7GB size data file will have 2 gam pages. SQL Server: Understanding GAM and SGAM pages.

The 4th page, page number 3, is the first SGAM page (page type 9). The Sgam page is used to track which zones are being used as mixed areas and have at least one available page. Each zone corresponds to a single GAM page that has a bit. If the value of this bit is 1, the corresponding area is used as a mixed area and at least one available page, if this bit value is 0, the corresponding area is not used as a mixed area or all pages are used as a mixed area. A SGAM page can hold information for close to 64,000 extents. That is to say, a Sgam page can be saved 64000 * 8 * 8/1024 =4000MB. In short, a 7GB size data file will have 2 Sgam pages. SQL Server: Understanding GAM and SGAM pages.

5th, 6 pages, (page number 4,5), are not currently used in the SQL Server schema. The page type is 0. If you view these pages with the DBCC PAGE command, only the page header information is printed and ends with an illegal page type.

The 7th page, page number 6, is the first DCM page (page type 16). SQL Server uses the DCM pages to track the area information that has been modified since the last full backup. Each zone corresponds to a bit in the DCM page. If the value of this bit is 1, the corresponding zone has been modified since the last full backup. If this bit value is 0, the corresponding zone has not been modified since the last full backup. A DCM page can hold information for close to 64,000 extents. Each of the 511,232 pages, the DCM page repeats one. A DCM page keeps track of 63,904 zone information. A 2nd DCM page appears on page No. 511238. SQL Server: Understanding DCM pages.

The 8th page, page number 7, is the first BCM page (page type 17). SQL Server uses BCM pages to track zone information that has been modified through the bulk-logged operation since the last log backup. Each area corresponds to a bit in the BCM page. If the value of this bit is 1, the zone has been modified since the last log backup after the bulk-logged operation. If the value of this bit is 0, the zone has not been modified since the last log backup after the bulk-logged operation. A BCM page can hold information for nearly 64,000 districts. Each of the 511,232 pages, the BCM page repeats one. A BCM page can track 63,904 zone information. A 2nd BCM page appears on page No. 511239. SQL Server: Understanding BCM pages.

The 9th page, page number 8, is the first IAM page (page type 10). An IAM page is used to track the allocation of the corresponding page or section within the GAM for the specified table's allocation unit. SQL Server: Understanding IAM pages.

The 10th page, page number 9, is the startup page (page type 13). The start page appears only in the 9th page of the main data file (Prmary), and the start page does not appear in the 2nd data file. We can use the DBCC PAGE command to view its page information, and the value of the page information saved in this page is self-explanatory. If this page is damaged for some reason, we will not be able to use the command DBCC CHECKDB to fix it. Page restore does not change the situation. This problem can only be fixed by recovering from the last good database backup.

Starting from page 11th, you can see a mix of different pages, like data pages, index pages, IAM pages, row overflow pages and LOB pages, and so on. The page type of the data page is 1, the page type of the index page is 2, the row Overflow (row-overflow) page, and the page type of the LOB page is 3. Data pages and index pages are saved in the same structure. SQL Server: Understand the structure of the data page.

The row overflow (row-overflow) page is used to store data that cannot be saved on a single page. LOB pages are used to hold large objects and are not saved as part of the row data.

SQL Server: Understanding the data file structure