Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to
Contact the author
for this blog
I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.
- Posted on 10 October 2012
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
The output will looks like as given below:
Let us create a table with data
SELECT * INTO SalesOrderHeaderTest FROM AdventureWorks2008.Sales.SalesOrderHeader
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
DROP TABLE SalesOrderHeaderTest
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
Leave a comment on the original post
[www.practicalsqldba.com, opens in a new window]