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.
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:
|Unified & Allocated||0||0|
|Mixed with Free Pages||0||1|
|Mixed & Full||0||0|
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.
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.
bpage = 0x00000001F523E000 bhash = 0x0000000000000000 bpageno = (1:3)
bdbid = 21 breferences = 1 bcputicks = 0
bsampleCount = 0 bUse1 = 26902 bstat = 0x9
blog = 0xa6ab215a bnext = 0x0000000000000000
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 = (40361:723:8) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 1173753686
DB Frag ID = 1
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
Memory Dump @0x000000007329A000
000000007329A000: 01090000 00020000 00000000 00005a00 00000000 . ............Z.....
000000007329A014: 00000200 63000000 0600f61f 03000000 01000000 ....c.....ö.........
000000007329A028: a99d0000 d3020000 08000000 00000000 00000000 ©...Ó...............
000000007329A03C: 560ff645 00000000 00000000 00000000 00000000 V.öE................
000000007329A050: 00000000 00000000 00000000 00000000 00005e00 ..................^.
000000007329A064: 00000000 00000000 00000000 00000000 00000000 ....................
000000007329A078: 00000000 00000000 00000000 00000000 00000000 ....................
000000007329A08C: 00000000 00000000 00000000 00000000 00000000 ....................
000000007329A0A0: 00000000 00000000 00000000 00000000 00000000 ....................
000000007329A0B4: 00000000 00000000 00000000 381f0000 00002000 ............8..... .
000000007329A0C8: 00000000 00000000 00000000 00000000 00000000 ....................
000000007329BFE0: 00000000 00000000 00000000 00000000 00000000 ....................
000000007329BFF4: 00000000 00000000 be006000 ........¾.`.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
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.
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.