1. Create sample db
use master
go
CREATE DATABASE [test] ON PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB)
GO
2. Create table
use test
GO
BEGIN TRANSACTION
GO
CREATE TABLE dbo.mytest
(
a int NOT NULL,
b varchar(500) NOT NULL,
d varchar(400) NULL,
) ON [PRIMARY]
GO
ALTER TABLE dbo.mytest ADD CONSTRAINT
PK_mytest PRIMARY KEY CLUSTERED
(
a
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
3. Insert test data
use test
go
Declare @int int
set @int=1
while (@int <= 3000)
begin
insert into mytest(a,b,d)
values(@int,CONVERT(char(500),@int),CONVERT(char(400),@int))
set @int=@int+1
end
4. select data and check page number
use master
GO
SELECT %%physloc%% rowid, sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID],DATALENGTH(a) a_length, DATALENGTH(b) b_length,DATALENGTH(d) d_length, *
FROM test.dbo.mytest where a<9
GO
output
| rowid | Physical RID | a_length | b_length | d_length | a | b | d |
| 0x9900000001000000 | (1:153:0) | 4 | 500 | 400 | 1 | 1 | 1 |
| 0x9900000001000100 | (1:153:1) | 4 | 500 | 400 | 2 | 2 | 2 |
| 0x9900000001000200 | (1:153:2) | 4 | 500 | 400 | 3 | 3 | 3 |
| 0x9900000001000300 | (1:153:3) | 4 | 500 | 400 | 4 | 4 | 4 |
| 0x9900000001000400 | (1:153:4) | 4 | 500 | 400 | 5 | 5 | 5 |
| 0x9900000001000500 | (1:153:5) | 4 | 500 | 400 | 6 | 6 | 6 |
| 0x9900000001000600 | (1:153:6) | 4 | 500 | 400 | 7 | 7 | 7 |
| 0x9900000001000700 | (1:153:7) | 4 | 500 | 400 | 8 | 8 | 8 |
so 8 rows in pages: (1:153)
5. delete row and print page
use test
go
delete from mytest where a=3
go
DBCC TRACEON(3604)
GO
dbcc page(test,1,153,2)
GO
6. check page 153.
Here I only print the content which is changed after data deleting
a) in page header
m_xactReserved = 0 m_xdesId = (0:4099) m_ghostRecCnt = 1
#m_ghostRecCnt=1: there is 1 ghost record
b) in the data section:
000000001485A780: 20202020 20202020 20203c00 08000300 ? <.....
000000001485A790: 00000300 00020005 02950333 20202020 ?.........?.3
000000001485A7A0: 20202020 20202020 20202020 20202020 ?
#the first 2 bytes of row(a=3) is changed from "30" to "3c"= 0011 1100
0011=Bit 4 means the record has a NULL bitmap and bit 5 means the record has variable length columns
1100 : Bits 1-3 of byte 0 give the record type, 110=6=ghost record
c) there is no change in the offset table, still 8 records in it.
Row - Offset
7 (0x7) - 6515 (0x1973)
6 (0x6) - 5598 (0x15de)
5 (0x5) - 4681 (0x1249)
4 (0x4) - 3764 (0xeb4)
3 (0x3) - 2847 (0xb1f)
2 (0x2) - 1930 (0x78a)
1 (0x1) - 1013 (0x3f5)
0 (0x0) - 96 (0x60)
7. wait for while and check the page again
DBCC TRACEON(3604)
GO
dbcc page(test,1,153,2)
GO
a) page header
m_xactReserved = 0 m_xdesId = (0:4099) m_ghostRecCnt = 0
#ghost record is gone now
b) row data
00000000140DA780: 20202020 20202020 20203c00 08000300 ? <.....
00000000140DA790: 00000300 00020005 02950333 20202020 ?.........?.3
00000000140DA7A0: 20202020 20202020 20202020 20202020 ?
#still no change in the row data
c) offset table
Row - Offset
6 (0x6) - 6515 (0x1973)
5 (0x5) - 5598 (0x15de)
4 (0x4) - 4681 (0x1249)
3 (0x3) - 3764 (0xeb4)
2 (0x2) - 2847 (0xb1f)
1 (0x1) - 1013 (0x3f5)
0 (0x0) - 96 (0x60)
#only 7 rows now, the row (a=3) is gone from the offset table
Here is a question:
if we insert the same data back, is it inserted into the same place? or reorganize the page?
8) insert data back again.
use test
go
insert into mytest(a,b,d)
values(3,CONVERT(char(500),3),CONVERT(char(400),3))
GO
use master
go
SELECT %%physloc%% rowid, sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID],DATALENGTH(a) a_length, DATALENGTH(b) b_length,DATALENGTH(d) d_length, *
FROM test.dbo.mytest where a<10
GO
output:
| rowid | Physical RID | a_length | b_length | d_length | a | b | d |
| 0x9900000001000000 | (1:153:0) | 4 | 500 | 400 | 1 | 1 | 1 |
| 0x9900000001000100 | (1:153:1) | 4 | 500 | 400 | 2 | 2 | 2 |
| 0x9900000001000200 | (1:153:2) | 4 | 500 | 400 | 3 | 3 | 3 |
| 0x9900000001000300 | (1:153:3) | 4 | 500 | 400 | 4 | 4 | 4 |
| 0x9900000001000400 | (1:153:4) | 4 | 500 | 400 | 5 | 5 | 5 |
| 0x9900000001000500 | (1:153:5) | 4 | 500 | 400 | 6 | 6 | 6 |
| 0x9900000001000600 | (1:153:6) | 4 | 500 | 400 | 7 | 7 | 7 |
| 0x9900000001000700 | (1:153:7) | 4 | 500 | 400 | 8 | 8 | 8 |
so confirmed the original row(a=3) is inserted into page 153, and rowid is still 1:153:2.
9) let's check the page
DBCC TRACEON(3604)
GO
dbcc page(test,1,153,2)
GO
the Most important change is:
the page is reorganized, rows (4,5,6,7) have been moved up, and the deleted slot is overwrote. then the new row is added at the tail of the original content. here is the page, I marked the row header with red
PAGE: (1:153)
DATA:
Memory Dump @0x00000000140DA000
00000000140DA000: 01010400 00000001 00000000 00000800 ?................
00000000140DA010: 9c000000 01000800 1c000000 e802081d ??...........è...
00000000140DA020: 99000000 01000000 30000000 8d010000 ?........0.......
00000000140DA030: 04000000 03100000 00000000 6f0c6a1c ?............o.j.
00000000140DA040: 01000000 00000000 00000000 00000000 ?................
00000000140DA050: 00000000 00000000 00000000 00000000 ?................
00000000140DA060: 30000800 01000000 03000002 00050295 ?0..............?
00000000140DA070: 03312020 20202020 20202020 20202020 ?.1
00000000140DA080: 20202020 20202020 20202020 20202020 ?
00000000140DA090: 20202020 20202020 20202020 20202020 ?
00000000140DA0A0: 20202020 20202020 20202020 20202020 ?
00000000140DA0B0: 20202020 20202020 20202020 20202020 ?
00000000140DA0C0: 20202020 20202020 20202020 20202020 ?
00000000140DA0D0: 20202020 20202020 20202020 20202020 ?
00000000140DA0E0: 20202020 20202020 20202020 20202020 ?
00000000140DA0F0: 20202020 20202020 20202020 20202020 ?
00000000140DA100: 20202020 20202020 20202020 20202020 ?
00000000140DA110: 20202020 20202020 20202020 20202020 ?
00000000140DA120: 20202020 20202020 20202020 20202020 ?
00000000140DA130: 20202020 20202020 20202020 20202020 ?
00000000140DA140: 20202020 20202020 20202020 20202020 ?
00000000140DA150: 20202020 20202020 20202020 20202020 ?
00000000140DA160: 20202020 20202020 20202020 20202020 ?
00000000140DA170: 20202020 20202020 20202020 20202020 ?
00000000140DA180: 20202020 20202020 20202020 20202020 ?
00000000140DA190: 20202020 20202020 20202020 20202020 ?
00000000140DA1A0: 20202020 20202020 20202020 20202020 ?
00000000140DA1B0: 20202020 20202020 20202020 20202020 ?
00000000140DA1C0: 20202020 20202020 20202020 20202020 ?
00000000140DA1D0: 20202020 20202020 20202020 20202020 ?
00000000140DA1E0: 20202020 20202020 20202020 20202020 ?
00000000140DA1F0: 20202020 20202020 20202020 20202020 ?
00000000140DA200: 20202020 20202020 20202020 20202020 ?
00000000140DA210: 20202020 20202020 20202020 20202020 ?
00000000140DA220: 20202020 20202020 20202020 20202020 ?
00000000140DA230: 20202020 20202020 20202020 20202020 ?
00000000140DA240: 20202020 20202020 20202020 20202020 ?
00000000140DA250: 20202020 20202020 20202020 20202020 ?
00000000140DA260: 20202020 20312020 20202020 20202020 ? 1
00000000140DA270: 20202020 20202020 20202020 20202020 ?
00000000140DA280: 20202020 20202020 20202020 20202020 ?
00000000140DA290: 20202020 20202020 20202020 20202020 ?
00000000140DA2A0: 20202020 20202020 20202020 20202020 ?
00000000140DA2B0: 20202020 20202020 20202020 20202020 ?
00000000140DA2C0: 20202020 20202020 20202020 20202020 ?
00000000140DA2D0: 20202020 20202020 20202020 20202020 ?
00000000140DA2E0: 20202020 20202020 20202020 20202020 ?
00000000140DA2F0: 20202020 20202020 20202020 20202020 ?
00000000140DA300: 20202020 20202020 20202020 20202020 ?
00000000140DA310: 20202020 20202020 20202020 20202020 ?
00000000140DA320: 20202020 20202020 20202020 20202020 ?
00000000140DA330: 20202020 20202020 20202020 20202020 ?
00000000140DA340: 20202020 20202020 20202020 20202020 ?
00000000140DA350: 20202020 20202020 20202020 20202020 ?
00000000140DA360: 20202020 20202020 20202020 20202020 ?
00000000140DA370: 20202020 20202020 20202020 20202020 ?
00000000140DA380: 20202020 20202020 20202020 20202020 ?
00000000140DA390: 20202020 20202020 20202020 20202020 ?
00000000140DA3A0: 20202020 20202020 20202020 20202020 ?
00000000140DA3B0: 20202020 20202020 20202020 20202020 ?
00000000140DA3C0: 20202020 20202020 20202020 20202020 ?
00000000140DA3D0: 20202020 20202020 20202020 20202020 ?
00000000140DA3E0: 20202020 20202020 20202020 20202020 ?
00000000140DA3F0: 20202020 20300008 00020000 00030000 ? 0..........
00000000140DA400: 02000502 95033220 20202020 20202020 ?....?.2
00000000140DA410: 20202020 20202020 20202020 20202020 ?
00000000140DA420: 20202020 20202020 20202020 20202020 ?
00000000140DA430: 20202020 20202020 20202020 20202020 ?
00000000140DA440: 20202020 20202020 20202020 20202020 ?
00000000140DA450: 20202020 20202020 20202020 20202020 ?
00000000140DA460: 20202020 20202020 20202020 20202020 ?
00000000140DA470: 20202020 20202020 20202020 20202020 ?
00000000140DA480: 20202020 20202020 20202020 20202020 ?
00000000140DA490: 20202020 20202020 20202020 20202020 ?
00000000140DA4A0: 20202020 20202020 20202020 20202020 ?
00000000140DA4B0: 20202020 20202020 20202020 20202020 ?
00000000140DA4C0: 20202020 20202020 20202020 20202020 ?
00000000140DA4D0: 20202020 20202020 20202020 20202020 ?
00000000140DA4E0: 20202020 20202020 20202020 20202020 ?
00000000140DA4F0: 20202020 20202020 20202020 20202020 ?
00000000140DA500: 20202020 20202020 20202020 20202020 ?
00000000140DA510: 20202020 20202020 20202020 20202020 ?
00000000140DA520: 20202020 20202020 20202020 20202020 ?
00000000140DA530: 20202020 20202020 20202020 20202020 ?
00000000140DA540: 20202020 20202020 20202020 20202020 ?
00000000140DA550: 20202020 20202020 20202020 20202020 ?
00000000140DA560: 20202020 20202020 20202020 20202020 ?
00000000140DA570: 20202020 20202020 20202020 20202020 ?
00000000140DA580: 20202020 20202020 20202020 20202020 ?
00000000140DA590: 20202020 20202020 20202020 20202020 ?
00000000140DA5A0: 20202020 20202020 20202020 20202020 ?
00000000140DA5B0: 20202020 20202020 20202020 20202020 ?
00000000140DA5C0: 20202020 20202020 20202020 20202020 ?
00000000140DA5D0: 20202020 20202020 20202020 20202020 ?
00000000140DA5E0: 20202020 20202020 20202020 20202020 ?
00000000140DA5F0: 20202020 20202020 20203220 20202020 ? 2
00000000140DA600: 20202020 20202020 20202020 20202020 ?
00000000140DA610: 20202020 20202020 20202020 20202020 ?
00000000140DA620: 20202020 20202020 20202020 20202020 ?
00000000140DA630: 20202020 20202020 20202020 20202020 ?
00000000140DA640: 20202020 20202020 20202020 20202020 ?
00000000140DA650: 20202020 20202020 20202020 20202020 ?
00000000140DA660: 20202020 20202020 20202020 20202020 ?
00000000140DA670: 20202020 20202020 20202020 20202020 ?
00000000140DA680: 20202020 20202020 20202020 20202020 ?
00000000140DA690: 20202020 20202020 20202020 20202020 ?
00000000140DA6A0: 20202020 20202020 20202020 20202020 ?
00000000140DA6B0: 20202020 20202020 20202020 20202020 ?
00000000140DA6C0: 20202020 20202020 20202020 20202020 ?
00000000140DA6D0: 20202020 20202020 20202020 20202020 ?
00000000140DA6E0: 20202020 20202020 20202020 20202020 ?
00000000140DA6F0: 20202020 20202020 20202020 20202020 ?
00000000140DA700: 20202020 20202020 20202020 20202020 ?
00000000140DA710: 20202020 20202020 20202020 20202020 ?
00000000140DA720: 20202020 20202020 20202020 20202020 ?
00000000140DA730: 20202020 20202020 20202020 20202020 ?
00000000140DA740: 20202020 20202020 20202020 20202020 ?
00000000140DA750: 20202020 20202020 20202020 20202020 ?
00000000140DA760: 20202020 20202020 20202020 20202020 ?
00000000140DA770: 20202020 20202020 20202020 20202020 ?
00000000140DA780: 20202020 20202020 20203000 08000400 ? 0.....
00000000140DA790: 00000300 00020005 02950334 20202020 ?.........?.4
00000000140DA7A0: 20202020 20202020 20202020 20202020 ?
00000000140DA7B0: 20202020 20202020 20202020 20202020 ?
00000000140DA7C0: 20202020 20202020 20202020 20202020 ?
00000000140DA7D0: 20202020 20202020 20202020 20202020 ?
00000000140DA7E0: 20202020 20202020 20202020 20202020 ?
00000000140DA7F0: 20202020 20202020 20202020 20202020 ?
00000000140DA800: 20202020 20202020 20202020 20202020 ?
00000000140DA810: 20202020 20202020 20202020 20202020 ?
00000000140DA820: 20202020 20202020 20202020 20202020 ?
00000000140DA830: 20202020 20202020 20202020 20202020 ?
00000000140DA840: 20202020 20202020 20202020 20202020 ?
00000000140DA850: 20202020 20202020 20202020 20202020 ?
00000000140DA860: 20202020 20202020 20202020 20202020 ?
00000000140DA870: 20202020 20202020 20202020 20202020 ?
00000000140DA880: 20202020 20202020 20202020 20202020 ?
00000000140DA890: 20202020 20202020 20202020 20202020 ?
00000000140DA8A0: 20202020 20202020 20202020 20202020 ?
00000000140DA8B0: 20202020 20202020 20202020 20202020 ?
00000000140DA8C0: 20202020 20202020 20202020 20202020 ?
00000000140DA8D0: 20202020 20202020 20202020 20202020 ?
00000000140DA8E0: 20202020 20202020 20202020 20202020 ?
00000000140DA8F0: 20202020 20202020 20202020 20202020 ?
00000000140DA900: 20202020 20202020 20202020 20202020 ?
00000000140DA910: 20202020 20202020 20202020 20202020 ?
00000000140DA920: 20202020 20202020 20202020 20202020 ?
00000000140DA930: 20202020 20202020 20202020 20202020 ?
00000000140DA940: 20202020 20202020 20202020 20202020 ?
00000000140DA950: 20202020 20202020 20202020 20202020 ?
00000000140DA960: 20202020 20202020 20202020 20202020 ?
00000000140DA970: 20202020 20202020 20202020 20202020 ?
00000000140DA980: 20202020 20202020 20202020 20202034 ? 4
00000000140DA990: 20202020 20202020 20202020 20202020 ?
00000000140DA9A0: 20202020 20202020 20202020 20202020 ?
00000000140DA9B0: 20202020 20202020 20202020 20202020 ?
00000000140DA9C0: 20202020 20202020 20202020 20202020 ?
00000000140DA9D0: 20202020 20202020 20202020 20202020 ?
00000000140DA9E0: 20202020 20202020 20202020 20202020 ?
00000000140DA9F0: 20202020 20202020 20202020 20202020 ?
00000000140DAA00: 20202020 20202020 20202020 20202020 ?
00000000140DAA10: 20202020 20202020 20202020 20202020 ?
00000000140DAA20: 20202020 20202020 20202020 20202020 ?
00000000140DAA30: 20202020 20202020 20202020 20202020 ?
00000000140DAA40: 20202020 20202020 20202020 20202020 ?
00000000140DAA50: 20202020 20202020 20202020 20202020 ?
00000000140DAA60: 20202020 20202020 20202020 20202020 ?
00000000140DAA70: 20202020 20202020 20202020 20202020 ?
00000000140DAA80: 20202020 20202020 20202020 20202020 ?
00000000140DAA90: 20202020 20202020 20202020 20202020 ?
00000000140DAAA0: 20202020 20202020 20202020 20202020 ?
00000000140DAAB0: 20202020 20202020 20202020 20202020 ?
00000000140DAAC0: 20202020 20202020 20202020 20202020 ?
00000000140DAAD0: 20202020 20202020 20202020 20202020 ?
00000000140DAAE0: 20202020 20202020 20202020 20202020 ?
00000000140DAAF0: 20202020 20202020 20202020 20202020 ?
00000000140DAB00: 20202020 20202020 20202020 20202020 ?
00000000140DAB10: 20202020 20202020 20202020 20202030 ? 0
00000000140DAB20: 00080005 00000003 00000200 05029503 ?..............?.
00000000140DAB30: 35202020 20202020 20202020 20202020 ?5
00000000140DAB40: 20202020 20202020 20202020 20202020 ?
00000000140DAB50: 20202020 20202020 20202020 20202020 ?
00000000140DAB60: 20202020 20202020 20202020 20202020 ?
00000000140DAB70: 20202020 20202020 20202020 20202020 ?
00000000140DAB80: 20202020 20202020 20202020 20202020 ?
00000000140DAB90: 20202020 20202020 20202020 20202020 ?
00000000140DABA0: 20202020 20202020 20202020 20202020 ?
00000000140DABB0: 20202020 20202020 20202020 20202020 ?
00000000140DABC0: 20202020 20202020 20202020 20202020 ?
00000000140DABD0: 20202020 20202020 20202020 20202020 ?
00000000140DABE0: 20202020 20202020 20202020 20202020 ?
00000000140DABF0: 20202020 20202020 20202020 20202020 ?
00000000140DAC00: 20202020 20202020 20202020 20202020 ?
00000000140DAC10: 20202020 20202020 20202020 20202020 ?
00000000140DAC20: 20202020 20202020 20202020 20202020 ?
00000000140DAC30: 20202020 20202020 20202020 20202020 ?
00000000140DAC40: 20202020 20202020 20202020 20202020 ?
00000000140DAC50: 20202020 20202020 20202020 20202020 ?
00000000140DAC60: 20202020 20202020 20202020 20202020 ?
00000000140DAC70: 20202020 20202020 20202020 20202020 ?
00000000140DAC80: 20202020 20202020 20202020 20202020 ?
00000000140DAC90: 20202020 20202020 20202020 20202020 ?
00000000140DACA0: 20202020 20202020 20202020 20202020 ?
00000000140DACB0: 20202020 20202020 20202020 20202020 ?
00000000140DACC0: 20202020 20202020 20202020 20202020 ?
00000000140DACD0: 20202020 20202020 20202020 20202020 ?
00000000140DACE0: 20202020 20202020 20202020 20202020 ?
00000000140DACF0: 20202020 20202020 20202020 20202020 ?
00000000140DAD00: 20202020 20202020 20202020 20202020 ?
00000000140DAD10: 20202020 20202020 20202020 20202020 ?
00000000140DAD20: 20202020 35202020 20202020 20202020 ? 5
00000000140DAD30: 20202020 20202020 20202020 20202020 ?
00000000140DAD40: 20202020 20202020 20202020 20202020 ?
00000000140DAD50: 20202020 20202020 20202020 20202020 ?
00000000140DAD60: 20202020 20202020 20202020 20202020 ?
00000000140DAD70: 20202020 20202020 20202020 20202020 ?
00000000140DAD80: 20202020 20202020 20202020 20202020 ?
00000000140DAD90: 20202020 20202020 20202020 20202020 ?
00000000140DADA0: 20202020 20202020 20202020 20202020 ?
00000000140DADB0: 20202020 20202020 20202020 20202020 ?
00000000140DADC0: 20202020 20202020 20202020 20202020 ?
00000000140DADD0: 20202020 20202020 20202020 20202020 ?
00000000140DADE0: 20202020 20202020 20202020 20202020 ?
00000000140DADF0: 20202020 20202020 20202020 20202020 ?
00000000140DAE00: 20202020 20202020 20202020 20202020 ?
00000000140DAE10: 20202020 20202020 20202020 20202020 ?
00000000140DAE20: 20202020 20202020 20202020 20202020 ?
00000000140DAE30: 20202020 20202020 20202020 20202020 ?
00000000140DAE40: 20202020 20202020 20202020 20202020 ?
00000000140DAE50: 20202020 20202020 20202020 20202020 ?
00000000140DAE60: 20202020 20202020 20202020 20202020 ?
00000000140DAE70: 20202020 20202020 20202020 20202020 ?
00000000140DAE80: 20202020 20202020 20202020 20202020 ?
00000000140DAE90: 20202020 20202020 20202020 20202020 ?
00000000140DAEA0: 20202020 20202020 20202020 20202020 ?
00000000140DAEB0: 20202020 30000800 06000000 03000002 ? 0...........
00000000140DAEC0: 00050295 03362020 20202020 20202020 ?...?.6
00000000140DAED0: 20202020 20202020 20202020 20202020 ?
00000000140DAEE0: 20202020 20202020 20202020 20202020 ?
00000000140DAEF0: 20202020 20202020 20202020 20202020 ?
00000000140DAF00: 20202020 20202020 20202020 20202020 ?
00000000140DAF10: 20202020 20202020 20202020 20202020 ?
00000000140DAF20: 20202020 20202020 20202020 20202020 ?
00000000140DAF30: 20202020 20202020 20202020 20202020 ?
00000000140DAF40: 20202020 20202020 20202020 20202020 ?
00000000140DAF50: 20202020 20202020 20202020 20202020 ?
00000000140DAF60: 20202020 20202020 20202020 20202020 ?
00000000140DAF70: 20202020 20202020 20202020 20202020 ?
00000000140DAF80: 20202020 20202020 20202020 20202020 ?
00000000140DAF90: 20202020 20202020 20202020 20202020 ?
00000000140DAFA0: 20202020 20202020 20202020 20202020 ?
00000000140DAFB0: 20202020 20202020 20202020 20202020 ?
00000000140DAFC0: 20202020 20202020 20202020 20202020 ?
00000000140DAFD0: 20202020 20202020 20202020 20202020 ?
00000000140DAFE0: 20202020 20202020 20202020 20202020 ?
00000000140DAFF0: 20202020 20202020 20202020 20202020 ?
00000000140DB000: 20202020 20202020 20202020 20202020 ?
00000000140DB010: 20202020 20202020 20202020 20202020 ?
00000000140DB020: 20202020 20202020 20202020 20202020 ?
00000000140DB030: 20202020 20202020 20202020 20202020 ?
00000000140DB040: 20202020 20202020 20202020 20202020 ?
00000000140DB050: 20202020 20202020 20202020 20202020 ?
00000000140DB060: 20202020 20202020 20202020 20202020 ?
00000000140DB070: 20202020 20202020 20202020 20202020 ?
00000000140DB080: 20202020 20202020 20202020 20202020 ?
00000000140DB090: 20202020 20202020 20202020 20202020 ?
00000000140DB0A0: 20202020 20202020 20202020 20202020 ?
00000000140DB0B0: 20202020 20202020 20362020 20202020 ? 6
00000000140DB0C0: 20202020 20202020 20202020 20202020 ?
00000000140DB0D0: 20202020 20202020 20202020 20202020 ?
00000000140DB0E0: 20202020 20202020 20202020 20202020 ?
00000000140DB0F0: 20202020 20202020 20202020 20202020 ?
00000000140DB100: 20202020 20202020 20202020 20202020 ?
00000000140DB110: 20202020 20202020 20202020 20202020 ?
00000000140DB120: 20202020 20202020 20202020 20202020 ?
00000000140DB130: 20202020 20202020 20202020 20202020 ?
00000000140DB140: 20202020 20202020 20202020 20202020 ?
00000000140DB150: 20202020 20202020 20202020 20202020 ?
00000000140DB160: 20202020 20202020 20202020 20202020 ?
00000000140DB170: 20202020 20202020 20202020 20202020 ?
00000000140DB180: 20202020 20202020 20202020 20202020 ?
00000000140DB190: 20202020 20202020 20202020 20202020 ?
00000000140DB1A0: 20202020 20202020 20202020 20202020 ?
00000000140DB1B0: 20202020 20202020 20202020 20202020 ?
00000000140DB1C0: 20202020 20202020 20202020 20202020 ?
00000000140DB1D0: 20202020 20202020 20202020 20202020 ?
00000000140DB1E0: 20202020 20202020 20202020 20202020 ?
00000000140DB1F0: 20202020 20202020 20202020 20202020 ?
00000000140DB200: 20202020 20202020 20202020 20202020 ?
00000000140DB210: 20202020 20202020 20202020 20202020 ?
00000000140DB220: 20202020 20202020 20202020 20202020 ?
00000000140DB230: 20202020 20202020 20202020 20202020 ?
00000000140DB240: 20202020 20202020 20300008 00070000 ? 0......
00000000140DB250: 00030000 02000502 95033720 20202020 ?........?.7
00000000140DB260: 20202020 20202020 20202020 20202020 ?
00000000140DB270: 20202020 20202020 20202020 20202020 ?
00000000140DB280: 20202020 20202020 20202020 20202020 ?
00000000140DB290: 20202020 20202020 20202020 20202020 ?
00000000140DB2A0: 20202020 20202020 20202020 20202020 ?
00000000140DB2B0: 20202020 20202020 20202020 20202020 ?
00000000140DB2C0: 20202020 20202020 20202020 20202020 ?
00000000140DB2D0: 20202020 20202020 20202020 20202020 ?
00000000140DB2E0: 20202020 20202020 20202020 20202020 ?
00000000140DB2F0: 20202020 20202020 20202020 20202020 ?
00000000140DB300: 20202020 20202020 20202020 20202020 ?
00000000140DB310: 20202020 20202020 20202020 20202020 ?
00000000140DB320: 20202020 20202020 20202020 20202020 ?
00000000140DB330: 20202020 20202020 20202020 20202020 ?
00000000140DB340: 20202020 20202020 20202020 20202020 ?
00000000140DB350: 20202020 20202020 20202020 20202020 ?
00000000140DB360: 20202020 20202020 20202020 20202020 ?
00000000140DB370: 20202020 20202020 20202020 20202020 ?
00000000140DB380: 20202020 20202020 20202020 20202020 ?
00000000140DB390: 20202020 20202020 20202020 20202020 ?
00000000140DB3A0: 20202020 20202020 20202020 20202020 ?
00000000140DB3B0: 20202020 20202020 20202020 20202020 ?
00000000140DB3C0: 20202020 20202020 20202020 20202020 ?
00000000140DB3D0: 20202020 20202020 20202020 20202020 ?
00000000140DB3E0: 20202020 20202020 20202020 20202020 ?
00000000140DB3F0: 20202020 20202020 20202020 20202020 ?
00000000140DB400: 20202020 20202020 20202020 20202020 ?
00000000140DB410: 20202020 20202020 20202020 20202020 ?
00000000140DB420: 20202020 20202020 20202020 20202020 ?
00000000140DB430: 20202020 20202020 20202020 20202020 ?
00000000140DB440: 20202020 20202020 20202020 20203720 ? 7
00000000140DB450: 20202020 20202020 20202020 20202020 ?
00000000140DB460: 20202020 20202020 20202020 20202020 ?
00000000140DB470: 20202020 20202020 20202020 20202020 ?
00000000140DB480: 20202020 20202020 20202020 20202020 ?
00000000140DB490: 20202020 20202020 20202020 20202020 ?
00000000140DB4A0: 20202020 20202020 20202020 20202020 ?
00000000140DB4B0: 20202020 20202020 20202020 20202020 ?
00000000140DB4C0: 20202020 20202020 20202020 20202020 ?
00000000140DB4D0: 20202020 20202020 20202020 20202020 ?
00000000140DB4E0: 20202020 20202020 20202020 20202020 ?
00000000140DB4F0: 20202020 20202020 20202020 20202020 ?
00000000140DB500: 20202020 20202020 20202020 20202020 ?
00000000140DB510: 20202020 20202020 20202020 20202020 ?
00000000140DB520: 20202020 20202020 20202020 20202020 ?
00000000140DB530: 20202020 20202020 20202020 20202020 ?
00000000140DB540: 20202020 20202020 20202020 20202020 ?
00000000140DB550: 20202020 20202020 20202020 20202020 ?
00000000140DB560: 20202020 20202020 20202020 20202020 ?
00000000140DB570: 20202020 20202020 20202020 20202020 ?
00000000140DB580: 20202020 20202020 20202020 20202020 ?
00000000140DB590: 20202020 20202020 20202020 20202020 ?
00000000140DB5A0: 20202020 20202020 20202020 20202020 ?
00000000140DB5B0: 20202020 20202020 20202020 20202020 ?
00000000140DB5C0: 20202020 20202020 20202020 20202020 ?
00000000140DB5D0: 20202020 20202020 20202020 20203000 ? 0.
00000000140DB5E0: 08000800 00000300 00020005 02950338 ?.............?.8
00000000140DB5F0: 20202020 20202020 20202020 20202020 ?
00000000140DB600: 20202020 20202020 20202020 20202020 ?
00000000140DB610: 20202020 20202020 20202020 20202020 ?
00000000140DB620: 20202020 20202020 20202020 20202020 ?
00000000140DB630: 20202020 20202020 20202020 20202020 ?
00000000140DB640: 20202020 20202020 20202020 20202020 ?
00000000140DB650: 20202020 20202020 20202020 20202020 ?
00000000140DB660: 20202020 20202020 20202020 20202020 ?
00000000140DB670: 20202020 20202020 20202020 20202020 ?
00000000140DB680: 20202020 20202020 20202020 20202020 ?
00000000140DB690: 20202020 20202020 20202020 20202020 ?
00000000140DB6A0: 20202020 20202020 20202020 20202020 ?
00000000140DB6B0: 20202020 20202020 20202020 20202020 ?
00000000140DB6C0: 20202020 20202020 20202020 20202020 ?
00000000140DB6D0: 20202020 20202020 20202020 20202020 ?
00000000140DB6E0: 20202020 20202020 20202020 20202020 ?
00000000140DB6F0: 20202020 20202020 20202020 20202020 ?
00000000140DB700: 20202020 20202020 20202020 20202020 ?
00000000140DB710: 20202020 20202020 20202020 20202020 ?
00000000140DB720: 20202020 20202020 20202020 20202020 ?
00000000140DB730: 20202020 20202020 20202020 20202020 ?
00000000140DB740: 20202020 20202020 20202020 20202020 ?
00000000140DB750: 20202020 20202020 20202020 20202020 ?
00000000140DB760: 20202020 20202020 20202020 20202020 ?
00000000140DB770: 20202020 20202020 20202020 20202020 ?
00000000140DB780: 20202020 20202020 20202020 20202020 ?
00000000140DB790: 20202020 20202020 20202020 20202020 ?
00000000140DB7A0: 20202020 20202020 20202020 20202020 ?
00000000140DB7B0: 20202020 20202020 20202020 20202020 ?
00000000140DB7C0: 20202020 20202020 20202020 20202020 ?
00000000140DB7D0: 20202020 20202020 20202020 20202020 ?
00000000140DB7E0: 20202038 20202020 20202020 20202020 ? 8
00000000140DB7F0: 20202020 20202020 20202020 20202020 ?
00000000140DB800: 20202020 20202020 20202020 20202020 ?
00000000140DB810: 20202020 20202020 20202020 20202020 ?
00000000140DB820: 20202020 20202020 20202020 20202020 ?
00000000140DB830: 20202020 20202020 20202020 20202020 ?
00000000140DB840: 20202020 20202020 20202020 20202020 ?
00000000140DB850: 20202020 20202020 20202020 20202020 ?
00000000140DB860: 20202020 20202020 20202020 20202020 ?
00000000140DB870: 20202020 20202020 20202020 20202020 ?
00000000140DB880: 20202020 20202020 20202020 20202020 ?
00000000140DB890: 20202020 20202020 20202020 20202020 ?
00000000140DB8A0: 20202020 20202020 20202020 20202020 ?
00000000140DB8B0: 20202020 20202020 20202020 20202020 ?
00000000140DB8C0: 20202020 20202020 20202020 20202020 ?
00000000140DB8D0: 20202020 20202020 20202020 20202020 ?
00000000140DB8E0: 20202020 20202020 20202020 20202020 ?
00000000140DB8F0: 20202020 20202020 20202020 20202020 ?
00000000140DB900: 20202020 20202020 20202020 20202020 ?
00000000140DB910: 20202020 20202020 20202020 20202020 ?
00000000140DB920: 20202020 20202020 20202020 20202020 ?
00000000140DB930: 20202020 20202020 20202020 20202020 ?
00000000140DB940: 20202020 20202020 20202020 20202020 ?
00000000140DB950: 20202020 20202020 20202020 20202020 ?
00000000140DB960: 20202020 20202020 20202020 20202020 ?
00000000140DB970: 20202030 00080003 00000003 00000200 ? 0............
00000000140DB980: 05029503 33202020 20202020 20202020 ?..?.3
00000000140DB990: 20202020 20202020 20202020 20202020 ?
00000000140DB9A0: 20202020 20202020 20202020 20202020 ?
00000000140DB9B0: 20202020 20202020 20202020 20202020 ?
00000000140DB9C0: 20202020 20202020 20202020 20202020 ?
00000000140DB9D0: 20202020 20202020 20202020 20202020 ?
00000000140DB9E0: 20202020 20202020 20202020 20202020 ?
00000000140DB9F0: 20202020 20202020 20202020 20202020 ?
00000000140DBA00: 20202020 20202020 20202020 20202020 ?
00000000140DBA10: 20202020 20202020 20202020 20202020 ?
00000000140DBA20: 20202020 20202020 20202020 20202020 ?
00000000140DBA30: 20202020 20202020 20202020 20202020 ?
00000000140DBA40: 20202020 20202020 20202020 20202020 ?
00000000140DBA50: 20202020 20202020 20202020 20202020 ?
00000000140DBA60: 20202020 20202020 20202020 20202020 ?
00000000140DBA70: 20202020 20202020 20202020 20202020 ?
00000000140DBA80: 20202020 20202020 20202020 20202020 ?
00000000140DBA90: 20202020 20202020 20202020 20202020 ?
00000000140DBAA0: 20202020 20202020 20202020 20202020 ?
00000000140DBAB0: 20202020 20202020 20202020 20202020 ?
00000000140DBAC0: 20202020 20202020 20202020 20202020 ?
00000000140DBAD0: 20202020 20202020 20202020 20202020 ?
00000000140DBAE0: 20202020 20202020 20202020 20202020 ?
00000000140DBAF0: 20202020 20202020 20202020 20202020 ?
00000000140DBB00: 20202020 20202020 20202020 20202020 ?
00000000140DBB10: 20202020 20202020 20202020 20202020 ?
00000000140DBB20: 20202020 20202020 20202020 20202020 ?
00000000140DBB30: 20202020 20202020 20202020 20202020 ?
00000000140DBB40: 20202020 20202020 20202020 20202020 ?
00000000140DBB50: 20202020 20202020 20202020 20202020 ?
00000000140DBB60: 20202020 20202020 20202020 20202020 ?
00000000140DBB70: 20202020 20202020 33202020 20202020 ? 3
00000000140DBB80: 20202020 20202020 20202020 20202020 ?
00000000140DBB90: 20202020 20202020 20202020 20202020 ?
00000000140DBBA0: 20202020 20202020 20202020 20202020 ?
00000000140DBBB0: 20202020 20202020 20202020 20202020 ?
00000000140DBBC0: 20202020 20202020 20202020 20202020 ?
00000000140DBBD0: 20202020 20202020 20202020 20202020 ?
00000000140DBBE0: 20202020 20202020 20202020 20202020 ?
00000000140DBBF0: 20202020 20202020 20202020 20202020 ?
00000000140DBC00: 20202020 20202020 20202020 20202020 ?
00000000140DBC10: 20202020 20202020 20202020 20202020 ?
00000000140DBC20: 20202020 20202020 20202020 20202020 ?
00000000140DBC30: 20202020 20202020 20202020 20202020 ?
00000000140DBC40: 20202020 20202020 20202020 20202020 ?
00000000140DBC50: 20202020 20202020 20202020 20202020 ?
00000000140DBC60: 20202020 20202020 20202020 20202020 ?
00000000140DBC70: 20202020 20202020 20202020 20202020 ?
00000000140DBC80: 20202020 20202020 20202020 20202020 ?
00000000140DBC90: 20202020 20202020 20202020 20202020 ?
00000000140DBCA0: 20202020 20202020 20202020 20202020 ?
00000000140DBCB0: 20202020 20202020 20202020 20202020 ?
00000000140DBCC0: 20202020 20202020 20202020 20202020 ?
00000000140DBCD0: 20202020 20202020 20202020 20202020 ?
00000000140DBCE0: 20202020 20202020 20202020 20202020 ?
00000000140DBCF0: 20202020 20202020 20202020 20202020 ?
00000000140DBD00: 20202020 20202020 00004100 73007900 ? ..A.s.y.
00000000140DBD10: 73007300 65007100 30002d00 49000000 ?s.s.e.q.0.-.I...
00000000140DBD20: 00000d00 00007f7f 00000008 00130000 ?................
00000000140DBD30: 00000001 00000008 00000000 00000000 ?................
00000000140DBD40: 00000000 00100000 80010047 0065006e ?...........G.e.n
00000000140DBD50: 00640064 006c0067 00730065 00710030 ?.d.d.l.g.s.e.q.0
00000000140DBD60: 002d0049 00000000 000e0000 007f7f00 ?.-.I............
00000000140DBD70: 00000800 13000000 00000100 00000800 ?................
00000000140DBD80: 00000000 00000000 00000000 10000080 ?................
00000000140DBD90: 01004b00 66006900 72007300 74006f00 ?..K.f.i.r.s.t.o.
00000000140DBDA0: 6f007200 64006500 72003000 2d004900 ?o.r.d.e.r.0.-.I.
00000000140DBDB0: 00000000 0f000000 7f7f0000 00080013 ?................
00000000140DBDC0: 00000000 00010000 00080000 00000000 ?................
00000000140DBDD0: 00000000 00000010 00008001 0049006c ?.............I.l
00000000140DBDE0: 00610073 0074006f 006f0072 00640065 ?.a.s.t.o.o.r.d.e
00000000140DBDF0: 00720030 002d0049 00000000 00100000 ?.r.0.-.I........
00000000140DBE00: 00383800 00000400 0a000000 00000100 ?.88.............
00000000140DBE10: 00000400 00000000 00000000 00000000 ?................
00000000140DBE20: 10000080 01004d00 6c006100 73007400 ?......M.l.a.s.t.
00000000140DBE30: 6f006f00 72006400 65007200 66007200 ?o.o.r.d.e.r.f.r.
00000000140DBE40: 30002d00 49000000 00001100 00003d3d ?0.-.I.........==
00000000140DBE50: 00000008 00170300 00000001 00000008 ?................
00000000140DBE60: 00000000 00000000 00000000 00100000 ?................
00000000140DBE70: 80010045 0064006c 00670074 0069006d ?...E.d.l.g.t.i.m
00000000140DBE80: 00650072 0030002d 00490000 00000012 ?.e.r.0.-.I......
00000000140DBE90: 0000003d 3d000000 08001703 00000000 ?...==...........
00000000140DBEA0: 01000000 08000000 00000000 00000000 ?................
00000000140DBEB0: 00001000 00800100 47006400 6c006700 ?........G.d.l.g.
00000000140DBEC0: 6f007000 65006e00 65006400 30002d00 ?o.p.e.n.e.d.0.-.
00000000140DBED0: 49000000 00001300 00003838 00000004 ?I.........88....
00000000140DBEE0: 000a0000 00000001 00000004 00000000 ?................
00000000140DBEF0: 00000000 00000000 00100000 80010043 ?...............C
00000000140DBF00: 00700072 0069006e 00630069 00640030 ?.p.r.i.n.c.i.d.0
00000000140DBF10: 002d0049 00000000 00140000 00a5a500 ?.-.I.........￥￥.
00000000140DBF20: 00003800 00000000 00000300 00003800 ?..8...........8.
00000000140DBF30: 00000000 00000000 00000000 10000080 ?................
00000000140DBF40: 01004700 6f007500 74007300 65007300 ?..G.o.u.t.s.e.s.
00000000140DBF50: 6b006500 79003000 2d004900 00000000 ?k.e.y.0.-.I.....
00000000140DBF60: 15000000 24240000 00100000 00000000 ?....$$..........
00000000140DBF70: 00010000 00100000 00000000 00000000 ?................
00000000140DBF80: 00000010 00008001 004b006f 00750074 ?.........K.o.u.t
00000000140DBF90: 00730065 0073006b 00650079 00690064 ?.s.e.s.k.e.y.i.d
00000000140DBFA0: 00000006 bf057c05 3705840b 390bf80a ?....?.|.7.?.9.?.
00000000140DBFB0: af0a6a0a 210ad609 8f094c09 0509c008 ?ˉ.j.!.? . L . à.
00000000140DBFC0: 73082a08 df079807 57071007 d1068c06 ?s.*.?...W...?.?.
00000000140DBFD0: 47060806 c7058405 43050205 bb047a04 ?G...?.?.C...?.z.
00000000140DBFE0: 3504f203 ab036a03 2903e402 a3025a02 ?5.ò.?.j.).?.￡.Z.
00000000140DBFF0: de154912 b40e1f0b 8a077319 f5036000 ?T.I.′...?.s.?.`.
OFFSET TABLE:
Row - Offset
7 (0x7) - 5598 (0x15de)
6 (0x6) - 4681 (0x1249)
5 (0x5) - 3764 (0xeb4)
4 (0x4) - 2847 (0xb1f)
3 (0x3) - 1930 (0x78a)
2 (0x2) - 6515 (0x1973)
1 (0x1) - 1013 (0x3f5)
0 (0x0) - 96 (0x60)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
so SQL Server did a huge work for the insert operation, it re-organize the page, then insert the data.
10) last time we inserted a same length row, what happen if I insert a small row? does the page re-organize happen again? let's test it
11) delete row(a=4)
delete from mytest where a=4
12) wait for a while, and make sure the ghost row is gone,
13) insert a small row with same cluster key(4)
insert into mytest(a,b,d)
values(4,CONVERT(char(50),4),CONVERT(char(50),4))
14) check the page
you can find the deleted row is not overwrote, there is no page re-organize, the new row is just inserted at the tail of the existing Data. that's because there is still enough free space at the page tail, while last time the free space at the page tail is not enough to save the new row, so the page need to be re-organized.
In a summary:
1. When delete row, the row is just marked a ghost row or delete row with flag. then ghost clean up thread will remove the ghost row by updating the offset table.
2. when insert row.
a)if there is enough free space in the page,
i) if there is enough free space from the end of last row to beginning of the offset table, insert it direct after the last row.
ii) or else, re-organize the page, then insert the new row after the last row.
b) if there is no enough free space in the page, then create a new page, move half data to the new page, then insert the new row in the old page.
15) what happen if we delete all the rows in the page?let's run
delete from mytest where a<9
when checking page, we can see all rows are marked as ghost row in the row header. however, in the offset table, ghost clean up thread doesn't remove all the slot, it still keep one row in the offset table.
Row - Offset
0 (0x0) - 5598 (0x15de)
let's check if the page has been removed from page chain.
select sys.fn_PhysLocFormatter(first_page) from sys.system_internals_allocation_units where allocation_unit_id=72057594039762944
output is (1:153:0)
so page 153 is still in the page chain. what happen if we delete other page? let's run
delete from mytest where a<=24 and a>=17
we deleted all 8 rows from page 157, check the page:
m_pageId = (1:157) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039762944
Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535 m_prevPage = (1:156) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 1 m_freeCnt = 7177
m_freeData = 7432 m_reservedCnt = 0 m_lsn = (48:473:12)
m_xactReserved = 0 m_xdesId = (0:4106) m_ghostRecCnt = 1
m_tornBits = 1214600220
Row - Offset
0 (0x0) - 6515 (0x1973)
Although the ghost cleanup thread still keeps 1 ghost row, the m_nextpage is 0 now. let's check its original previous page 156:
DBCC TRACEON(3604)
GO
dbcc page(test,1,156,2)
GO
output:
m_pageId = (1:156) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039762944
Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535 m_prevPage = (1:153) m_nextPage = (1:158)
pminlen = 8 m_slotCnt = 8 m_freeCnt = 744
m_freeData = 7432 m_reservedCnt = 0 m_lsn = (48:473:10)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = -677617447
the next page of page 156 is 158 now. let's check page 158:
output:
m_pageId = (1:158) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039762944
Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535 m_prevPage = (1:156) m_nextPage = (1:159)
pminlen = 8 m_slotCnt = 8 m_freeCnt = 744
m_freeData = 7432 m_reservedCnt = 0 m_lsn = (48:473:11)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 2036857631
the m_prevPage of page 158 is 156 now. so page 157 has been removed from page chain, if you check table space by
sp_spaceused 'mytest',@updateusage = N'TRUE';
you can see 8kb reduced. so 157 has been deallocated.
reference:
Inside the Storage Engine: Ghost cleanup in depth
http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx
No comments:
Post a Comment