The Shared Global Allocation Map (SGAM)

Introduction

Last week we talked about the Global Allocation Map (GAM). The GAM tracks if an extent is still available to be used, or if it is allocated already. However, the GAM cannot tell us if there is still a page free within that extent. This information is particularly important when SQL Server needs to find an available page within a mixed extend. Therefore, an additional mechanism exists that identifies mixed extents that still have at least one free page available. This mechanism is called the Shared Global Allocation Map or SGAM.

The Shared Global Allocation Map (SGAM)

An SGAM page is organized the same way as a GAM page. For every GAM Interval, exactly one GAM and one SGAM page exists. Both are located within the GAM Extent, the first extent of each GAM Interval.

Each extent within a GAM Interval is associated with a single bit in the respective GAM as well as SGAM page. Like a GAM page, an SGAM page also starts out with an area big enough to hold eight page addresses. This area is used for neither GAM nor SGAM pages.

Following that area is the actual bit map. A set bit in a GAM page indicates that the extent associated with that bit is not yet allocated and therefore still available. The meaning of a set bit in an SGAM page is just a tad more complex. A set bit in an SGAM page indicates not only that the associated extent is a mixed extent, but also that there is still at least one page free within that extent. This means that an unset bit indicates one of three things: Either the extent is still available, or it is unified and allocated or it is mixed and completely full. The following table shows the four possible extent states and their respective GAM and SGAM bit settings:

As you can see, there is no way to distinguish between a full mixed extent and an allocated unified extent. However, that is not important to SQL Server, as both extents are not available to allocated new pages.

The combination in which the GAM and the SGAM bits for one extent are both set is invalid. If you encounter that combination, it indicates some kind of corruption.

An SGAM Example

SGAM pages are of page type 9 and the first SGAM page of a file is always page 3. To look at that page you can use DBCC PAGE like this:

More of the output of that statement is below. Only rows without any set bits have been removed.

As you can see, there is only a single bit set in the entire SGAM bit map. That bit is for the extent starting at page 296 as DBCC PAGE with option 3 reveals:

However, that extent is not the only mixed extent in the database. Page 283 for example is an IAM page. As IAM pages are always part of a mixed extent, the extent starting with page 280 must be mixed as well. A quick look at that very IAM page shows that this assumption is correct:

This IAM page shows that page 282 and 284 amongst others have been allocated from a mixed extend. But the bit for that extent in above SGAM output is not set. The reason is that all pages within that mixed extent have been allocated already. Take a look back at the table above that shows the different GAM and SGAM bit combinations. A mixed extent that does not have a free page anymore is expected to have the SGAM bit (as well as the GAM bit) not set.

Summary

Like GAM pages, SGAM pages are used to track free space and they occur once for every GAM Interval within a database file. However, SGAM pages track only mixed extents that still have at least one free page available.