Don't get scared by terms PFS, GAM and SGAM pages
Tracking pages in SQL Server
In this article, I’m only scratching the surface of PFS, GAM, and SGAM pages. There’s a lot more detail to explore and I could talk for hours! But my goal here is to provide SQL DBAs and developers with ‘just enough’ information to understand what these pages are, so, it will be easier for you to understand and troubleshoot allocation and contention issues.
Below is an image with the sequence of first 4 pages of a datafile. Every datafile contains PFS, GAM and SGAM pages and they’re always page 1, 2 and 3.
Datafile image Source - https://learn.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver16
PFS page
PFS page is Page Free Space, an 8KB page, just like any other page in SQL Server.
Every datafile .mdf or .ndf contains atleast one PFS page.
PFS page is always page 1 in a datafile.
It contains free space information of next 8087 pages.
So, for every 8088 pages there will be a new PFS page popping up.
To understand about the specific interval of 8088 pages, read here.
Run the following to see how PFS page looks internally:
dbcc traceon(3604) /*OUTPUT RESULTS IN SSMS*/
dbcc page(0,1,1,3) /* dbcc page(DBID, FILENUMBER, PAGENUMBER, PAGEHEADER) */
If you look at the image, notice how the last column shows PCT_FULL(percentage full), which tells how much percentage of the page is full.
For example, look at the first row - File ID 1 page 0 to File ID 1 page 3 is 100 percent full, and second row - page 4 to page 5 is 0 percent full. This is how PFS page contains free space information of data pages.
GAM page
GAM page is Global Allocation Map page, again an 8KB page just like any other page in SQL Server.
Every datafile .mdf or .ndf contains GAM page.
GAM page is always page 2 in a datafile.
It contains the allocation information of uniform extents.
An extent is 8 pages and uniform extent means all the 8 pages contain data of same object. See image below
There will be a new GAM page(followed by SGAM page) popping up in the datafile for every 511232 pages.
To understand about the specific interval of 511232 pages, read here.
511232 pages are ~4 GB, so for every 4GB in the datafile, there is a new GAM page. If there is a 8GB datafile, then there are chances of having 2 GAM and SGAM pages.
Image Source - https://learn.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver16
Run the following to see how GAM page looks internally:
dbcc traceon(3604) /*OUTPUT RESULTS IN SSMS*/
dbcc page(0,1,2,3) /* dbcc page(DBID, FILENUMBER, PAGENUMBER, PAGEHEADER) */
If you look at the image, notice how the last column shows ALLOCATED or NOT ALLOCATED value.
For example, look at the first row - File ID 1 page 0 to File ID 1 page 112 are allocated and second row - page 120 is not allocated. This is how GAM page contains allocation information of data pages.
SGAM page
SGAM page is Shared Global Allocation Map page, again an 8KB page just like any other page in SQL Server.
Every datafile .mdf or .ndf contains SGAM page.
SGAM page is always page 3 in a datafile.
It contains the allocation status of mixed extents.
An extent is 8 pages and mixed extent means those 8 pages in an extent will contain data of different objects. See image below
There will be a new SGAM page popping up in the datafile for every 511232 pages, followed after GAM page.
Difference between GAM and SGAM pages are GAM page tracks uniform extents and SGAM tracks mixed extents.
To understand about the specific interval of 511232 pages, read here.
Image Source - https://learn.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver16
Run the following to see how SGAM page looks internally:
dbcc traceon(3604) /*OUTPUT RESULTS IN SSMS*/
dbcc page(0,1,3,3) /* dbcc page(DBID, FILENUMBER, PAGENUMBER, PAGEHEADER) */
If you look at the image, notice how the last column shows ALLOCATED or NOT ALLOCATED value.
For example, look at the first row - File ID 1 page 0 to File ID 1 page 512 are not allocated and second row - page 520 is allocated. This is how SGAM page contains allocation information of data pages.
Summary
The 3 special pages, PFS, GAM and SGAM pages, contain free space information and allocation information of the data pages in a datafile.
PFS is always page 1 and repeats every 8,088 pages.
GAM is page 2 and repeats every 511,232 pages, followed directly by the SGAM page.
Read here to understand how these special pages help SQL Server allocate an object.
Good content and useful information.
Useful information, Thanks🙏