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/

DBCC IND and DBCC PAGE

DBCC IND: It is one of the undocumented commands, It is not supported by Microsoft. This command helps in identifying the page numbers that table or index is using. The syntax looks like below. 

Syntax: DBCC IND ('DBName' or DBID, 'TableName' or ObjectId, NOn Clustred Index ID)

The third parameter can be the Non Clustered Index Id from Sys.indexes table or 1 or 0 or -1 or -2.  -1 provides complete information about all type of pages ( in row data, row over flow data, IAM, all indexes ) associated with the table.

The result set of DBCC IND looks like below: 

My table using the page numbers 290, 290, 292 and 293. 

Explanation of the what does each column mean: 

PageFID -- the file ID of the page

PagePID -- the page number in the file

IAMFID -- the file ID of the IAM page that maps this page (this will be NULL for IAM pages themselves as they’re not self-referential)

IAMPID -- the page number in the file of the IAM page that maps this page

ObjectID -- the ID of the object this page is part of

IndexID -- the ID of the index this page is part of

PartitionNumber -- the partition number (as defined by the partitioning scheme for the index) of the partition this page is part of

PartitionID -- the internal ID of the partition this page is part of

iam_chain_type -- see IAM chains and allocation units in SQL Server 2005

PageType -- the page type. Some common ones are:

                  1 – data page  2 – index page 3 and 4 – text pages 8 – GAM page 9 – SGAM page 
                  10 – IAM    page 11 – PFS page

IndexLevel -- what level the page is at in the index (if at all). Remember that index levels go from 0 at the   leaf to N at the root page (except in clustered indexes in SQL Server 2000 and 7.0 – where there’s a 0 at the leaf level (data pages) and a 0 at the next level up (first level of index pages))

NextPageFID and NextPagePID -- the page ID of the next page in the doubly-linked list of pages at this level of the index

PrevPageFID and PrevPagePID -- the page ID of the previous page in the doubly-linked list of pages at this   level of the index

DBCC PAGE: It is another undocumented command. As the name suggests this command helps view the contents of the data and index pages. The result of this command is little hard to understand. The syntax looks like below. 

Syntax: DBCC PAGE('DBName' or DBID, FileNumber, PageNumber, PrintOption)

                 PrintOption can be 0 or 1 or 2 or 3 - each option provides different information.

To see the result of this command, lets create a test database, table and insert few records as below. 

USE MASTER

CREATE DATABASE Test

GO

USE Test

CREATE TABLE Pagetable

(

SNo INT IDENTITY(1,1),

Name VARCHAR(50) NOT NULL,

Gender varchar(5),

Fathername varchar(50),

DOB DATETIME NOT NULL,

CONSTRAINT pk_pagetable PRIMARY KEY (Sno),

CONSTRAINT uq_name_father UNIQUE (Name, Fathername)

)

GO

INSERT INTO DBO.Pagetable (Name,Gender,Fathername,DOB) 

VALUES('Tim','M','Tom','1999-03-15') 

INSERT INTO DBO.Pagetable (Name,Gender,Fathername,DOB) 

VALUES('Jim','M','Cam','1999-03-15') 

INSERT INTO DBO.Pagetable (Name,Gender,Fathername,DOB) 

VALUES('Tina','F','Mik','1999-03-15') 

Now lets use the DBCC PAGE command to see the connects of this Test database. But we dont know which page does this database is using. So first run the below DBCC IND command, it results as below screenshot. 

DBCC IND ('test',pagetable,-1)

Result:

Let’s say I am interested in viewing the DBCC PAGE contents of the PageID 286, run the below command.

And we need to turn on the trace 3604 to view the results.

DBCC TRACEON (3604)

DBCC PAGE ('test',1,286,3) with TABLERESULTS

Result:

Please go through the below posts for more details on DBCC IND and PAGE.

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-using-dbcc-page-and-dbcc-ind-to-find-out-if-page-splits-ever-roll-back/