table scan against heap with forward pointer
This post was published to stswordman at 6:09:31 PM 4/14/2009
table scan against heap with forward pointer
Sql server维护了一个内部指针，它指向了表的第一个IAM页,还有一个执行表的首页的内部指针
对一个表(heap)执行table scan时，sql server会按照IAM顺序查找,如果在页A发现了forward point，会立刻去相应的页（例如页B）查找的记录（产生一个逻辑读），然后返回页A继续遍历，当按照IAM遍历到页B时，不会将页A的forward pointer的所指向的记录重复加载 。
实验：
表A有8个数据页（A，B，C...H)，占满1个extent(Uniform extents)，对第A页的行a进行update操作，使起A页无法继续容纳行a,
sql server 会将行a的位置替换成forward pointer。 将行a上的内容移动到新的页（位于新的extent）,继续插入数据...
执行select *from A查看结果
use tempdb
go
Dbcc traceon(1118,-1)
go
drop table A
CREATE TABLE A (c1 INT, c2 VARCHAR (8000));
go
DECLARE @n INT;
SELECT @n = 0;
WHILE (@n < 16)
BEGIN
INSERT INTO A VALUES (@n, replicate (char(97+@n),4000))
SELECT @n +=1
END
go
update A set c2=replicate('z',5000) where c1=0--generate a forward pointer
go
--check the forward pointer
select forwarded_record_count from sys.dm_db_index_physical_stats(db_id(),object_id('A'),null,null,'DETAILED')
go
DECLARE @n INT;
SELECT @n = 16;
WHILE (@n < 20)
BEGIN
INSERT INTO A VALUES (@n, replicate (char(97+@n),1000))
SELECT @n +=1
END
go
set statistics io on--查看逻辑读=data page+forward pointer的数量
go
SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID],* FROM Ago
go
Dbcc traceon(1118,-1)
go
drop table A
CREATE TABLE A (c1 INT, c2 VARCHAR (8000));
go
DECLARE @n INT;
SELECT @n = 0;
WHILE (@n < 16)
BEGIN
INSERT INTO A VALUES (@n, replicate (char(97+@n),4000))
SELECT @n +=1
END
go
update A set c2=replicate('z',5000) where c1=0--generate a forward pointer
go
--check the forward pointer
select forwarded_record_count from sys.dm_db_index_physical_stats(db_id(),object_id('A'),null,null,'DETAILED')
go
DECLARE @n INT;
SELECT @n = 16;
WHILE (@n < 20)
BEGIN
INSERT INTO A VALUES (@n, replicate (char(97+@n),1000))
SELECT @n +=1
END
go
set statistics io on--查看逻辑读=data page+forward pointer的数量
go
SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID],* FROM Ago
一些相关文章：
When can allocation order scans be used
Geek City: What's Worse Than a Table Scan?
