SQL Server: Understanding page Free Space (PFS) pages

We have discussed the GAM with Sgam page, the data page, and now we look at the page Free Space page (PFS) .

PFS is the 2nd page in the Data file (page No. 1, page number starting from 0), followed by the header (file header, page number 0). GAM and Sgam are used to track the allocation of a zone, and the PFS page is used to track page assignment levels (page level allocation). When assigning pages, the database engine uses GAM and SGAM to determine the extents of pages that are available. Once the database engine finds a zone with available pages, it uses the PFS page to determine how much space is available on the page that is not allocated in that area. Free space is tracked only for pages where LOB values are stored (such as text/image, varchar (max), nvarchar (max), varbinary (max), row overflow data), or heap pages. By default, LOB data is saved on a separate page, only by keeping a pointer to a separate page on the data page. Data can be saved on pages that are available for free. For index pages, the data should be saved in the same order as the index, so there is no word to insert the record pointer. There is no need to track the available space in the index.

Both GAM and Sgam have bitmaps, but PFS pages are only byte graphs. In the PFS page interval it retains 1 bits for each page. A PFS page can hold 8088 pages of free space usage information.

The information for each bit in each byte represents the following:

• 第0-2位: How much free space is available in the page
  • 0x00: Empty, not using
  • 0x02:51%-80% is occupied
  • 0x04:96%-100% is occupied
• 5th Bit (0x20): Is the current page a mixed page?
• 6th bit (0x40): is the current page assigned?
• 7th bit: Not used

  let's look at a specific example: Create a new database and use the DBCC PAGE command to view information about the PFS page:

 1  create  database   Pfsdb  2  3  use   Pfsdb  4  5  dbcc  TRACEON (3604  )  6   7  DBCC  page (Pfsdb,1 , 1 , 3 ) 

........

As you can see, the space is not allocated from 180 pages to 287 pages.

We insert point data into the table and then use the DBCC page to view information about the PFS page:

1 SELECT *  into  from AdventureWorks2008R2.Sales.SalesOrderHeader 2 DBCC TRACEON (3604)3GO4DBCC PAGE (pfsdb,1, 1,3)

......

You can see that there are new pages added from page 288 to page 1311.

Let's drop the table now and look at the information on the PFS page:

......

You can see that the page you just assigned is now unassigned , but the space is still 100% occupied. This is because the PFS bytes are not completely reset before the page is reassigned. When reassigned, the database engine resets the allocation state bit only, which allows the database engine to reset the allocation state bit when the rollback is reassigned.

SQL Server: Understanding page Free Space (PFS) pages