Under the covers: GAM, SGAM, and PFS pages

(Been a week or so since the last post but I haven't burnt out with blogging yet - I was on vacation

over the July 4th weekend and totally offline in and around a small town called Pullman in

south-eastern Washington.)

In a previous post I described extents, and in another previous post a while back I described how

the extents and pages that are allocated to an IAM chain are tracked in IAM pages. What I didn't

describe is how the allocation status of individual pages is tracked, or how the global allocation

bitmaps work - that's the subject of this post.

This is the last post that lays the groundwork to

be able to discuss allocation checks in CHECKDB - the subject of the following post - and various

corruption scenarios (yes Kimberly, I'm going to get to scenarios...)

Bear in mind that everything below is exactly the same in SQL Server 2000 and 2005.

GAM pages

GAM stands for Global Allocation Map. If you remember from before, database

data files are split up into GAM intervals (don't get confused - they're not split physically, just conceptually). A GAM interval is equivalent to the amount of

space that the bitmaps in GAM, SGAM, and IAM pages track - 64000 extents or almost 4GB. These 

bitmaps are the same size in each of these three page types and have one bit per extent, but they

mean different things in each of the different allocation pages.

One thing to note, at the start of every GAM interval is a GAM extent which contains the

global allocation pages that track that GAM interval. This GAM extent cannot be used for any

regular page allocations.

The bits in the GAM bitmap have the following semantics:

• bit = 1: the extent is available for allocation (you could think of it as currently

  allocated to the GAM page)

• bit = 0: the extent is already allocated for use

These semantics are the same for mixed and dedicated/uniform extents.

SGAM pages

I remember last year having an email discussion about what the 'S' stands for in SGAM. Various names have been used over the

years inside and outside Microsoft but the official name that Books Online uses is Shared

Global Allocation Map. To be honest, we always just call them 'es-gams' and

never spell it out.

As I said above, the SGAM bitmap is exactly the same as the GAM bitmap in structure and the interval it covers, but the semantics of the

bits are different:

• bit = 1: the extent is a mixed extent and has at least one unallocated page available for

  use

• bit = 0: the extent is either dedicated or is a mixed extent with no unallocated pages

  (essentially the same situation given that the SGAM is used to find mixed extents with unallocated

  pages)

GAM, SGAM and IAM pages
So, taking the GAM, SGAM and IAM pages together (remember that in the IAM bitmap, the bit is set if the extent is allocated to the IAM chain/allocation unit), the various combinations of bits are:

You can see that only 4 of the 8 possible bit combinations for any particular extent are valid. Anything else constitutes a corruption of some sort and can lead to all kinds of horrible situations - more on some of these in later posts.

(But I can't resist - 'how do these corruptions happen?' I'm sure someone is asking. Every database page is 8KB, which is really 16 512-byte disk segments. Imagine a flaky IO system writing some random data into one of the disk segments of a GAM page and causing multiple IAM pages to think they have the same extents allocated...)

PFS pages

PFS stands for Page Free Space, but the PFS page tracks much more than that. As well as GAM intervals, every database file is also split (conceptually) into PFS intervals. A PFS interval is 8088 pages, or about 64MB. A PFS page doesn't have a bitmap - it has a byte-map, with one byte for each page in the PFS interval (not including itself).

The bits in each byte are encoded to mean the following:

• bits 0-2: how much free space is on the page
  • 0x00 is empty
  • 0x01 is 1 to 50% full
  • 0x02 is 51 to 80% full
  • 0x03 is 81 to 95% full
  • 0x04 is 96 to 100% full
• bit 3 (0x08): is there one or more ghost records on the page?
• bit 4 (0x10): is the page an IAM page?
• bit 5 (0x20): is the page a mixed-page?
• bit 6 (0x40): is the page allocated?

For instance, an IAM page will have a PFS byte value of 0x70 (allocated + IAM page + mixed page). You can examine PFS pages using DBCC PAGE (the instructions in that post use a PFS page as an example).

Free space is only tracked for pages storing LOB values (i.e. text/image in SQL Server 2000, plus varchar(max)/varbinary(max)/XML and row-overflow data in SQL Server 2005) and heap data pages. This is because these are the only pages that store unordered data and so insertions can occur anywhere there's space. For indexes, there's an explicit ordering so there's no choice in the insertion point.

The point at which a PFS byte is reset is not intuitive. Just yesterday I was helping a couple of MVPs with an issue and one of the questions was (paraphrasing) "This page has a PFS byte value of 0x04 - how can it be full when its not allocated?"

The answer is that PFS bytes are not fully reset until the page is reallocated. On deallocation, the only bit in the PFS byte that's changed is the allocation status bit - this makes it very easy to rollback a deallocation.

Here's an example. Using a database with a simple table with one row. A DBCC PAGE of the IAM page includes:

PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL

If I run the following:

BEGIN

TRANSACTION

DROP

TABLE T1

GO

And then do the DBCC PAGE again, the output now includes:

PFS (1:1) = 0x30 IAM_PG MIXED_EXT 0_PCT_FULL

And if I rollback then transaction, the DBCC PAGE output reverts to:

PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL

Now that these three pages have been discussed, we're free to explore allocation checks and corruptions and I'm free to go and have breakfast!

(This is just adding the blog to Technorati - Technorati Profile)

Comments

• Anonymous
  July 16, 2006
  Thanks again. Keep up the good posts.

• Anonymous
  March 21, 2007
  Very good post, it answers questions which I could not find anywhere else! However, in the PFS pages section, I think  bit 4 and bit 5 are switched. should be like this: bit 5 (0x20): is the page a mixed-page? bit 4 (0x10): is the page an IAM page? I did some practises on NON-IAM mixed-extent pages, the PFS returns 0X60 (allocated + Mixed) for example: DBCC page(AdventureWorks,1,16176,3)  -- check the PFS to find a mixed extent --return (1:20760)    - (1:20767)    =     ALLOCATED   0_PCT_FULL                     Mixed Ext DBCC page(AdventureWorks,1,20760,1) --GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:16176) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL And I have a question for the IAM pages, I checked some of them, they alays return 0x70 (allocated + Mixed + IAM), Does that mean IAM pages are always allocated in a mix extent ? Also in the GAM, SGAM and IAM pages section, in the chart the 3rd column is "Any IAM", What does "Any IAM" = 0 or "Any IAM" = 1 mean ? Thanks. David

• Anonymous
  March 21, 2007
  Hey David - good catch - that's what happens when you try to do stuff from memory! Yes, IAM pages are always from mixed extents. Any IAM means that one IAM page mut have a bit set corresponding to that extent. Cheers

• Anonymous
  June 28, 2007
  You’ve probably heard the term banded around but do you know what it means and what it means to the performance

• Anonymous
  January 11, 2017
  I know I am putting up this question way too late going by the blog creation date. But none the less, I want to know, are these pages stored in Primary MDF file only or are they stored in File Header in each of the datafile?