Database Reference
In-Depth Information
Following is the output of the code from Listing 2-3:
Result: New page has been allocated for 2000 bytes row (100 bytes = ~25% of the page size)
page_count avg_record_size_in_bytes avg_page_space_used_in_percent
---------------- ----------------------------------- -----------------------------------------
21 3823.727 49.4922782307882
That behavior leads to the situation where SQL Server unnecessarily allocates new data pages, leaving large
amounts of free space unused. It is not always a problem when the size of rows vary—in those cases, SQL Server
eventually fills empty spaces with the smaller rows. However, especially in cases when all rows are relatively large,
you can end up with large amounts of wasted space.
When selecting data from the heap table, SQL Server uses as Index Allocation Map (IAM) to find the pages and
extents that need to be scanned. It analyzes what extents belong to the table and processes them based on their
allocation order rather than on the order in which the data was inserted. Figure
2-1
illustrates this point.
Figure 2-1.
Selecting data from the heap table
When you update the row in the heap table, SQL Server tries to accommodate it on the same page. If there is
no free space available, SQL Server moves the new version of the row to another page and replaces the old row with
a special 16-byte row called a
forwarding pointer
. The new version of the row is called
forwarded row
. Figure
2-2
illustrates this point.
