PFS Pages: How SQL Server tracks “Page Free Space”

Introduction

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.

Page Free Space (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 0 is unused.
• Bit 1 is set if the page is allocated
• Bit 2 is set if the page is part of a mixed extent
• Bit 3 is set if the page itself is an IAM page
• Bit 4 is set if the page contains ghost records
• Bits 5 - 7 show how full the page is

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:

• 000: The page is empty.
• 001: The page is between 1% and 50% full.
• 010: The page is between 51% and 80% full.
• 011: The page is between 81% and 95% full.
• 100: The page is between 96% and 100% full.

Other possible values for those three bits are not used.

PFS Interval

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.

Example PFS Page

DBCC PAGE with mode 3 neatly prepares the content of PFS pages in an easily readable table format.

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.

Summary

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.