Database Reference
In-Depth Information
select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent, forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.ForwardingPointers'),0,null,'DETAILED');
set statistics io on
select count(*) from dbo.ForwardingPointers
set statistics io off
Following is the output of the code from Listing 2-4:
page_count avg_record_size_in_bytes avg_page_space_used_in_percent forwarded_record_count
----------- ------------------------ ------------------------------ ------------------------
1 2612.333 98.8742278230788 0
Table 'ForwardingPointers'. Scan count 1, logical reads 1
As you can see in Figure
2-3
, all three rows fit into the single page, and SQL Server needs to read just that page
when it scans the table.
Figure 2-3.
Forwarding pointers and I/O: Data pages after table creation
Now let's update two of the table rows, increasing their size. The new version of the rows will not fit into the page
anymore, which introduces the allocation of the two new pages and two forwarding pointers. Listing 2-5 shows the
code for this.
Listing 2-5.
Forwarding pointers and I/O: Increasing size of the rows
update dbo.ForwardingPointers set Val = replicate('1',5000) where ID = 1;
update dbo.ForwardingPointers set Val = replicate('3',5000) where ID = 3;
select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent, forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.ForwardingPointers'),0,null,'DETAILED');
set statistics io on
select count(*) from dbo.ForwardingPointers
set statistics io off
