SQL Server:Understanding The Page Free Space (PFS) Page

In my earlier post, we have discussed about GAM and SGAM Page and Data Page. In this postlet us discuss about the Page Free Space (PFS) page.

PFS page is the second  page (Page Number 1) in the data file followed by file header (Page Number 0). GAM and SGAM are used to track the extent allocation status where as PFS pages are  used to track page level allocation. While allocating pages , database engine identify the extent with free pages using the GAM and SGAM. Once the database engine  found extent with free page, it use the PFS page to identify the unallocated page in that extent and amount of free space in the page. Free space is only tracked for pages storing LOB values  (ie  text/image, varchar(max),nvarchar(max),varbinary(max) ,row overflow data) and heap data pages. By default LOB data are stored in a separate page by keeping only a pointer along with record stored in the data pages. These are the only pages where data can be stored in the available free pages.For index pages, the data should be stored in the order of index and there is no options in the insertion point of the records.So it will be unnecessary overhead of tracking free space in the index pages. 

GAM and SGAM have bitmap, but PFS page has a byte map. PFS page keep one byte for each page in the PFS interval. A PFS page can hold the information of 8088 page. 

The bits in each byte are encoded to mean the following:

• bits 0-2: how much free space is on the page
• 0x00 is empty
• 0x01 is 1 to 50% full
• 0x02 is 51 to 80% full
• 0x03 is 81 to 95% full
• 0x04 is 96 to 100% full
• bit 3 (0x08): is there one or more ghost records on the page?
• bit 4 (0x10): is the page an IAM page?
• bit 5 (0x20): is the page a mixed-page?
• bit 6 (0x40): is the page allocated?
• Bit 7 is unused

Let us try to explore a PFS page

CREATE DATABASE mydb
GO
USE Mydb
GO
DBCC TRACEON(3604)
GO
DBCC PAGE(mydb,1,1,3)

The output will looks like as given below:















Let us create a table with data

USE Mydb
GO
SELECT * INTO SalesOrderHeaderTest FROM AdventureWorks2008.Sales.SalesOrderHeader
GO
DBCC TRACEON(3604)
GO
DBCC PAGE(mydb,1,1,3)

The output will looks like as given below. You can see that new pages added from 288 to 1311.


















Now let us drop this table

USE Mydb
GO
DROP TABLE SalesOrderHeaderTest 
GO
DBCC TRACEON(3604)
GO
DBCC PAGE(mydb,1,1,3)

The output will look like as shown below:

You will notice that, a set of pages are in not allocated state but 100 percent full.This is because PFS bytes are not fully reset until the page is reallocated. On deallocation, database engine reset only the allocation status bit, this helps the database engine to rollback the deallocation by only resetting the allocation status bit.

Reference :Paul Randal Blog post 

If you liked this post, do like my page on FaceBook