Gam page and SGAM page in SQL Server

Introduction

We already know that the smallest unit of SQL Server Io is a page, and the eight consecutive pages are a zone. SQL Server requires a way to know the space usage in the database under its jurisdiction. This is the GAM page and SGAM page.

 

Global allocation map page

GAM (globally allocated Bitmap) is a page used to identify bitmaps used by SQL server space. It is located on the 3rd page of the database, that is, the page number is 2. Next we will create a new database to view its Gam structure. The code for creating a test database is shown in the code.

CREATE DATABASE [test] ON  PRIMARY ( NAME = N'test', FILENAME = N'C:\Test\test.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'test_log', FILENAME = N'C:\Test\test_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GO

Code 1. Create a Test Database

 

After the database is created, view the page with the database page number 2. We can see the result 1.

Figure 1. Gam page example

 

We can see that the data on the page is represented in hexadecimal notation. That is, a number is 4 bits, and two are one byte. The first four bytes of memory 381f are system information, and the last 10 bytes of slot1 are also system information. Each Other indicates the status of a zone of SQL Server, 0 indicates that it has been allocated, and 1 indicates that it has not been allocated. The following figure shows the GAM page used to calculate the space occupied by the database.

As we can see, since the database was just created, the allocated space can be expressed in the 4-8th byte, that is, 0001c0ff. The following code converts 0001c0ff from hexadecimal to binary. The result is

0000 0000 0000 0001 1100 0000 1111

Through calculation, we can see that there are 21 0 in the bit above, that is, 21 zones have been allocated to the database. We know that each zone is 8*8 K = 64 K. Therefore, the occupied space of the database (21*64)/1024 = 1.3125mb ≈ 1.31 MB

The following describes the actual space occupied by the database through SSMs, as shown in figure 2.

Figure 2. view the space occupied by the database through SSMs

 

The calculation result of 3-1.69 = 1.31mb is exactly the same as that of the GAM page.

If the database growth exceeds the range indicated by a gam, what should we do? The answer is simple: create another Gam page. The location of the second Gam page can also be calculated based on the information in Figure 1. In Figure 1, slot1 contains 7992 bytes. The first four bytes are used to store system information, and the last 7988 bytes are used to indicate the partition. Therefore, the range can be 7988*8 = 63904, the cross-page range is 511232, so the 511,232nd + 1 page should be the next Gam page, and the page number will be the 511232 page. This interval is also called Gam interval, which is close to 4 GB.

 

Shared global allocation map page

The Gam page shows that the smallest unit of space allocated is a zone. However, if a very small index or table only occupies 1 kb, it is too extravagant to allocate 64 KB of space to it. Therefore, when several tables or indexes are very small, several tables or indexes can be shared with one partition, which is a hybrid partition. The partition that can only be used by one table or index is a unified partition. SGAM is located on the fourth page of the database, that is, the next page of gam. The page number is 3. By combining bits at the same position as GAM, you can know the state of the space. Table 1 lists the statuses that can be expressed.

	Gam	SGAM bit
Unallocated	1	0
Unified zone or mixed zone with full space usage	0	0
Hybrid zone with allocable Space	0	1

Table 1. SGAM and gam

 

Through the combination of SGAM and GAM, SQL Server can know where to allocate space.

The second SGAM page is located after the second Gam page, that is, the page with a page number of 511233. And so on.