First of all I would like to thank Uwe Ricken who alerted me to this point at the recent SQL Saturday in Manchester. I would also like to thank the organisers and sponsors of SQL Saturday, especially our local user group leader Chris Testa-O’Neill.

I recently blogged, to a small extent, about allocation pages. One of the allocation pages I blogged about was the Page Free Space (Or PFS for short) page. This tracks more than just the free space on a page. In fact it only tracks the free space in heap pages. It also tracks whether or not a page is an IAM page (Also briefly mentioned in my blog post about allocation pages) and a few other things. Please see this blog post by Paul Randal of SQL Skills for a fuller description of PFS pages.

Now the main point I want to call out is that the PFS page isn’t very granular in it’s ability to track page fullness. It tracks where in the following range of percent fullness a page is: –

• empty
• 1 to 50% full
• 51 to 80% full
• 81 to 95% full
• 96 to 100% full

It might be clear from these values that large, wide, records in a heap could cause problems. However if not please allow me to demonstrate with a small demo followed by an explanation. Try the below script in SSMS: –

[code language=”sql”]
— Use tempdb for simplicity
USE [tempdb];
GO

— Drop test object we intend to use and then create a test table to use
IF OBJECT_ID( N’dbo.demo’, ‘U’ ) IS NOT NULL DROP TABLE dbo.demo;
GO

CREATE TABLE dbo.demo(
data CHAR( 2000 )
);
GO

— We insert 4 values in one transaction
INSERT INTO dbo.demo( data )
VALUES( ‘a’ )
, ( ‘b’ )
, ( ‘c’ )
, ( ‘d’ );
GO

— Lets check the number of data pages (Should be 1 as these 4 rows fit)
SELECT allocated_page_file_id
, allocated_page_page_id
FROM sys.dm_db_database_page_allocations(
DB_ID( N’tempdb’ )
, OBJECT_ID( N’dbo.demo’, ‘U’ )
, 0
, 1
, ‘DETAILED’
)
WHERE is_allocated = 1
AND page_type = 1 — data page;
GO

— Now lets try that again with 4 separate transactions
TRUNCATE TABLE dbo.demo;
GO

INSERT INTO dbo.demo( data ) VALUES( ‘a’ );
INSERT INTO dbo.demo( data ) VALUES( ‘b’ );
INSERT INTO dbo.demo( data ) VALUES( ‘c’ );
INSERT INTO dbo.demo( data ) VALUES( ‘d’ );
GO

— Well isn’t that odd we now have 2 data pages
SELECT allocated_page_file_id
, allocated_page_page_id
FROM sys.dm_db_database_page_allocations(
DB_ID( N’tempdb’ )
, OBJECT_ID( N’dbo.demo’, ‘U’ )
, 0
, 1
, ‘DETAILED’
)
WHERE is_allocated = 1
AND page_type = 1 — data page;
GO
[/code]

Explanation of the issue: –

In a heap SQL Server uses the PFS page to track the amount of free space in a page. It does not read each individual page in the heap (Which would make large heaps unusable). When you add records in a single transaction (Like the first insert in the script) SQL Server will add the records until they no longer fit on the page and will then allocate another page and keep adding records there. However with individual transactions (Like the second set of inserts) the PFS page is checked each time we attempt to insert a record. Since the 4 records fit on a single page when we add them in a single transaction SQL Server fills the page (Well near enough – there’s actually 60 bytes left on the page with the records that I created) so we know that they should fit. However because we check the PFS page at the start of each insert for the second part of the demo with individual transactions the inserts go something like this: –

Insert 1: No data pages allocated so allocate a data page and insert 1 record.
Insert 2: Check the PFS page, our page is 25% full so shows as 50% fill (Due to the lack of granularity in how we track free space) we therefore insert the record on the same page.
Insert 3: Check the PFS page, our page is about 50% full and shows as 50% full, we therefore insert the record on the same page.
Insert 4: Check the PFS page, our page is about 75% full and shows as 80% full. The PFS page shows that there is not enough space for a row of 25% the size of the page. We therefore allocate a new page and insert the record there.

The consequence of this optimisation is that many heap pages for tables with wide rows are likely to have quite a bit of free space. Please note that this is not the case with a clustered index (Or a non clustered index but I’m just looking at table data to draw your attention to this) because in an index the amount of free space on a page is attained from the actual data pages and not the PFS pages.

To wrap up I’d like to point out that this issue can be resolved with the below command, however please be aware that this, similarly to rebuilding a clustered index, will force an update of all non-clustered indexes on the table, this command has worked since at least 2008 R2: –

[code language=”sql”]
ALTER TABLE dbo.demo REBUILD;
GO
[/code]