GAM and SGAM pages contain information about the allocation status of extents. They both however do not tell us anything about how full single pages are. That information is provided by another page type, the Page Free Space or PFS pages.
A PFS page does not contain a bitmap like GAM and SGAM pages. Instead, it contains a byte-map. Each page in a database file is associated with a single byte in one of the PFS pages. The byte contains information about how much space is still available on the page as well as a few status bits:
Bit 1 identifies for example unallocated pages that are part of an allocated (or mixed) extent. Bit 2 is set for all pages that are part of a mixed extent. Bit 3 allows SQL Server to identify IAM pages without reading the page itself. Bit 4 finally indicates that the page contains logically deleted records that have not yet been cleaned up by the ghost cleanup task.
Possible values for bits 5 - 7 are:
Other possible values for those three bits are not used.
A PFS page is of type 11. 8088 bytes of the page make up the byte-map. That means that a new PFS page is required every 8088 pages. A section of 8088 pages is therefore called a PFS Interval. The first PFS page is page 1 in all SQL Server database files. It covers pages 0 to 8087. After that, the first page of each PFS interval contains the PFS page for that interval: 8088, 16176, 24264 and so on.
DBCC PAGE with mode 3 neatly prepares the content of PFS pages in an easily readable table format.
DBCC TRACEON(3604); DBCC PAGE(0,1,1,3);
Executing these statements will produce a result similar to the following:
The pages are grouped into ranges of pages with equal bit values. The space used bits are translated into the upper boundary of the interval they represent. E.g. 010 which represent 51% to 80% full is shown as 80_PCT_FULL. The other bits are spelled out. IAM pages for example show the text IAM Page.
SQL Server uses PFS pages to track the amount of space still available on each page. In addition, each page has four status bits that are also stored on the PFS pages.