Database Reference
In-Depth Information
insert into dbo.Heap
select Val from CTE;
select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.Heap'),0,null,'DETAILED');
Following is the output of the code from Listing 2-1:
Result: 1 row per page. 4,100 bytes are used. 3,960 bytes are available per page
page_count avg_record_size_in_bytes avg_page_space_used_in_percent
---------------- ----------------------------- ---------------------------------------------
20 4100 50.6548060291574
At this point, the table stores 20 rows of 4,100 bytes each. SQL Server allocates 20 data pages—one page per
row—with 3,960 bytes available. PFS would indicate that pages are 51-80 percent full.
The code shown in Listing 2-2 inserts the small 111-byte row, which is about 1.4 percent of the page size. As a
result, SQL Server knows that the row would fit into one of the existing pages (they all have at least 20 percent of free
space available), and a new page should not be allocated.
Listing 2-2.
Inserting data into heap tables: Inserting a small row
insert into dbo.Heap(Val) values(replicate('1',100));
select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.Heap'),0,null,'DETAILED');
Following is the output of the code from Listing 2-2:
Result: 100 bytes row has been inserted into one of existing pages (100 bytes = ~1.4% of the page
size)
page_count avg_record_size_in_bytes avg_page_space_used_in_percent
---------------- ----------------------------- ---------------------------------------------
20 3910.047 50.7246108228317
Lastly, the third insert statement shown in Listing 2-3 needs 2,011 bytes for the row, which is about 25 percent of
the page size. SQL Server does not know if any of the existing pages have enough free space to accommodate the row
and, as a result, it allocates the new page. You can see that SQL Server does not access existing pages by checking the
actual free space, and it uses PFS data for the estimation.
Listing 2-3.
Inserting data into heap tables: Inserting a large row
insert into dbo.Heap(Val) values(replicate('2',2000));
select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.Heap'),0,null,'DETAILED');
