How does SQL Server track space to allocate an object
PFS, GAM, SGAM pages
Have you ever wondered how SQL Server allocates space for objects?
To allocate an object, SQL Server needs to know the following:
Which pages are free
How much of a page is free
Which pages are allocated and
Which pages are not allocated
All this information is provided by 3 special pages called PFS, GAM and SGAM pages.
Any operation trying to create/insert/update/delete need a latch on these 3 special pages to determine the free space and allocation status of pages.
SQL Server engine uses these 3 special pages to track space and get allocation information for each page in a datafile.
These special tracking pages are PFS Page(Page 1), GAM Page(Page 2), SGAM Page(Page 3 ) and they are present on every .mdf/.ndf file.
Let’s look at each of these special pages and how many pages each can track.
PFS (Page Free Space): Each datafile begins with a File Header page(Page 0) and second page is PFS Page (Page 1). This page is used to track how much space is free in each page.
dbcc page on a PFS page will show up as:
dbcc traceon(3604) — to output results in SSMS
dbcc page(0,1,1,3) — dbid, filenumber, pagenumber, 3- page header plus detailed per-row interpretation
Let’s calculate how many pages 1 PFS page can track:
1 PFS Page = 8 KB
= 8*1024 bytes
= 8088 bytes
So, 1 PFS page has 8088 bytes (any page, for that matter). It needs 1 byte to track free space for 1 page. So, with 8088 bytes, a PFS page can track space for 8088 pages.
A PFS page appears once every 8088 pages. There can be multiple PFS pages in a datafile.
Any page number with a multiple of 8088 is a PFS page.
GAM Page(Global Allocation Map): This page is used to track if an extent is allocated or not, and it tracks only the uniform extents. An extent is 8 pages. Each page is 8KB. So, an extent is 64 KB. Uniform extent means all the pages in that extent have data belonging to the same object.
dbcc page on a GAM page will look like this:
dbcc traceon(3604) — to output results in SSMS
dbcc page(0,1,2,3) — dbid, filenumber, pagenumber, 3- page header plus detailed per-row interpretation
A GAM page by itself is a 8KB page like any other page.
1 GAM page = 8KB
= 8*1024 bytes
= 8192 bytes
Out of 8192 bytes, 204 bytes are reserved, there 7988 bytes left in a GAM page.
Let’s see how many data pages a GAM page with 7988 bytes can track.
1 GAM page = 7988 bytes
= 7988*8 bits
= 63904 bits
So, 1 GAM page has 63904 bits and takes 1 bit to track 1 extent, which means each GAM page can track 63904 extents.
Let’s convert 63904 extents to pages and size
1 GAM page can track 63904 extents
= 63904*8 pages
= 511232 pages
= 511232*8 KB
= 4,089,856 KB
= 4,089,856 KB/ 1024
= 3994 MB/1024
= almost 4 GB
So, 1 GAM page can cover tracking details for 511232 pages or 4GB of pages and a new GAM page will appear every 511232 pages to track the next 511232 pages.
Any page number with a multiple of 511232 is a GAM page.
SGAM (Shared Global Allocation Map) Page: This page is used to track if an extent is allocated or not, and it tracks only the mixed extents. An extent is 8 pages. Each page is 8KB. So, an extent is 64 KB. Mixed extent means all the pages in that extent may belong to different objects.
dbcc page on a SGAM page will look like this:
dbcc traceon(3604) — to output results in SSMS
dbcc page(0,1,3,3) — dbid, filenumber, pagenumber, 3- page header plus detailed per-row interpretation
Similar to a GAM page, SGAM page also covers tracking details for 511232 pages and appears once every 511232 pages right after a GAM page.
Any page number with a multiple of 511233 is a SGAM page.
GAM and SGAM use a combination of bits 0,1 to denote allocation status on its pages.
All operations trying to create/insert/update/delete need a latch on these 3 special pages to determine the free space and allocation status of pages.
Summary: PFS, GAM, and SGAM are three special pages present in every datafile used by SQL Server to track space and indicate the allocation status of each page within the datafile.
The PFS page can cover free space for 8088 pages, while each GAM and SGAM page can cover the allocation status of 511232 pages.
All operations trying to create/insert/update/delete need a latch on these 3 special pages to determine the free space and allocation status of pages.
Resources:
https://learn.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver16
https://sqlity.net/en/2331/gam-interval/