SQL Server – Understanding SGAM, GAM, IAM, and PFS

database-internalssql server

Have several questions about internal structure of SQL Server.

Lets assume that we have a table in a database.

  1. SGAM & GAM pages keep track of shared & uniform extents within a single GAM interval(~4GB), and help us find appropriate type of extent when allocating pages/extents(initially first 8 pages are allocated from mixed extents & positions of those pages are recorder to IAM pages, and after that from GAM extents(uniform extents) and this information is stored in next chained IAM page).

    Let's say, pages for the table were allocated, and table size is more than 8 pages, and sometime later, rows were deleted, leaving some re-usable space. How SQL Server knows that extents have free space ?

    Pages have information about the size of free space, but it is too time consuming to check every page. Bits in SGAM & GAM pages don't tell us anything beside that extend is allocated or has some free pages(entire page is free, not partially).

  2. File header & SGAM & GAM & IAM pages are among the first pages within a file. What data-structure actually points to them ?

Best Answer

How SQL Server knows that extents have free space ?

SQL Server tracks free space on the page, in heap tables as well as in LOB pages using PSF page internally.

Also, you can use sys.dm_db_file_space_usage DMV to find out total_page_count, allocated_extent_page_count as well as unallocated_extent_page_count within a database.

Page Free Space (PFS) pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page. The PFS has one byte for each page, recording whether the page is allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full, or 96 to 100 percent full.

A PFS page is the first page after the file header page in a data file (page number 1). This is followed by a GAM page (page number 2), and then an SGAM page (page 3). There is a PFS page approximately 8,000 pages in size after the first PFS page. There is another GAM page 64,000 extents after the first GAM page on page 2, and another SGAM page 64,000 extents after the first SGAM page on page 3. The following illustration shows the sequence of pages used by the Database Engine to allocate and manage extents.

Click here to enlarge - output using DBCC PAGE

enter image description here

Refer to : Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps

File header & SGAM & GAM & IAM pages are among the first pages within a file. What data-structure actually points to them ?

I do not get this question. The smallest unit of storage is a page. Eight 8K Pages form an extent. Below is what comprises of a data page:

enter image description here

I would highly recommend to read SQLSkills.com blog wherein Paul and his team blogs a lot about SQL Server internals.

Related Question