SQL Server 2008 storage structure GAM, Sgam introduction _mssql2008

When we create a database, for example, the default way to create a DB Testdb,sqlserver automatically helps us build the following two database files.

These two data files are real operating system files, one of which is called row data files, used to store various objects of the database, the other is log files, never record the process of data change.

Logically, the minimum storage unit for a database is the page that is 8kb.

The database is divided into several logical pages (8KB per page), and in each file, all pages are numbered sequentially from 0 to X, where x is determined by the size of the file. We can refer to any data page by specifying a database ID, a file ID, and a page number. Each data page is used to store tables and indexes, as well as related database management information.

We can find the file along the path of the above data file, and look at the size of the newly created data file:

2.18 MB (2,293,760 bytes) =2,293,760b/8kb=280 page = 35 extents

The smallest unit of space management for a database is a district (extents).

A zone consists of 8 logically contiguous pages (64KB of space). In order to allocate space more efficiently, SQL Server 2008 does not allocate space for a small amount of data to a data table. SQL Server 2008 has two types of extents.

The area of the unified type is owned by a single object, and all 8 data pages in the zone can only be used by the owning object.

A zone of mixed types can be shared by up to 8 objects.

SQL Server assigns pages from mixed-type extents for new tables or indexes. When the table or index grows to 8 pages, all subsequent allocations use the consolidated type of extents.

When a table or an index requires more space, SQL Server needs to find the space that can be used to allocate it. If the table or index is still less than 8 pages in its entirety, SQL Server must find space that is composed of mixed-type areas that can be allocated. If the table or index has 8 pages or greater, SQL Server must find a free, unified type of zone.

SQL Server uses two special types of pages to record which areas have been allocated, and which types (mixed or unified) are available for use:

Global allocation Map,gam pages these pages record which areas have been allocated and used for what purpose. A GAM page has a data bit for each area in the space it covers. If the data bit is 0, then the corresponding zone is in use, and if the data bit is 1, then the zone is free. A GAM page can cover 64 000 districts, or approximately 4GB of data, in addition to the page header and some other expenses that need to be recorded, perhaps 8 000 bytes or 64 000-bit space is available. This means that each 4GB space of a file corresponds to a GAM page.

Shared Global Allocation Map,sgam pages these pages record which extents are currently being used as a mixed type, and those areas need to contain at least one unused page. Like a GAM page, each SGAM page covers about 64 000 districts, or about 4GB of data. A Sgam page has a data bit for each area in the space it covers. If the data bit is 1, then the corresponding used zone is a mixed type, and there are some free pages in the area, and if the data bit is 0, then the corresponding section is not a mixed-type zone, or a mixed-type zone, but all the pages are already in use.

Table 4-2 shows the bit patterns for the area in GAM and Sgam, based on the current usage of each sector.

Current usage of the zone	GAM bit-bit settings	Sgam bit-bit settings
Free, not used	1	0
A uniform type or a mixed area that has been used entirely	0	0
Mixed area with free pages	0	1

If SQL Server needs to find a new, completely unused zone, it can use any one that corresponds to a bit value of 1 in the GAM page. If SQL Server needs to find a zone of mixed types with free space (one or more free pages), it can look for a region with a value of 0 in the GAM and a value of 1 in Sgam. If a zone with a mixed type of free space is not present, SQL Server uses the GAM page to look for an entirely new area and assign it as a mixed-type zone, and then use one of the pages in the zone. If there is no free zone at all, then the file is full.

No. 0 Page	1th page	2nd page	3rd page	4th page	5th page	6th page	7th page
M_type=15	m_type=11	M_type=8	M_type=9	M_type=0	M_type=0	M_type=16	M_type=17
Header File page	PFS pages	GAM page	Sgam page	Retention page	Retention page	DCM Page	BCM Page

SQL Server can quickly lock the GAM page in a file because it is always on the third page of any database file (page 2). The Sgam page is on page fourth (Page 3). The next GAM page appears on every 511 230 pages after the first GAM page (page 2), and the next Sgam page appears on every 511 230 pages after the first SGAM page (page number 3). The page with 0 pages per database file is the header page of the file, and each file has only one page. Page 0 is the header file page, and Page 1 is the Free Space page (page Space,pfs).

The first eight page order in each of the SQLSERVER2008 databases is fixed.

In addition to page 9th, the boot page for the database, from page 8th through 173th, stores information for the SQLSERVER2008 internal system tables, and then from 174th page to No. 279 page for unassigned pages. Because the first page starts at 0, it's just 280 pages, which is exactly the same size as the database data file we see.

8th page	8th page	8th page	Nth page	173th page	No. 279 Page
M_type=1	M_type=13	M_type in (1,2,10)			N/A
Data page	Boot page	Mainly for internal system table related information			Not assigned

The screenshot below is the overall page structure seen through the SQLServer2008 Internals Viewer plugin, which is downloaded from the http://www.SQLInernalsViewer.com Web site and is divided into different. NET versions.

Note: TestDB is a newly created empty database with no user-defined objects until a table script is available;


The database page types are as follows:

Type	Page type name	Page type description
1	Data page	Leaf node data for heap tables and clustered indexes
2	Index page	All index records for non-leaf nodes of the clustered index and for nonclustered indexes
3	Text Mixed page	A Text page that holds small chunks of LOB values plus internal parts of the text tree. These can is shared between LOB values in the same partition of the A index or heap.
4	Text Tree Page	A text page that holds large chunks of LOB values from a single column value.
7	Sort page	The temporary pages used to sort the intermediate operations to store the data.
8	GAM page	Global allocation Map,gam pages these pages record which areas have been allocated and used for what purpose.
9	SGAM page	Shared Global Allocation Map,gam pages these pages record which extents are currently being used as a mixed type, and those areas need to contain at least one unused page.
10	IAM page.	Information about the area used by a table or index in each allocation unit
11	PFS page.	Information about page allocations and free space for pages
13	The boot page.	Records information about the database, only on page 9th of each database
15	File Header page	Information about database files is recorded, on page No. 0 of each database file
16	DCM page	Record a page that has changed since the last time it was fully prepared for a differential backup
17	BCM page	Information about extents modified by bulk operations in each allocation unit since the last BACKUP LOG statement

In fact, SQL Server also includes some open page types, such as type 19,type 14, and so on.

In this chapter we introduce the GAM and SGAM pages, and other page types will be introduced later.

So how do you view the page information, starting from the SQLServer2000 to provide a command to read the data page structure of the DBCC page. This command is not a documented command, as follows:

DBCC Page ({dbid|dbname},filenum,pagenum[,printopt])

The specific parameters are described as follows:

DBID the database ID that contains the page

DBName the name of the database that contains the page

FileNum contains the page's file number

Pages in the Pagenum file

Printopt Optional Output option, select one of the values:

0: Default value, output buffer title and page title

1: The title of the output buffer, the page header (each row is output separately), and the row offset scale

2: The output Buffer title, page title (overall output page), and row offset scale

3: The title of the output buffer, the title of the page (each row is output separately), and the row offset table; each row followed by its column value listed separately

If you want to see the results of these outputs, you also need to set up DBCC TRACEON (3604).

As mentioned earlier, the GAM page must exist on the second page of the database, and the SGAM page must exist on the third page of the database, and each database will have a database file with a file number of 1, so we execute the following command.

Copy Code code as follows:

DBCC Traceon (3604)
DBCC page (testdb,1,2,1)-View GAM pages information
DBCC page (testdb,1,3,1)-View Sgam page information
DBCC page (testdb,1,2,2)-View GAM pages information and overall output page
DBCC page (testdb,1,3,2)-View Sgam page information and overall output page
DBCC page (testdb,1,2,3)-View GAM pages information and corresponding column values
DBCC page (testdb,1,3,3)-View Sgam page information and corresponding column values
DBCC page (testdb,1,2,1) with tableresults-view Sgam page information and corresponding column values in tabular form
DBCC page (testdb,1,3,1) with tableresults-view Sgam page information and corresponding column values in tabular form

We can see that a complete page is divided into four parts; BUFFER, PAGE HEADER, data, and offset TABLE.

Let's start with the GAM page:

Buffer section:

Displays the buffered information for a given page, which is an in-memory structure for managing pages that make sense only if the page is in memory. We know very little about this part and basically can't find the relevant material.

BUF @0x03585cd8	Every time the cache is emptied again, the address will change.
bpage = 0x060b4000	Every time the cache is emptied again, the address will change.
Bhash = 0x00000000	relatively unchanged
Bpageno = (1:2)	Current page address
Bdbid = 8	sys.databases.database_id
Breferences = 1	Every time the cache is emptied again, the address will change.
BUse1 = 41490	Every time the cache is emptied again, the address will change.
Bstat = 0xc00009	relatively unchanged
Blog = 0x59ca2159	relatively unchanged
bnext = 0x00000000	relatively unchanged

　　 PAGE header section:
The page header section displays data for all header fields on the page

This part of the page header can only be displayed through the DBCC page (testdb,1,2,2), the overall output page, and by comparison with the table above, we can barely identify some of the relevant storage information, and when this part lacks the support of official documents, in order to avoid unnecessary speculation, So for the time being do not do in-depth discussion.

DATA section

The data section is generally divided into several slot numbers (Slot), which, if it is a page or index page, can be understood as a row, and SQL Server uses the file number + page number + slot number to uniquely identify each record in the table. But in the GAM page we can interpret slot 0 as a reserved page for the GAM page, totaling 94 bytes.

Starting with the 194th byte (the page always starts with the No. 0 byte), to the 196th byte, these three bytes represent the condition of the allocated partition. namely 0000C0.

Let's take a look at the execution results of the DBCC PAGE (testdb,1,2,3).

The above shows that from page 1th to page 168th has been allocated, while pages 176th through 272 are unassigned, and the 194 pages displayed by the DBCC page (testdb,1,2,2) appear to be contradictory, in fact, contradictory. As mentioned earlier, GAM identifies the unused partition as 0 and the allocated partition as 1
1 partitions = 64 pages, because the first 128 pages are allocated, so the first two bytes are 00 00
Pages from page 128th to page 175th are also allocated, in fact, 6 extents are 0, that is 6 consecutive bit 0, one byte is 8 bit, and the last two bit is 11, so the byte is 0000 0011, and this needs to be reversed for the related bits; 0000 is the C0.
Finally, let's take a look at the GAM page with the Internals Viewer plugin.



Sgam page

PAGE: (1:3)

BUFFER:
BUF @0x0358a7f4
bpage = 0x062ae000 Bhash = 0x00000000 Bpageno = (1:3)
Bdbid = 8 Breferences = 3 BUse1 = 14428
Bstat = 0xc00009 Blog = 0x21212159 bnext = 0x00000000

PAGE HEADER:
Page @0x062ae000
M_pageid = (1:3) m_headerversion = 1 M_type = 9
M_typeflagbits = 0x0 M_level = 0 M_flagbits = 0x200
M_objid (allocunitid.idobj) =99 M_indexid (allocunitid.idind) =0 metadata:allocunitid=6488064
Metadata:partitionid = 0 Metadata:indexid = 0 Metadata:objectid = 99
M_prevpage = (0:0) M_nextpage = (0:0) Pminlen = 90
m_slotcnt = 2 M_freecnt = 6 M_freedata = 8182
m_reservedcnt = 0 M_lsn = (18:435:5) m_xactreserved = 0
M_xdesid = (0:0) m_ghostreccnt = 0 M_tornbits = 177043542
Allocation Status
GAM (1:2) =allocated SGAM (1:3) =not Allocated PFS (1:1) =0x44 allocated 100_pct_full
DIFF (1:6) = CHANGED ML (1:7) = Not min_logged

DATA:
Slot 0, Offset 0x60, Length, Dumpstyle BYTE
Record Type = Primary_record record Attributes =
Memory Dump @0x4f32c060
00000000:00005e00 00000000 00000000 00000000?.. ^.............
00000010:00000000 00000000 00000000 00000000?................
00000020:00000000 00000000 00000000 00000000?................
00000030:00000000 00000000 00000000 00000000?................
00000040:00000000 00000000 00000000 00000000?................
00000050:00000000 00000000 00000000 0000?????? ..............

Slot 1, Offset 0xbe, Length 7992, Dumpstyle BYTE
Record Type = Primary_record record Attributes =
Memory Dump @0x4f32c0be
00000000:0000381f 20ee2000 00000000 00000000?.. 8.. .........
00000010:00000000 00000000 00000000 00000000?................
00001f30:00000000 00000000??????????????????? ........

The following is information about the DBCC PAGE (testdb,1,3,3), which is interesting to compare with 20EE20.

(1:0)-(1:32) = Not allocated
(1:40)-= Allocated
(1:48)-(1:64) = Not allocated
(1:72)-(1:88) = Allocated
(1:96)-= Not Allocated
(1:104)-(1:120) = Allocated
(1:128)-(1:160) = Not allocated
(1:168)-= Allocated
(1:176)-(1:272) = Not allocated

Finally, let's take a look at the full picture of the Sgam page with the Internals Viewer plugin.



To summarize, the more difficult part about GAM and SGAM pages:

1, on the GAM and Sgam page of the buffer information can not understand the basic, and can not find the relevant materials.

2, the PAGE header part of the information and slot 0 part of the information, also can not find related materials.

3, the not allocated in the Sgam page is actually a uniform type area or a mixed-type zone that has been used, and allocated is actually a mixed area containing free pages.

4, the GAM page 0 represents has been allocated, 1 represents the free zone; it's just the opposite of the general sign.

5, GAM and Sgam actually only allocated 280 pages, that is, 35 areas; The displayed data content is much, but the subsequent partition information does not actually exist.

6. The page allocation information displayed by the GAM and SGAM through DBCC's printopt to 3 appears to be a broken number.

7. The byte of the area information of the GAM and SGAM is obtained through the two-level system inversion.

　　The total size of the GAM and Sgam pages is 8,192 bytes; The file header is 96 bytes, slot 0 is 94 bytes, the system information for the head of slot 1 is 4 bytes, and the tail system information is 10 bytes, so the effective storage should be 7,988 bytes, 63,904 districts, 511,230 pages In fact, when the data file is over 4G, we will be able to find its corresponding GAM, Sgam page on page No. 511232 and No. 511233 respectively.