SQL Server 存储引擎-剖析Forwarded Records

create database testpage
go
use testpage
go
create table testtb
(
id int identity(1,1),
str1 char(100) default replicate('a',100),
str2 varchar(2000) default replicate('b',500),
str3 varchar(2000) default replicate('c',1000)
)
go

insert into testtb default values
go 20

DBCC TRACEON(3604)

GO

DBCC IND(testpage,'testtb', 1)---find the data page

GO

DBCC PAGE('testpage', 1, 79, 3)-----view data page find slot 2(ID=3)

GO

insert into testtb default values
go 20----先插入一些数据

update dbo. Testtb set str2=replicate('t', 2000),str3=replicate('t', 2000) where ID=3 GO---继续更新ID=3

DBCC PAGE('testpage', 1, 79, 3)--------源ID=3,现在执行(1:184:2)
GO
DBCC PAGE('testpage', 1, 94, 3)--------第一次修改时ID=3存储位置(1:94:0),现在slot 0没有了
GO
DBCC PAGE('testpage', 1, 184, 3)------目前id=3的数据存储位置
GO

CREATE UNIQUE NONCLUSTERED INDEX inx_1 ON testtb (id )

DBCC IND(testpage,'testtb', -1)----find the index page (page type 2)115
GO
DBCC PAGE('testpage', 1, 115, 3)---find the heap rid where id=3 heap rid =0x4F00000001000200

DECLARE @HeapRid BINARY(8)
SET @HeapRid = 0x4F00000001000200
       SELECT
       CONVERT (VARCHAR(5),
                    CONVERT(INT, SUBSTRING(@HeapRid, 6, 1)
                               + SUBSTRING(@HeapRid, 5, 1)))
     + ':'
     + CONVERT(VARCHAR(10),
                    CONVERT(INT, SUBSTRING(@HeapRid, 4, 1)
                               + SUBSTRING(@HeapRid, 3, 1)
                               + SUBSTRING(@HeapRid, 2, 1)
                               + SUBSTRING(@HeapRid, 1, 1)))
     + ':'
          + CONVERT(VARCHAR(5),
                    CONVERT(INT, SUBSTRING(@HeapRid, 8, 1)
                               + SUBSTRING(@HeapRid, 7, 1)))
                               AS 'Fileid:Pageid:slot'

set statistics io on

select * from testtb where id=2

select * from testtb where id=3

create table testbp
(
id int identity(1,1),
str1 char(100) default replicate('a',100),
str2 varchar(2000) default replicate('b',500),
str3 varchar(2000) default replicate('c',1000)
)
go
insert into testbp default values
go 10000

dbcc dropcleanbuffers
select * from testbp

SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors with(nolock)
where db_name(database_id)='testpage'
GROUP BY db_name(database_id) ,database_id
-----buffer pool 15MB

update dbo. testbp set str2=replicate('t', 1000)---make forwarded recordes

dbcc dropcleanbuffers

select * from testbp


SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors with(nolock)
where db_name(database_id)='testpage'
GROUP BY db_name(database_id) ,database_id
------31MB

select 
    object_name(object_id) as objectName
    ,index_type_desc
    ,forwarded_record_count
 from 
    sys.dm_db_index_physical_stats(db_id(),null,null,null, 'detailed')
    where object_name(object_id)='testbp'
------view the forwarded records info