Few of the SQL Server Storage Definitions

Page - Basic Unit of SQL Server Storage - 8KB

Extent: 8 Consecutive pages - 64KB

Extent Types:

• Uniform Extent: If all the 8 Pages of the extent belong to the same IAM(Object)
• Mixed Extent: If the pages in the extent belong to more than 1 IAM(Object). So a maximum of 8 different IAMs can share an extent.

When is a page allocated from a 

• Mixed Extent: If the table size is very small then the pages are allocated from a Mixed Extent. First 8 pages of the table are stored in Mixed Extents.
• Uniform Extent: If the table size grows beyond 8 Pages then a Uniform extent is allocated to the new pages and the free pages in the Uniform extent are reserved for that particular IAM.

GAM Page: Global Allocation Map: This page keeps information of various Extents in the system and signifies whether it is in use extent(1) or an Unallocated extent(0)

A single GAM page can keep track of 64,000 Extents = Data of almost 8GB. So a single GAM page is used to keep track of 8GB worth of Data.

SGAM Page: Shared GAM: This keeps track of whether the Mixed Extent contains free pages or not.

• if SGAM bit = 1 then the particular extent is a mixed extent and has pages available to be used up
• if SGAM bit = 0 then the particular extent is either Uniform Extent or a mixed extent with no free pages

Page Structure:

• First 96 Bytes is the Header of the Page and gives a lot of information about the type of page and other metadata
• Then there are Data record
• Then there are Slots which gives the information of various record's offsets within that particular page.

More information about the storage could be found in the blogs written by Paul Randal: http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps/