Principles of SQL Server Management Zone allocation (GAM, SGAM) and available space (PAM)

I have read it in detail before, but when I look back today, I find that I forgot about it. It is a tragedy. It seems that it is better to record it.

SQL Server zone management (GAM, SGAM)

We all know that the smallest unit of data file storage in SQL Server is page, but the actual sqlserve does not allocate space for data in the unit of page, the default storage allocation unit of SQL Server is the disk area (extend ). The main reason for this is to avoid frequent read/write Io and improve performance. Instead of allocating an 8 K page directly to a table or other objects, the storage allocation unit is a disk partition (extend, A disk has eight pages (size = 8*8 K = 64 K ).

In this way, the operations on the partition will be very frequent, and SQL server is required to have its own system to manage a large number of zones. One of the most prominent problems is that objects with only a small amount of data and less than 8 K are stored. If they are also allocated to a disk area, there will be a waste of storage space, space allocation efficiency is reduced.

To solve the above problems, sqlserver provides a solution that defines two types of disk areas, uniform disk areas and hybrid disk areas.

Global allocation ing table (GAM): Unified Disk Area. The Gam page records allocated areas. Each gam contains 64,000 zones, which is equivalent to nearly 4 GB of data. Gam uses a single bit to indicate the status of each zone in the covered range. If the bit is 1, the zone is available. If the bit is 0, the zone has been allocated.

Shared global allocation ing table (SGAM): The disk is owned by multiple objects. The SGAM page records the areas currently used as the hybrid zone and at least one unused page. Each SGAM contains 64,000 zones, which is equivalent to nearly 4 GB of data. SGAM uses a single digit to indicate the status of each zone in the covered range. If the bit is 1, the zone is used as a hybrid zone and there are available pages. If the bit is 0, the zone is not used as the hybrid zone, or all its pages are used even though it is used as the hybrid zone.

In order to improve space utilization when actually allocating storage disk areas for objects, by default, if an object is smaller than eight pages at the beginning, try to put it in the hybrid Disk Area, if the size of the object is increased to 8 pages, sqlserver will re-allocate a unified Disk Area for the object.

According to the current usage of the Zone, each zone in GAM and SGAM has the following bit mode:

This simplifies zone management.Algorithm. To allocate a unified partition, the database engine searches for the 1-bit value in GAM and sets it to 0. To search for a hybrid zone with available pages, the database engine searches for 1 bit in SGAM. To allocate a hybrid zone, the database engine searches for the 1 bit in GAM, sets it to 0, and then sets the corresponding bit in SGAM to 1. To release a zone, the database engine must set the GAM bit to 1 and the SGAM bit to 0. In fact, the algorithms used inside the database engine are more complex than described in this topic, because the database engine distributes data evenly in the database. However, because you do not need to allocate an information chain to a management zone, even the actual algorithm is simplified.

Manage SQL server space

First, extract an official explanation from the msdn section:

Page available space (PFS) page records the allocation status of each page, whether a single page has been allocated and the amount of available space per page. PFS has one byte for each page and records whether the page has been allocated. If it has been allocated, the page is recorded as blank, full 1% to 50%, full 51% to 80%, full 81% to 95% or full 96% to 100%.

After the objects are assigned, the database engine uses the PFS page to record which pages in the zone are allocated or available. This information is used when the database engine must allocate a new page. The available space on the reserved page is only used for heap and text/image pages. The database engine must find a page with free space to save the newly inserted rows. The index does not require the available space on the tracking page, because the points for inserting new rows are set by the index key value.

In a data file, the PFS page is the first page after the file header page (page 1 ). Next is the GAM page (the page number is 2), and then the SGAM page (the page number is 3 ). The first PFS page is followed by an PFS page of approximately 8,000 pages. There is another Gam page (including 2nd zones) after the first Gam page on page 1 ), there is also another SGAM page (including 3rd zones) after the first SGAM page on page 1 ). Displays the order of pages used by the database engine to allocate and manage zones.

After reading it, It was confusing. yunyun really admired how msdn was translated. It seems that Chinese msdn is too unreliable and there is no way at last, but Google is the only thing that can be done.

In fact, the above means: the way SQL server manages available space is to find whether each page is used and the usage. In this case, you need a page to record the usage of each page. This is the PFS page.

PFS (page free space), also known as page free space, is used to track the utilization of each specific page in a file. The second page (page 1) in a file is the PFS page, each byte on the page records the distribution, page type, Iam page, deletion record, and space utilization information of the corresponding page; PFS can manage and track the usage of 8088 pages, that is, close to 64 MB space.Each 8088 pages will appear again.

Let's first take a look at the structure of PFS Page Management byte. The management unit is byte, and each byte manages a page.

0th bits are reserved bytes, always 0

1st bits indicates whether the page has been allocated. We know that the GAM page is used to manage whether a zone has been allocated, but a zone contains eight pages, therefore, this bit is used to accurately locate whether a page in the area has been allocated.

2nd bits indicates whether the page is a page with mixed partitions.

The 3rd bits indicate whether the page is an Iam (index allocation ing) page.

4th bits indicate whether the page contains phantom or deleted records, which helps SQL Server regularly clear phantom or deleted records.

5th ~ Seven pages indicate the space usage of the page.