Tool Used : sys.dm_db_index_physical_stats
DMV
Conclusion : Forwarded
records are a type of fragmentation within heap tables that can cause poor SQL
Server performance.
In a heap it is possible to
get forwarded records. They occur when a
record in a heap expands such that it no longer fits on the page it currently
resides on. In this case, the record is moved to a new page, and a small forwarding record
is left in the original location. The forwarding record points to the new
location of the record, which is known as a forwarded record.
This is done as a performance optimization so that all the non clustered
indexes on the heap do not have to be altered with the new location of the heap
record.
When a larger value is updated
in a variable length field in a record, SQL Server will first try to expand the
row of the existing page on the chance that there’s enough room to continue to
use the original page. If that fails and SQL Server can’t find an existing page
with enough room for the larger value, a new page has to be created for the
record. The data is moved to the new page and is assigned a new Relative Identifier
(RID). The old page now contains a forwarding pointer (forwarded record) that
tells SQL Server to jump to the new RID. The new record location also has a
back pointer to the old record.
Forwarded records prevent
non-clustered indices on heap tables from being updated with the RID of the new
row. The overhead of updating indices is worse than replacing the old records
with a pointer. But the drawback to forwarded records is that their presence
could result in a large amount of I/O. For each forwarded record, SQL Server
has to jump to the target page, then back to the original one. This is a very
inefficient way to read a row, and it also wastes space.
There can be non-clustered
indexes in the Heap. So in case a record has to move to new page, at the old
location of the row it will leave a forwarding pointer. This mechanism is to
stop the updating of non-clustered indexes as it can still point to the old
location. So the modification is less costly than if all non-clustered indexes
needed to reflect the row move.
Let’s now stimulate a
condition where we will be getting the forwarding records, here we are creating
table Forwarding_Records_Demo
create table Forwarding_Records_Demo
(id bigint identity(1,1),
name varchar(200),
ordered_qty
int,
date_getdate datetime default getdate() )
Command(s) completed successfully.
Here we are creating two non cluster
index on date_getdate and ordered_qty
create nonclustered index
idx_date_getdate on Forwarding_Records_Demo(date_getdate)
create nonclustered
index idx_ordered_qty on
Forwarding_Records_Demo(ordered_qty)
Command(s) completed successfully.
Now lets insert some records in
newly created table
declare @i int =0
while @i < 100
begin
insert into Forwarding_Records_Demo(name,ordered_qty)
values ( 'DemoNames for ' + cast(@i as varchar(10)),rand(1) * 100 )
set @i = @i + 1
end
Let’s check the insertion
select COUNT(*) as No_of_Records from Forwarding_Records_Demo
No_of_Records
-------------
100
Let’s query the dm_db_index_physical_stats to check
forwarded record count.
select
object_name(ps.object_id) as tablename,
i.name as indexname,
ps.index_type_desc,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.forwarded_record_count
from
sys.dm_db_index_physical_stats
(db_id(), null, null, null,'detailed') as ps
inner join sys.indexes as i
on ps.object_id = i.object_id and ps.index_id = i.index_id
where object_name(ps.object_id) = 'Forwarding_Records_Demo'
indexname index_type_desc page_count
avg_fragmentation forwarded_
in_percent record_count
---------------------------------
---------------------------- ------------
NULL HEAP 1 0 NULL
idx_date_getdate NONCLUSTERED 1 0 NULL
idx_ordered_qty NONCLUSTERED 1
0 NULL
(3 row(s) affected)
Now let’s alter the column
size of name, and forces SQL Server to move those rows to another page
alter table Forwarding_Records_Demo
alter column
name char(400);
Command(s) completed successfully.
Now since we have modified the
column to 400 sizes, let’s re-run the same DMV query to check whether
select
object_name(ps.object_id) as tablename,
i.name as indexname,
ps.index_type_desc,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.forwarded_record_count
from
sys.dm_db_index_physical_stats
(db_id(), null, null, null,'detailed') as ps
inner join sys.indexes as i
on ps.object_id = i.object_id and ps.index_id = i.index_id
where object_name(ps.object_id) = 'Forwarding_Records_Demo'
indexname index_type_desc page_count
avg_fragmentation forwarded_
_in_percent record_count
---------------------------------
---------------------------- ------------
NULL HEAP 6 50 85
idx_date_getdate NONCLUSTERED 1
0 NULL
idx_ordered_qty NONCLUSTERED 1
0 NULL
(3 row(s) affected)
Now we have 85 forwarded
records.
Forwarding pointers are common
performance problem in Heaps. SQL Server 2008 introduced ALTER TABLE
... REBUILD which removes all forwarding pointers and performs a
rebuild
alter table
Forwarding_Records_Demo rebuild
Command(s) completed successfully.
Now let us re-examine the
forwarded recount again which will be back to zero by running the above DMV
query.
indexname index_type_desc page_count
avg_fragmentation forwarded_
in_percent record_count
---------------------------------
---------------------------- ------------
NULL HEAP 7 0 NULL
idx_date_getdate NONCLUSTERED 1 0 NULL
idx_ordered_qty NONCLUSTERED 1
0 NULL
(3 row(s) affected)
Now we have no forwarding
records.
Every ash
of mine will fabricate a novel code which will lessen up the world of programmer hitches
Post Reference: Vikram Aristocratic Elfin Share
No comments:
Post a Comment