Heap fragmentation is the degree of “out of order extents” and are given by avg_fragmentation_in_percent in sys.dm_db_index_physical_stats.
This means that when SQL server allocate pages in bulks of 8 pages per bulk that is called extents. Each page gets a page number and is ordered in the IAM. When a row gets written to a page on a new extent due to page split with forward pointers, we get a mismatch between the order in the IAM and the physical order that the pages must be read. This is what we call physical fragmentation, We also have logical fragmentation, but then we are talking about page fullness. We examine both these types more closely.
When data is inserted into a heap, sql server will first check the “page free space (PFS) allocation map”. PFS has 5 levels that describes the page fullness.
- 1-50 percent full
- 51-80 percent full
- 81-05 percent full
- 96-100 percent full
When evaluating if a row can be inserted into a page. SQL server uses the highest value on the level of fullness given by PFS. This means that if a page is 51 % full SQL server will evaluate if the row to be inserted is bigger or equal to the 20 percent remaining that level.
For example. A page consists of 8060 bytes. when the page is 51 percent full it remains 3949 bytes, however because of the PFS level, sql server will think of the page as 80 percent full, giving it only 1612 bytes remaining for the next row. If the row to be inserted is bigger than 1612 bytes a new page will be allocated leaving the page with significantly unused space available.
Updates operate in the same manner but in addition to maybe allocating a new page for the new version of the row to be updated, sql server will update the old row with a forwarding pointer to the new version of the row. Remember updates is only marking delete on a row, setting a forwarding pointer and insert a new row. The intention of forwarding pointers is that non-clustered indexes can still point to the old row and there by not have to be updated constantly and reflect every data change.
When sql server reads a heap with forwarding pointers it first has to read the original page, and then have to read the additional page that where the forwarding pointer directs. If you have multiple updates on a heap, there are a risk that the server must read multiple pages that are caused by updates, where pages pint to other pages and so on resulting in a performance hit. If the table is wide (many columns), the risk of a page having a lot of free space, due to the PFS factor evaluation is high, making the performance hit even greater.
To fix this issue the Heaps must be analyzed regularly through.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
and run a rebuilding of the table by
alter table schema.table REBUILD partition = all with (data_compression = PAGE)