Pages & Extents and GAMs & SGAMs

Page: The fundamental unit of data storage in SQL Server is page. In SQL Server the page size if of 8 KB. So SQL Server splits 1 MB into 128 pages. Each page contains a header of 96 bytes, It stores the details like type of page, free space left on that page and object Id of the object whose data is residing on that page. Below is the page structure (Image taken from SQLServerCentral.com)

Types of Pages:

Extents: 8 contiguous physical pages make an Extent. When a new database is created the data files will be logically divided into Pages and Extents. And when the user objects were created the empty pages will be assigned to those to store data. GAM and SGAM pages store the page allocation details for each object. 

There are two types of Extents:

Uniform Extents: These extents are fully owned\allocated by single object, i.e. all 8 pages are only allocated to one object.

Mixed Extents: These extents are shared between different objects, i.e each page allocated to different object. Maximum of 8 objects can share a mixed extent. 

To better optimize the space allocation SQL Server will only allocates uniform Extent to the an object or index if and only if its size is more than 8 KB. 

Let's to a demo on this, create a demo table as GAMSGAM_Table and load some data as below to see the allocations.

USE Test
CREATE TABLE GAMSGAM_Table
(Name VARCHAR(8000) NOT NULL)
GO
I am inserting few records as part of first 3 batch of insertions and I made insertions into a different table between each batch to make sure all these initial insertions use the Mixed Extent
INSERT INTO DBO.GAMSGAM_Table (Name) 
VALUES('Tim')
GO 30

Insert some random records into some other table.
INSERT INTO DBO.GAMSGAM_Table (Name) 
VALUES('Tim')
GO 60

Insert some random records into some other table.
INSERT INTO DBO.GAMSGAM_Table (Name) 
VALUES('Tim')
GO 90

Insert some random records into some other table. 
INSERT INTO DBO.GAMSGAM_Table (Name) 
VALUES('Tim')
GO 9000

As part of last insertion, I inserted lot of records to see all they going into uniform extents. 

Now I am running the DBCC IND command to see the page allocations, I have used the color to show you the how page allocations changed by the amount records that I inserted above. Please visit my previous post here to know how to use the DBCC IND. From the below result it is clear that when I have inserted 9000 rows the SQL Server has used all the Uniform extents as shown in Green, they went from page 752 to 903, I was not able to capture whole in one screenshot. 

This is how we see higher level of fragmentation on the few tables (usually small tables) as they built from Mixed Extents that is the reason the fragmentation doesn't change though we run the rebuild indexing.

SQL server allocates the pages from the mixed extents when the new tables or indexes gets created, once they grow beyond the 8 KB size SQL Server will start allocating the pages from Uniform Extent. 

• Let's take a table of less than 8 KB and it need to grow to accommodate new updates, SQL Server will get it a  page from a Mixed extent. 
• Let's take a tables of size more 8 KB and it need to grow further in this case SQL Server get it a page from Uniform Extent.

Global Allocation Map (GAM) Pages: GAM Pages keeps track all the extents (uniform and Mixed), These pages will contain a bit for each and every extent. If the bit is 1 the corresponding extent is fully free and available, and if the bit is 0 the extent is in use as uniform or Mixed extent. One GAM page can hold detail of 6400 extents which is 6400X8 pages which is 6400X8X8 KB which is ((6400X8X8)/1024) MB = 4000 MB. 

Shared Global Allocation MAP (SGAM) Pages: SGAM pages also keeps track of all the extents, these pages also contains a bit for each extent as GAM Pages. If the bit 1 the corresponding extent is MIXED and has at least one free page. If the bit 0 the respective extent is UNIFORM or Mixed extent with no free page. Similar to GAM page, single SGAM page can hold detail of 6400 extents which is 6400X8 pages which is 6400X8X8 KB which is ((6400X8X8)/1024) MB = 4000 MB. 

Below table shows the bit setting for GAM and SGAM.

SQL Server takes helps of GAM and SGAM bit settings in managing the extents. Let's say if the SQL Server want to allocate an extent to table it will search the GAM page with bit 1 and sets the bit to 0 and if it is allocating it as Mixed extent then SQL Server will change the corresponding SGAM bit to 1, if it is allocating it as Uniform extent then no changes need on corresponding SGAM bit.  And if SQL Server looking for a Mixed extent with free pages then it will look for SGAM pages with bit as 1. If there is no free extent, the data file is full. To deallocate an extent, the database engine sets the corresponding GAM bit set to 1 and SGAM bit to 0.

In any data file, 

• GAM PAGE - the third page(page no 2).
• SGAM PAGE - the fourth page (page no 3). 
• File Header - the first page (page no 0). 
• PFS (Page Free Space) - the second page (page no 1).

We can explore the GAM and SGAM pages using DBCC PAGE commands. Here is my post on DBCC PAGE command. For the same DB that I used in demo, please look at DBCC PAGE result for both GAM and SGAM Pages.

For GAM Page: 

On file 1 pages from 0 to 3016 are from extents that are allocated and So on. 

For the same DB look at below DBCC PAGE result for SGAM Page.

Thing to know: The data files only contains the pages and extents, log files doesn't contain pages, they only contains chain of log of records. And in SGAM, What "S" stands for? Few say Secondary and few say Shared. But Paul Randal says "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." 

References: 
I am using the below posts as my references. I got little familiarity about these GAM\SGAMs and pages from below posts. Please go through below posts if you have any more questions.
http://www.practicalsqldba.com/2012/09/sql-server-understanding-gam-and-sgam.html
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps/