/**********************************************************************************************
-- Title : [2k] Ghost Rec, Forward Rec.No Ghost Rec 확인(heap, clustered)
-- Reference : dbRang.com
-- Key word : 고스트 ghost 포워드 forward dbcc trace dbcc checktable dbcc page
**********************************************************************************************/
/*
-- FORWARD REC ON HEAP
-- 힙에서는 포워드레코드가 발생하나 고스트는 발생하지 않는다.
*/
use credit
drop table c포워드테스트CX
select top 100000 * into c포워드테스트CX from charge
alter table c포워드테스트CX add vc1 varchar(10) default 'a'
--create index ix on c포워드테스트CX (vc1)
exec sp_spaceused c포워드테스트CX
/*
name rows reserved data index_size unused
---------------- ----------- ------------------ ------------------ ------------------ ------
c포워드테스트CX 100000 4688 KB 4664 KB 8 KB 16 KB
*/
begin tran
update c포워드테스트CX set vc1 = 'abcdefgh' where charge_no % 10 = 0
exec sp_spaceused c포워드테스트CX
/*
name rows reserved data index_size unused
---------------- ----------- ------------------ ------------------ ------------------ ------
c포워드테스트CX 100000 4936 KB 4872 KB 8 KB 56 KB
*/
dbcc checktable (c포워드테스트CX)
/*
'c포워드테스트CX'의 DBCC결과입니다.
609 페이지에 'c포워드테스트CX' 개체에 대한 행이 100000개 있습니다.
Forwarded Record count = 2905
Ghost Record count = 0
*/
exec sp_converthex0 'c포워드테스트CX'
/*
object_name firstDec first rootDec root firstIAMDec firstIAM
---------------- -------- -------------- ------- -------------- -------------- --------------
c포워드테스트CX 1:5036 0xAC1300000100 1:6413 0x0D1900000100 1:5037 0xAD1300000100
*/
dbcc traceon(3604)
dbcc traceon(2514) --ghost rec.
dbcc traceon(2509) --forward rec.
dbcc page (credit, 1, 5036,3)
/*
Slot 0 Offset 0x60
------------------
Record Type = FORWARDED_RECORD
*/
commit tran
dbcc checktable (c포워드테스트CX)
/*
'c포워드테스트CX'의 DBCC결과입니다.
609 페이지에 'c포워드테스트CX' 개체에 대한 행이 100000개 있습니다.
Forwarded Record count = 2905
Ghost Record count = 0
*/
/*
-- GHOST REC ON CLUSTERED.
-- 클러스터드에서는 포워드 대신 고스트 레코드가 발생한다.
-- 고스트는 삭제시 발생하나 CL의 업데이트는 delete/insert방식이기에 발생한다.
*/
use credit
drop table c포워드테스트CX
select top 100000 * into c포워드테스트CX from charge
alter table c포워드테스트CX add vc1 varchar(10) default 'abcd'
create clustered index cx on c포워드테스트CX (vc1)
create index ix on c포워드테스트CX (member_no)
create index ix2 on c포워드테스트CX (charge_dt)
exec sp_spaceused c포워드테스트CX
/*
name rows reserved data index_size unused
---------------- ----------- ------------------ ------------------ ------------------ ------
c포워드테스트CX 100000 10112 KB 5560 KB 4440 KB 112 KB
*/
begin tran
update c포워드테스트CX set vc1 = 'abcdefgh' where charge_no % 3 = 0
exec sp_spaceused c포워드테스트CX
/*
name rows reserved data index_size unused
---------------- ----------- ------------------ ------------------ ------------------ ------
c포워드테스트CX 100000 18560 KB 7744 KB 10800 KB 16 KB
*/
dbcc checktable (c포워드테스트CX)
/*
968 페이지에 'c포워드테스트CX' 개체에 대한 행이 100000개 있습니다.
Forwarded Record count = 0
Ghost Record count = 4860
*/
exec sp_converthex0 'c포워드테스트CX'
/*
object_name indid firstDec first rootDec root firstIAMDec firstIAM
----------- ------ -------- -------------- ------- -------------- -------------- --------------
cx 1 1:4472 0x781100000100 1:4849 0xF11200000100 1:5039 0xAF1300000100
ix 2 1:7344 0xB01C00000100 1:7346 0xB21C00000100 1:5036 0xAC1300000100
ix2 3 1:7848 0xA81E00000100 1:8127 0xBF1F00000100 1:5037 0xAD1300000100
*/
dbcc traceon(3604)
dbcc traceon(2514) --ghost rec.
dbcc traceon(2509) --forward rec.
dbcc page (credit, 1, 4472,3)
/*
Slot 2 Offset 0xc4
------------------
Record Type = GOAST_DATA_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
19F800C4: 002a0030 00008663 000011f5 00000172 0.*.c.......r...
19F800D4: 00000007 00e2d26d 000096da 01d92cd0 ....m........,..
19F800E4: 00000000 000011f5 00092020 00010100 ........ ......
19F800F4: 00020036 0000 6.....
charge_no = 34403
member_no = 4597
provider_no = 370
category_no = 7
charge_dt = 09 25 2005 1:45PM
charge_amt = 3101.00
statement_no = 4597
charge_code =
vc1 = [NULL]
*/
commit tran
dbcc checktable (c포워드테스트CX)
/*
968 페이지에 'c포워드테스트CX' 개체에 대한 행이 100000개 있습니다.
Forwarded Record count = 0
Ghost Record count = 4860
*/
/**********************************************************************************************
-- NO GHOST REC. ON HEAP
-- 테스트 실패
**********************************************************************************************/
use credit
drop table c삭제테스트
select * into c삭제테스트 from charge
create index pk on c삭제테스트 (charge_no)
create index ix on c삭제테스트 (member_no)
create index ix2 on c삭제테스트 (charge_dt)
exec sp_spaceused c삭제테스트
/*
name rows reserved data index_size unused
----------- ----------- ------------------ ------------------ ------------------ -------
c삭제테스트 100001 14640 KB 4664 KB 4968 KB 5008 KB
*/
begin tran
delete c삭제테스트 where charge_no % 3 = 0
exec sp_spaceused c삭제테스트
/*
name rows reserved data index_size unused
----------- ----------- ------------------ ------------------ ------------------ -------
c삭제테스트 66668 14640 KB 4664 KB 4968 KB 5008 KB
*/
exec sp_converthex0 'c삭제테스트'
/*
object_name indid firstDec rootDec firstIAMDec
------------- ------ -------------- -------------- --------------
c삭제테스트 0 1:6085 1:8237 1:6086
pk 2 0:0 1:8842 1:8242
ix 3 0:0 1:9170 1:8243
ix2 4 0:0 1:9322 1:8244
*/
dbcc traceon(3604)
dbcc traceon(2514) --ghost rec.
dbcc traceon(2509) --forward rec.
dbcc page (credit, 1, 8842,3) --charge_no
dbcc page (credit, 1, 8842,1) --charge_no
dbcc page (credit, 1, 9170,3)
commit tran
/* 테스트 확인 실패 */