Blog Post

Explore file physical structure - Insert Row - Part 2

,

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 = 3072KB , 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 = 10%)
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 <= 30)
begin
if ((@int % 2)=1)
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
GO
output

rowidPhysical RIDa_lengthb_lengthd_lengthabd
0x9900000001000000(1:153:0)4500400111
0x9900000001000100(1:153:1)4500400333
0x9900000001000200(1:153:2)4500400555
0x9900000001000300(1:153:3)4500400777
0x9900000001000400(1:153:4)4500400999
0x9900000001000500(1:153:5)4500400111111
0x9900000001000600(1:153:6)4500400131313
0x9900000001000700(1:153:7)4500400151515
0x9C00000001000000(1:156:0)4500400171717
0x9C00000001000100(1:156:1)4500400191919
0x9C00000001000200(1:156:2)4500400212121
0x9C00000001000300(1:156:3)4500400232323
0x9C00000001000400(1:156:4)4500400252525
0x9C00000001000500(1:156:5)4500400272727
0x9C00000001000600(1:156:6)4500400292929

so the 15 rows are saved in 2 pages: (1:153)  and (1:156)

5. insert a new row
use test
GO
insert into mytest(a,b,d)
values(2,CONVERT(char(500),2),CONVERT(char(400),2))
GO

6. select data again
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
GO
rowidPhysical RIDa_lengthb_lengthd_lengthabd
0x9900000001000000(1:153:0)450040011                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   1
0x9900000001000100(1:153:1)450040022                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   2
0x9900000001000200(1:153:2)450040033                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   3
0x9900000001000300(1:153:3)450040055                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   5
0x9900000001000400(1:153:4)450040077                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   7
0x9D00000001000000(1:157:0)450040099                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   9
0x9D00000001000100(1:157:1)45004001111                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  11
0x9D00000001000200(1:157:2)45004001313                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  13
0x9D00000001000300(1:157:3)45004001515                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  15
0x9C00000001000000(1:156:0)45004001717                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  17
0x9C00000001000100(1:156:1)45004001919                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  19
0x9C00000001000200(1:156:2)45004002121                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  21
0x9C00000001000300(1:156:3)45004002323                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  23
0x9C00000001000400(1:156:4)45004002525                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  25
0x9C00000001000500(1:156:5)45004002727                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  27
0x9C00000001000600(1:156:6)45004002929                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  29
since there is clustered index on this table, the row should be sorted in the table, the new row is inserted into page (1:153), however, this page is full now, what will happen? 
you can see a new page created, (1:157). 4 rows have been moved to new page . so the page split happen!
let's check page 153
7. check page 153
DBCC TRACEON(3604)
GO
dbcc page(test,1,153,2) --WITH TABLERESULTS
GO
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:153)
BUFFER:
BUF @0x0000000087FDB100
bpage = 0x00000000879D8000           bhash = 0x0000000000000000           bpageno = (1:153)
bdbid = 16                           breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 55386                        bstat = 0xc0010b
blog = 0x212159bb                    bnext = 0x0000000000000000           
PAGE HEADER:
Page @0x00000000879D8000
m_pageId = (1:153)                   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 = (0:0)                   m_nextPage = (1:157)
pminlen = 8                          m_slotCnt = 5                        m_freeCnt = 3501
m_freeData = 4681                    m_reservedCnt = 0                    m_lsn = (28:119:30)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       
Allocation Status
GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            
DATA:
Memory Dump @0x000000001774A000
000000001774A000:   01010400 00000001 00000000 00000800 ?................ 
000000001774A010:   9d000000 01000500 1c000000 ad0d4912 ?............-.I. 
000000001774A020:   99000000 01000000 1c000000 77000000 ?............w... 
000000001774A030:   1e000000 00000000 00000000 00000000 ?................ 
000000001774A040:   01000000 00000000 00000000 00000000 ?................ 
000000001774A050:   00000000 00000000 00000000 00000000 ?................ 
000000001774A060:   30000800 01000000 03000002 00050295 ?0..............? 
000000001774A070:   03312020 20202020 20202020 20202020 ?.1               
000000001774A080:   20202020 20202020 20202020 20202020 ?                 
000000001774A090:   20202020 20202020 20202020 20202020 ?                 
000000001774A0A0:   20202020 20202020 20202020 20202020 ?                 
000000001774A0B0:   20202020 20202020 20202020 20202020 ?                 
000000001774A0C0:   20202020 20202020 20202020 20202020 ?                 
000000001774A0D0:   20202020 20202020 20202020 20202020 ?                 
000000001774A0E0:   20202020 20202020 20202020 20202020 ?                 
000000001774A0F0:   20202020 20202020 20202020 20202020 ?                 
000000001774A100:   20202020 20202020 20202020 20202020 ?                 
000000001774A110:   20202020 20202020 20202020 20202020 ?                 
000000001774A120:   20202020 20202020 20202020 20202020 ?                 
000000001774A130:   20202020 20202020 20202020 20202020 ?                 
000000001774A140:   20202020 20202020 20202020 20202020 ?                 
000000001774A150:   20202020 20202020 20202020 20202020 ?                 
000000001774A160:   20202020 20202020 20202020 20202020 ?                 
000000001774A170:   20202020 20202020 20202020 20202020 ?                 
000000001774A180:   20202020 20202020 20202020 20202020 ?                 
000000001774A190:   20202020 20202020 20202020 20202020 ?                 
000000001774A1A0:   20202020 20202020 20202020 20202020 ?                 
000000001774A1B0:   20202020 20202020 20202020 20202020 ?                 
000000001774A1C0:   20202020 20202020 20202020 20202020 ?                 
000000001774A1D0:   20202020 20202020 20202020 20202020 ?                 
000000001774A1E0:   20202020 20202020 20202020 20202020 ?                 
000000001774A1F0:   20202020 20202020 20202020 20202020 ?                 
000000001774A200:   20202020 20202020 20202020 20202020 ?                 
000000001774A210:   20202020 20202020 20202020 20202020 ?                 
000000001774A220:   20202020 20202020 20202020 20202020 ?                 
000000001774A230:   20202020 20202020 20202020 20202020 ?                 
000000001774A240:   20202020 20202020 20202020 20202020 ?                 
000000001774A250:   20202020 20202020 20202020 20202020 ?                 
000000001774A260:   20202020 20312020 20202020 20202020 ?     1           
000000001774A270:   20202020 20202020 20202020 20202020 ?                 
000000001774A280:   20202020 20202020 20202020 20202020 ?                 
000000001774A290:   20202020 20202020 20202020 20202020 ?                 
000000001774A2A0:   20202020 20202020 20202020 20202020 ?                 
000000001774A2B0:   20202020 20202020 20202020 20202020 ?                 
000000001774A2C0:   20202020 20202020 20202020 20202020 ?                 
000000001774A2D0:   20202020 20202020 20202020 20202020 ?                 
000000001774A2E0:   20202020 20202020 20202020 20202020 ?                 
000000001774A2F0:   20202020 20202020 20202020 20202020 ?                 
000000001774A300:   20202020 20202020 20202020 20202020 ?                 
000000001774A310:   20202020 20202020 20202020 20202020 ?                 
000000001774A320:   20202020 20202020 20202020 20202020 ?                 
000000001774A330:   20202020 20202020 20202020 20202020 ?                 
000000001774A340:   20202020 20202020 20202020 20202020 ?                 
000000001774A350:   20202020 20202020 20202020 20202020 ?                 
000000001774A360:   20202020 20202020 20202020 20202020 ?                 
000000001774A370:   20202020 20202020 20202020 20202020 ?                 
000000001774A380:   20202020 20202020 20202020 20202020 ?                 
000000001774A390:   20202020 20202020 20202020 20202020 ?                 
000000001774A3A0:   20202020 20202020 20202020 20202020 ?                 
000000001774A3B0:   20202020 20202020 20202020 20202020 ?                 
000000001774A3C0:   20202020 20202020 20202020 20202020 ?                 
000000001774A3D0:   20202020 20202020 20202020 20202020 ?                 
000000001774A3E0:   20202020 20202020 20202020 20202020 ?                 
000000001774A3F0:   20202020 20300008 00030000 00030000 ?     0.......... 
000000001774A400:   02000502 95033320 20202020 20202020 ?....?.3          
000000001774A410:   20202020 20202020 20202020 20202020 ?                 
000000001774A420:   20202020 20202020 20202020 20202020 ?                 
000000001774A430:   20202020 20202020 20202020 20202020 ?                 
000000001774A440:   20202020 20202020 20202020 20202020 ?                 
000000001774A450:   20202020 20202020 20202020 20202020 ?                 
000000001774A460:   20202020 20202020 20202020 20202020 ?                 
000000001774A470:   20202020 20202020 20202020 20202020 ?                 
000000001774A480:   20202020 20202020 20202020 20202020 ?                 
000000001774A490:   20202020 20202020 20202020 20202020 ?                 
000000001774A4A0:   20202020 20202020 20202020 20202020 ?                 
000000001774A4B0:   20202020 20202020 20202020 20202020 ?                 
000000001774A4C0:   20202020 20202020 20202020 20202020 ?                 
000000001774A4D0:   20202020 20202020 20202020 20202020 ?                 
000000001774A4E0:   20202020 20202020 20202020 20202020 ?                 
000000001774A4F0:   20202020 20202020 20202020 20202020 ?                 
000000001774A500:   20202020 20202020 20202020 20202020 ?                 
000000001774A510:   20202020 20202020 20202020 20202020 ?                 
000000001774A520:   20202020 20202020 20202020 20202020 ?                 
000000001774A530:   20202020 20202020 20202020 20202020 ?                 
000000001774A540:   20202020 20202020 20202020 20202020 ?                 
000000001774A550:   20202020 20202020 20202020 20202020 ?                 
000000001774A560:   20202020 20202020 20202020 20202020 ?                 
000000001774A570:   20202020 20202020 20202020 20202020 ?                 
000000001774A580:   20202020 20202020 20202020 20202020 ?                 
000000001774A590:   20202020 20202020 20202020 20202020 ?                 
000000001774A5A0:   20202020 20202020 20202020 20202020 ?                 
000000001774A5B0:   20202020 20202020 20202020 20202020 ?                 
000000001774A5C0:   20202020 20202020 20202020 20202020 ?                 
000000001774A5D0:   20202020 20202020 20202020 20202020 ?                 
000000001774A5E0:   20202020 20202020 20202020 20202020 ?                 
000000001774A5F0:   20202020 20202020 20203320 20202020 ?          3      
000000001774A600:   20202020 20202020 20202020 20202020 ?                 
000000001774A610:   20202020 20202020 20202020 20202020 ?                 
000000001774A620:   20202020 20202020 20202020 20202020 ?                 
000000001774A630:   20202020 20202020 20202020 20202020 ?                 
000000001774A640:   20202020 20202020 20202020 20202020 ?                 
000000001774A650:   20202020 20202020 20202020 20202020 ?                 
000000001774A660:   20202020 20202020 20202020 20202020 ?                 
000000001774A670:   20202020 20202020 20202020 20202020 ?                 
000000001774A680:   20202020 20202020 20202020 20202020 ?                 
000000001774A690:   20202020 20202020 20202020 20202020 ?                 
000000001774A6A0:   20202020 20202020 20202020 20202020 ?                 
000000001774A6B0:   20202020 20202020 20202020 20202020 ?                 
000000001774A6C0:   20202020 20202020 20202020 20202020 ?                 
000000001774A6D0:   20202020 20202020 20202020 20202020 ?                 
000000001774A6E0:   20202020 20202020 20202020 20202020 ?                 
000000001774A6F0:   20202020 20202020 20202020 20202020 ?                 
000000001774A700:   20202020 20202020 20202020 20202020 ?                 
000000001774A710:   20202020 20202020 20202020 20202020 ?                 
000000001774A720:   20202020 20202020 20202020 20202020 ?                 
000000001774A730:   20202020 20202020 20202020 20202020 ?                 
000000001774A740:   20202020 20202020 20202020 20202020 ?                 
000000001774A750:   20202020 20202020 20202020 20202020 ?                 
000000001774A760:   20202020 20202020 20202020 20202020 ?                 
000000001774A770:   20202020 20202020 20202020 20202020 ?                 
000000001774A780:   20202020 20202020 20203000 08000500 ?          0..... 
000000001774A790:   00000300 00020005 02950335 20202020 ?.........?.5     
000000001774A7A0:   20202020 20202020 20202020 20202020 ?                 
000000001774A7B0:   20202020 20202020 20202020 20202020 ?                 
000000001774A7C0:   20202020 20202020 20202020 20202020 ?                 
000000001774A7D0:   20202020 20202020 20202020 20202020 ?                 
000000001774A7E0:   20202020 20202020 20202020 20202020 ?                 
000000001774A7F0:   20202020 20202020 20202020 20202020 ?                 
000000001774A800:   20202020 20202020 20202020 20202020 ?                 
000000001774A810:   20202020 20202020 20202020 20202020 ?                 
000000001774A820:   20202020 20202020 20202020 20202020 ?                 
000000001774A830:   20202020 20202020 20202020 20202020 ?                 
000000001774A840:   20202020 20202020 20202020 20202020 ?                 
000000001774A850:   20202020 20202020 20202020 20202020 ?                 
000000001774A860:   20202020 20202020 20202020 20202020 ?                 
000000001774A870:   20202020 20202020 20202020 20202020 ?                 
000000001774A880:   20202020 20202020 20202020 20202020 ?                 
000000001774A890:   20202020 20202020 20202020 20202020 ?                 
000000001774A8A0:   20202020 20202020 20202020 20202020 ?                 
000000001774A8B0:   20202020 20202020 20202020 20202020 ?                 
000000001774A8C0:   20202020 20202020 20202020 20202020 ?                 
000000001774A8D0:   20202020 20202020 20202020 20202020 ?                 
000000001774A8E0:   20202020 20202020 20202020 20202020 ?                 
000000001774A8F0:   20202020 20202020 20202020 20202020 ?                 
000000001774A900:   20202020 20202020 20202020 20202020 ?                 
000000001774A910:   20202020 20202020 20202020 20202020 ?                 
000000001774A920:   20202020 20202020 20202020 20202020 ?                 
000000001774A930:   20202020 20202020 20202020 20202020 ?                 
000000001774A940:   20202020 20202020 20202020 20202020 ?                 
000000001774A950:   20202020 20202020 20202020 20202020 ?                 
000000001774A960:   20202020 20202020 20202020 20202020 ?                 
000000001774A970:   20202020 20202020 20202020 20202020 ?                 
000000001774A980:   20202020 20202020 20202020 20202035 ?               5 
000000001774A990:   20202020 20202020 20202020 20202020 ?                 
000000001774A9A0:   20202020 20202020 20202020 20202020 ?                 
000000001774A9B0:   20202020 20202020 20202020 20202020 ?                 
000000001774A9C0:   20202020 20202020 20202020 20202020 ?                 
000000001774A9D0:   20202020 20202020 20202020 20202020 ?                 
000000001774A9E0:   20202020 20202020 20202020 20202020 ?                 
000000001774A9F0:   20202020 20202020 20202020 20202020 ?                 
000000001774AA00:   20202020 20202020 20202020 20202020 ?                 
000000001774AA10:   20202020 20202020 20202020 20202020 ?                 
000000001774AA20:   20202020 20202020 20202020 20202020 ?                 
000000001774AA30:   20202020 20202020 20202020 20202020 ?                 
000000001774AA40:   20202020 20202020 20202020 20202020 ?                 
000000001774AA50:   20202020 20202020 20202020 20202020 ?                 
000000001774AA60:   20202020 20202020 20202020 20202020 ?                 
000000001774AA70:   20202020 20202020 20202020 20202020 ?                 
000000001774AA80:   20202020 20202020 20202020 20202020 ?                 
000000001774AA90:   20202020 20202020 20202020 20202020 ?                 
000000001774AAA0:   20202020 20202020 20202020 20202020 ?                 
000000001774AAB0:   20202020 20202020 20202020 20202020 ?                 
000000001774AAC0:   20202020 20202020 20202020 20202020 ?                 
000000001774AAD0:   20202020 20202020 20202020 20202020 ?                 
000000001774AAE0:   20202020 20202020 20202020 20202020 ?                 
000000001774AAF0:   20202020 20202020 20202020 20202020 ?                 
000000001774AB00:   20202020 20202020 20202020 20202020 ?                 
000000001774AB10:   20202020 20202020 20202020 20202030 ?               0 
000000001774AB20:   00080007 00000003 00000200 05029503 ?..............?. 
000000001774AB30:   37202020 20202020 20202020 20202020 ?7                
000000001774AB40:   20202020 20202020 20202020 20202020 ?                 
000000001774AB50:   20202020 20202020 20202020 20202020 ?                 
000000001774AB60:   20202020 20202020 20202020 20202020 ?                 
000000001774AB70:   20202020 20202020 20202020 20202020 ?                 
000000001774AB80:   20202020 20202020 20202020 20202020 ?                 
000000001774AB90:   20202020 20202020 20202020 20202020 ?                 
000000001774ABA0:   20202020 20202020 20202020 20202020 ?                 
000000001774ABB0:   20202020 20202020 20202020 20202020 ?                 
000000001774ABC0:   20202020 20202020 20202020 20202020 ?                 
000000001774ABD0:   20202020 20202020 20202020 20202020 ?                 
000000001774ABE0:   20202020 20202020 20202020 20202020 ?                 
000000001774ABF0:   20202020 20202020 20202020 20202020 ?                 
000000001774AC00:   20202020 20202020 20202020 20202020 ?                 
000000001774AC10:   20202020 20202020 20202020 20202020 ?                 
000000001774AC20:   20202020 20202020 20202020 20202020 ?                 
000000001774AC30:   20202020 20202020 20202020 20202020 ?                 
000000001774AC40:   20202020 20202020 20202020 20202020 ?                 
000000001774AC50:   20202020 20202020 20202020 20202020 ?                 
000000001774AC60:   20202020 20202020 20202020 20202020 ?                 
000000001774AC70:   20202020 20202020 20202020 20202020 ?                 
000000001774AC80:   20202020 20202020 20202020 20202020 ?                 
000000001774AC90:   20202020 20202020 20202020 20202020 ?                 
000000001774ACA0:   20202020 20202020 20202020 20202020 ?                 
000000001774ACB0:   20202020 20202020 20202020 20202020 ?                 
000000001774ACC0:   20202020 20202020 20202020 20202020 ?                 
000000001774ACD0:   20202020 20202020 20202020 20202020 ?                 
000000001774ACE0:   20202020 20202020 20202020 20202020 ?                 
000000001774ACF0:   20202020 20202020 20202020 20202020 ?                 
000000001774AD00:   20202020 20202020 20202020 20202020 ?                 
000000001774AD10:   20202020 20202020 20202020 20202020 ?                 
000000001774AD20:   20202020 37202020 20202020 20202020 ?    7            
000000001774AD30:   20202020 20202020 20202020 20202020 ?                 
000000001774AD40:   20202020 20202020 20202020 20202020 ?                 
000000001774AD50:   20202020 20202020 20202020 20202020 ?                 
000000001774AD60:   20202020 20202020 20202020 20202020 ?                 
000000001774AD70:   20202020 20202020 20202020 20202020 ?                 
000000001774AD80:   20202020 20202020 20202020 20202020 ?                 
000000001774AD90:   20202020 20202020 20202020 20202020 ?                 
000000001774ADA0:   20202020 20202020 20202020 20202020 ?                 
000000001774ADB0:   20202020 20202020 20202020 20202020 ?                 
000000001774ADC0:   20202020 20202020 20202020 20202020 ?                 
000000001774ADD0:   20202020 20202020 20202020 20202020 ?                 
000000001774ADE0:   20202020 20202020 20202020 20202020 ?                 
000000001774ADF0:   20202020 20202020 20202020 20202020 ?                 
000000001774AE00:   20202020 20202020 20202020 20202020 ?                 
000000001774AE10:   20202020 20202020 20202020 20202020 ?                 
000000001774AE20:   20202020 20202020 20202020 20202020 ?                 
000000001774AE30:   20202020 20202020 20202020 20202020 ?                 
000000001774AE40:   20202020 20202020 20202020 20202020 ?                 
000000001774AE50:   20202020 20202020 20202020 20202020 ?                 
000000001774AE60:   20202020 20202020 20202020 20202020 ?                 
000000001774AE70:   20202020 20202020 20202020 20202020 ?                 
000000001774AE80:   20202020 20202020 20202020 20202020 ?                 
000000001774AE90:   20202020 20202020 20202020 20202020 ?                 
000000001774AEA0:   20202020 20202020 20202020 20202020 ?                 
000000001774AEB0:   20202020 30000800 02000000 03000002 ?    0........... 
000000001774AEC0:   00050295 03322020 20202020 20202020 ?...?.2           
#here the new row. originally, there are 8 rows in the table. when id=2 row need to be inserted, 
sql create a new page 157, move the last 4 rows to new page, then save the new row at the tail of the existing 4 rows.
000000001774AED0:   20202020 20202020 20202020 20202020 ?                 
000000001774AEE0:   20202020 20202020 20202020 20202020 ?                 
000000001774AEF0:   20202020 20202020 20202020 20202020 ?                 
000000001774AF00:   20202020 20202020 20202020 20202020 ?                 
000000001774AF10:   20202020 20202020 20202020 20202020 ?                 
000000001774AF20:   20202020 20202020 20202020 20202020 ?                 
000000001774AF30:   20202020 20202020 20202020 20202020 ?                 
000000001774AF40:   20202020 20202020 20202020 20202020 ?                 
000000001774AF50:   20202020 20202020 20202020 20202020 ?                 
000000001774AF60:   20202020 20202020 20202020 20202020 ?                 
000000001774AF70:   20202020 20202020 20202020 20202020 ?                 
000000001774AF80:   20202020 20202020 20202020 20202020 ?                 
000000001774AF90:   20202020 20202020 20202020 20202020 ?                 
000000001774AFA0:   20202020 20202020 20202020 20202020 ?                 
000000001774AFB0:   20202020 20202020 20202020 20202020 ?                 
000000001774AFC0:   20202020 20202020 20202020 20202020 ?                 
000000001774AFD0:   20202020 20202020 20202020 20202020 ?                 
000000001774AFE0:   20202020 20202020 20202020 20202020 ?                 
000000001774AFF0:   20202020 20202020 20202020 20202020 ?                 
000000001774B000:   20202020 20202020 20202020 20202020 ?                 
000000001774B010:   20202020 20202020 20202020 20202020 ?                 
000000001774B020:   20202020 20202020 20202020 20202020 ?                 
000000001774B030:   20202020 20202020 20202020 20202020 ?                 
000000001774B040:   20202020 20202020 20202020 20202020 ?                 
000000001774B050:   20202020 20202020 20202020 20202020 ?                 
000000001774B060:   20202020 20202020 20202020 20202020 ?                 
000000001774B070:   20202020 20202020 20202020 20202020 ?                 
000000001774B080:   20202020 20202020 20202020 20202020 ?                 
000000001774B090:   20202020 20202020 20202020 20202020 ?                 
000000001774B0A0:   20202020 20202020 20202020 20202020 ?                 
000000001774B0B0:   20202020 20202020 20322020 20202020 ?         2       
000000001774B0C0:   20202020 20202020 20202020 20202020 ?                 
000000001774B0D0:   20202020 20202020 20202020 20202020 ?                 
000000001774B0E0:   20202020 20202020 20202020 20202020 ?                 
000000001774B0F0:   20202020 20202020 20202020 20202020 ?                 
000000001774B100:   20202020 20202020 20202020 20202020 ?                 
000000001774B110:   20202020 20202020 20202020 20202020 ?                 
000000001774B120:   20202020 20202020 20202020 20202020 ?                 
000000001774B130:   20202020 20202020 20202020 20202020 ?                 
000000001774B140:   20202020 20202020 20202020 20202020 ?                 
000000001774B150:   20202020 20202020 20202020 20202020 ?                 
000000001774B160:   20202020 20202020 20202020 20202020 ?                 
000000001774B170:   20202020 20202020 20202020 20202020 ?                 
000000001774B180:   20202020 20202020 20202020 20202020 ?                 
000000001774B190:   20202020 20202020 20202020 20202020 ?                 
000000001774B1A0:   20202020 20202020 20202020 20202020 ?                 
000000001774B1B0:   20202020 20202020 20202020 20202020 ?                 
000000001774B1C0:   20202020 20202020 20202020 20202020 ?                 
000000001774B1D0:   20202020 20202020 20202020 20202020 ?                 
000000001774B1E0:   20202020 20202020 20202020 20202020 ?                 
000000001774B1F0:   20202020 20202020 20202020 20202020 ?                 
000000001774B200:   20202020 20202020 20202020 20202020 ?                 
000000001774B210:   20202020 20202020 20202020 20202020 ?                 
000000001774B220:   20202020 20202020 20202020 20202020 ?                 
000000001774B230:   20202020 20202020 20202020 20202020 ?                 
000000001774B240:   20202020 20202020 20000008 000b0000 ?         ....... 
000000001774B250:   00030000 02000502 95033131 20202020 ?........?.11     
#here we can still see the old data in the page,  the old data will not be zero, the only change is in the offset table at the page tail
000000001774B260:   20202020 20202020 20202020 20202020 ?                 
000000001774B270:   20202020 20202020 20202020 20202020 ?                 
000000001774B280:   20202020 20202020 20202020 20202020 ?                 
000000001774B290:   20202020 20202020 20202020 20202020 ?                 
000000001774B2A0:   20202020 20202020 20202020 20202020 ?                 
000000001774B2B0:   20202020 20202020 20202020 20202020 ?                 
000000001774B2C0:   20202020 20202020 20202020 20202020 ?                 
000000001774B2D0:   20202020 20202020 20202020 20202020 ?                 
000000001774B2E0:   20202020 20202020 20202020 20202020 ?                 
000000001774B2F0:   20202020 20202020 20202020 20202020 ?                 
000000001774B300:   20202020 20202020 20202020 20202020 ?                 
000000001774B310:   20202020 20202020 20202020 20202020 ?                 
000000001774B320:   20202020 20202020 20202020 20202020 ?                 
000000001774B330:   20202020 20202020 20202020 20202020 ?                 
000000001774B340:   20202020 20202020 20202020 20202020 ?                 
000000001774B350:   20202020 20202020 20202020 20202020 ?                 
000000001774B360:   20202020 20202020 20202020 20202020 ?                 
000000001774B370:   20202020 20202020 20202020 20202020 ?                 
000000001774B380:   20202020 20202020 20202020 20202020 ?                 
000000001774B390:   20202020 20202020 20202020 20202020 ?                 
000000001774B3A0:   20202020 20202020 20202020 20202020 ?                 
000000001774B3B0:   20202020 20202020 20202020 20202020 ?                 
000000001774B3C0:   20202020 20202020 20202020 20202020 ?                 
000000001774B3D0:   20202020 20202020 20202020 20202020 ?                 
000000001774B3E0:   20202020 20202020 20202020 20202020 ?                 
000000001774B3F0:   20202020 20202020 20202020 20202020 ?                 
000000001774B400:   20202020 20202020 20202020 20202020 ?                 
000000001774B410:   20202020 20202020 20202020 20202020 ?                 
000000001774B420:   20202020 20202020 20202020 20202020 ?                 
000000001774B430:   20202020 20202020 20202020 20202020 ?                 
000000001774B440:   20202020 20202020 20202020 20203131 ?              11 
000000001774B450:   20202020 20202020 20202020 20202020 ?                 
000000001774B460:   20202020 20202020 20202020 20202020 ?                 
000000001774B470:   20202020 20202020 20202020 20202020 ?                 
000000001774B480:   20202020 20202020 20202020 20202020 ?                 
000000001774B490:   20202020 20202020 20202020 20202020 ?                 
000000001774B4A0:   20202020 20202020 20202020 20202020 ?                 
000000001774B4B0:   20202020 20202020 20202020 20202020 ?                 
000000001774B4C0:   20202020 20202020 20202020 20202020 ?                 
000000001774B4D0:   20202020 20202020 20202020 20202020 ?                 
000000001774B4E0:   20202020 20202020 20202020 20202020 ?                 
000000001774B4F0:   20202020 20202020 20202020 20202020 ?                 
000000001774B500:   20202020 20202020 20202020 20202020 ?                 
000000001774B510:   20202020 20202020 20202020 20202020 ?                 
000000001774B520:   20202020 20202020 20202020 20202020 ?                 
000000001774B530:   20202020 20202020 20202020 20202020 ?                 
000000001774B540:   20202020 20202020 20202020 20202020 ?                 
000000001774B550:   20202020 20202020 20202020 20202020 ?                 
000000001774B560:   20202020 20202020 20202020 20202020 ?                 
000000001774B570:   20202020 20202020 20202020 20202020 ?                 
000000001774B580:   20202020 20202020 20202020 20202020 ?                 
000000001774B590:   20202020 20202020 20202020 20202020 ?                 
000000001774B5A0:   20202020 20202020 20202020 20202020 ?                 
000000001774B5B0:   20202020 20202020 20202020 20202020 ?                 
000000001774B5C0:   20202020 20202020 20202020 20202020 ?                 
000000001774B5D0:   20202020 20202020 20202020 20203000 ?              0. 
000000001774B5E0:   08000d00 00000300 00020005 02950331 ?.............?.1 
000000001774B5F0:   33202020 20202020 20202020 20202020 ?3                
000000001774B600:   20202020 20202020 20202020 20202020 ?                 
000000001774B610:   20202020 20202020 20202020 20202020 ?                 
000000001774B620:   20202020 20202020 20202020 20202020 ?                 
000000001774B630:   20202020 20202020 20202020 20202020 ?                 
000000001774B640:   20202020 20202020 20202020 20202020 ?                 
000000001774B650:   20202020 20202020 20202020 20202020 ?                 
000000001774B660:   20202020 20202020 20202020 20202020 ?                 
000000001774B670:   20202020 20202020 20202020 20202020 ?                 
000000001774B680:   20202020 20202020 20202020 20202020 ?                 
000000001774B690:   20202020 20202020 20202020 20202020 ?                 
000000001774B6A0:   20202020 20202020 20202020 20202020 ?                 
000000001774B6B0:   20202020 20202020 20202020 20202020 ?                 
000000001774B6C0:   20202020 20202020 20202020 20202020 ?                 
000000001774B6D0:   20202020 20202020 20202020 20202020 ?                 
000000001774B6E0:   20202020 20202020 20202020 20202020 ?                 
000000001774B6F0:   20202020 20202020 20202020 20202020 ?                 
000000001774B700:   20202020 20202020 20202020 20202020 ?                 
000000001774B710:   20202020 20202020 20202020 20202020 ?                 
000000001774B720:   20202020 20202020 20202020 20202020 ?                 
000000001774B730:   20202020 20202020 20202020 20202020 ?                 
000000001774B740:   20202020 20202020 20202020 20202020 ?                 
000000001774B750:   20202020 20202020 20202020 20202020 ?                 
000000001774B760:   20202020 20202020 20202020 20202020 ?                 
000000001774B770:   20202020 20202020 20202020 20202020 ?                 
000000001774B780:   20202020 20202020 20202020 20202020 ?                 
000000001774B790:   20202020 20202020 20202020 20202020 ?                 
000000001774B7A0:   20202020 20202020 20202020 20202020 ?                 
000000001774B7B0:   20202020 20202020 20202020 20202020 ?                 
000000001774B7C0:   20202020 20202020 20202020 20202020 ?                 
000000001774B7D0:   20202020 20202020 20202020 20202020 ?                 
000000001774B7E0:   20202031 33202020 20202020 20202020 ?   13            
000000001774B7F0:   20202020 20202020 20202020 20202020 ?                 
000000001774B800:   20202020 20202020 20202020 20202020 ?                 
000000001774B810:   20202020 20202020 20202020 20202020 ?                 
000000001774B820:   20202020 20202020 20202020 20202020 ?                 
000000001774B830:   20202020 20202020 20202020 20202020 ?                 
000000001774B840:   20202020 20202020 20202020 20202020 ?                 
000000001774B850:   20202020 20202020 20202020 20202020 ?                 
000000001774B860:   20202020 20202020 20202020 20202020 ?                 
000000001774B870:   20202020 20202020 20202020 20202020 ?                 
000000001774B880:   20202020 20202020 20202020 20202020 ?                 
000000001774B890:   20202020 20202020 20202020 20202020 ?                 
000000001774B8A0:   20202020 20202020 20202020 20202020 ?                 
000000001774B8B0:   20202020 20202020 20202020 20202020 ?                 
000000001774B8C0:   20202020 20202020 20202020 20202020 ?                 
000000001774B8D0:   20202020 20202020 20202020 20202020 ?                 
000000001774B8E0:   20202020 20202020 20202020 20202020 ?                 
000000001774B8F0:   20202020 20202020 20202020 20202020 ?                 
000000001774B900:   20202020 20202020 20202020 20202020 ?                 
000000001774B910:   20202020 20202020 20202020 20202020 ?                 
000000001774B920:   20202020 20202020 20202020 20202020 ?                 
000000001774B930:   20202020 20202020 20202020 20202020 ?                 
000000001774B940:   20202020 20202020 20202020 20202020 ?                 
000000001774B950:   20202020 20202020 20202020 20202020 ?                 
000000001774B960:   20202020 20202020 20202020 20202020 ?                 
000000001774B970:   20202030 0008000f 00000003 00000200 ?   0............ 
000000001774B980:   05029503 31352020 20202020 20202020 ?..?.15           
000000001774B990:   20202020 20202020 20202020 20202020 ?                 
000000001774B9A0:   20202020 20202020 20202020 20202020 ?                 
000000001774B9B0:   20202020 20202020 20202020 20202020 ?                 
000000001774B9C0:   20202020 20202020 20202020 20202020 ?                 
000000001774B9D0:   20202020 20202020 20202020 20202020 ?                 
000000001774B9E0:   20202020 20202020 20202020 20202020 ?                 
000000001774B9F0:   20202020 20202020 20202020 20202020 ?                 
000000001774BA00:   20202020 20202020 20202020 20202020 ?                 
000000001774BA10:   20202020 20202020 20202020 20202020 ?                 
000000001774BA20:   20202020 20202020 20202020 20202020 ?                 
000000001774BA30:   20202020 20202020 20202020 20202020 ?                 
000000001774BA40:   20202020 20202020 20202020 20202020 ?                 
000000001774BA50:   20202020 20202020 20202020 20202020 ?                 
000000001774BA60:   20202020 20202020 20202020 20202020 ?                 
000000001774BA70:   20202020 20202020 20202020 20202020 ?                 
000000001774BA80:   20202020 20202020 20202020 20202020 ?                 
000000001774BA90:   20202020 20202020 20202020 20202020 ?                 
000000001774BAA0:   20202020 20202020 20202020 20202020 ?                 
000000001774BAB0:   20202020 20202020 20202020 20202020 ?                 
000000001774BAC0:   20202020 20202020 20202020 20202020 ?                 
000000001774BAD0:   20202020 20202020 20202020 20202020 ?                 
000000001774BAE0:   20202020 20202020 20202020 20202020 ?                 
000000001774BAF0:   20202020 20202020 20202020 20202020 ?                 
000000001774BB00:   20202020 20202020 20202020 20202020 ?                 
000000001774BB10:   20202020 20202020 20202020 20202020 ?                 
000000001774BB20:   20202020 20202020 20202020 20202020 ?                 
000000001774BB30:   20202020 20202020 20202020 20202020 ?                 
000000001774BB40:   20202020 20202020 20202020 20202020 ?                 
000000001774BB50:   20202020 20202020 20202020 20202020 ?                 
000000001774BB60:   20202020 20202020 20202020 20202020 ?                 
000000001774BB70:   20202020 20202020 31352020 20202020 ?        15       
000000001774BB80:   20202020 20202020 20202020 20202020 ?                 
000000001774BB90:   20202020 20202020 20202020 20202020 ?                 
000000001774BBA0:   20202020 20202020 20202020 20202020 ?                 
000000001774BBB0:   20202020 20202020 20202020 20202020 ?                 
000000001774BBC0:   20202020 20202020 20202020 20202020 ?                 
000000001774BBD0:   20202020 20202020 20202020 20202020 ?                 
000000001774BBE0:   20202020 20202020 20202020 20202020 ?                 
000000001774BBF0:   20202020 20202020 20202020 20202020 ?                 
000000001774BC00:   20202020 20202020 20202020 20202020 ?                 
000000001774BC10:   20202020 20202020 20202020 20202020 ?                 
000000001774BC20:   20202020 20202020 20202020 20202020 ?                 
000000001774BC30:   20202020 20202020 20202020 20202020 ?                 
000000001774BC40:   20202020 20202020 20202020 20202020 ?                 
000000001774BC50:   20202020 20202020 20202020 20202020 ?                 
000000001774BC60:   20202020 20202020 20202020 20202020 ?                 
000000001774BC70:   20202020 20202020 20202020 20202020 ?                 
000000001774BC80:   20202020 20202020 20202020 20202020 ?                 
000000001774BC90:   20202020 20202020 20202020 20202020 ?                 
000000001774BCA0:   20202020 20202020 20202020 20202020 ?                 
000000001774BCB0:   20202020 20202020 20202020 20202020 ?                 
000000001774BCC0:   20202020 20202020 20202020 20202020 ?                 
000000001774BCD0:   20202020 20202020 20202020 20202020 ?                 
000000001774BCE0:   20202020 20202020 20202020 20202020 ?                 
000000001774BCF0:   20202020 20202020 20202020 20202020 ?                 
000000001774BD00:   20202020 20202020 00004100 73007900 ?        ..A.s.y. 
000000001774BD10:   73007300 65007100 30002d00 49000000 ?s.s.e.q.0.-.I... 
000000001774BD20:   00000d00 00007f7f 00000008 00130000 ?................ 
000000001774BD30:   00000001 00000008 00000000 00000000 ?................ 
000000001774BD40:   00000000 00100000 80010047 0065006e ?...........G.e.n 
000000001774BD50:   00640064 006c0067 00730065 00710030 ?.d.d.l.g.s.e.q.0 
000000001774BD60:   002d0049 00000000 000e0000 007f7f00 ?.-.I............ 
000000001774BD70:   00000800 13000000 00000100 00000800 ?................ 
000000001774BD80:   00000000 00000000 00000000 10000080 ?................ 
000000001774BD90:   01004b00 66006900 72007300 74006f00 ?..K.f.i.r.s.t.o. 
000000001774BDA0:   6f007200 64006500 72003000 2d004900 ?o.r.d.e.r.0.-.I. 
000000001774BDB0:   00000000 0f000000 7f7f0000 00080013 ?................ 
000000001774BDC0:   00000000 00010000 00080000 00000000 ?................ 
000000001774BDD0:   00000000 00000010 00008001 0049006c ?.............I.l 
000000001774BDE0:   00610073 0074006f 006f0072 00640065 ?.a.s.t.o.o.r.d.e 
000000001774BDF0:   00720030 002d0049 00000000 00100000 ?.r.0.-.I........ 
000000001774BE00:   00383800 00000400 0a000000 00000100 ?.88............. 
000000001774BE10:   00000400 00000000 00000000 00000000 ?................ 
000000001774BE20:   10000080 01004d00 6c006100 73007400 ?......M.l.a.s.t. 
000000001774BE30:   6f006f00 72006400 65007200 66007200 ?o.o.r.d.e.r.f.r. 
000000001774BE40:   30002d00 49000000 00001100 00003d3d ?0.-.I.........== 
000000001774BE50:   00000008 00170300 00000001 00000008 ?................ 
000000001774BE60:   00000000 00000000 00000000 00100000 ?................ 
000000001774BE70:   80010045 0064006c 00670074 0069006d ?...E.d.l.g.t.i.m 
000000001774BE80:   00650072 0030002d 00490000 00000012 ?.e.r.0.-.I...... 
000000001774BE90:   0000003d 3d000000 08001703 00000000 ?...==........... 
000000001774BEA0:   01000000 08000000 00000000 00000000 ?................ 
000000001774BEB0:   00001000 00800100 47006400 6c006700 ?........G.d.l.g. 
000000001774BEC0:   6f007000 65006e00 65006400 30002d00 ?o.p.e.n.e.d.0.-. 
000000001774BED0:   49000000 00001300 00003838 00000004 ?I.........88.... 
000000001774BEE0:   000a0000 00000001 00000004 00000000 ?................ 
000000001774BEF0:   00000000 00000000 00100000 80010043 ?...............C 
000000001774BF00:   00700072 0069006e 00630069 00640030 ?.p.r.i.n.c.i.d.0 
000000001774BF10:   002d0049 00000000 00140000 00a5a500 ?.-.I.........??. 
000000001774BF20:   00003800 00000000 00000300 00003800 ?..8...........8. 
000000001774BF30:   00000000 00000000 00000000 10000080 ?................ 
000000001774BF40:   01004700 6f007500 74007300 65007300 ?..G.o.u.t.s.e.s. 
000000001774BF50:   6b006500 79003000 2d004900 00000000 ?k.e.y.0.-.I..... 
000000001774BF60:   15000000 24240000 00100000 00000000 ?....$$.......... 
000000001774BF70:   00010000 00100000 00000000 00000000 ?................ 
000000001774BF80:   00000010 00008001 004b006f 00750074 ?.........K.o.u.t 
000000001774BF90:   00730065 0073006b 00650079 00690064 ?.s.e.s.k.e.y.i.d 
000000001774BFA0:   00000006 bf057c05 3705840b 390bf80a ?....?.|.7.?.9.?. 
000000001774BFB0:   af0a6a0a 210ad609 8f094c09 0509c008 ?¯.j.!.? . L . à. 
000000001774BFC0:   73082a08 df079807 57071007 d1068c06 ?s.*.?...W...?.?. 
000000001774BFD0:   47060806 c7058405 43050205 bb047a04 ?G...?.?.C...?.z. 
000000001774BFE0:   3504f203 ab036a03 2903e402 a3025a02 ?5.ò.?.j.).?.?.Z. 
000000001774BFF0:   7319de15 49121f0b 8a07f503 b40e6000 ?s.T.I...?.?.'.`. 
OFFSET TABLE:
Row - Offset                         
4 (0x4) - 2847 (0xb1f)               
3 (0x3) - 1930 (0x78a)               
2 (0x2) - 1013 (0x3f5)               
1 (0x1) - 3764 (0xeb4)               
0 (0x0) - 96 (0x60)                  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
8. so the current page chain is 153->157->156.  let's check index status
USE master;
GO
SELECT index_id,index_level,avg_fragment_size_in_pages,fragment_count,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats
    (DB_ID(N'test'), OBJECT_ID(N'mytest'), NULL, NULL , 'DETAILED');
GO
index_idindex_levelpage_countavg_page_space_used_in_percentavg_fragmentation_in_percent
10360.530442302940466.6666666666667
1110.4571287373362990
there are total 3 pages, but 2 pages are not in logical order(156,157), so the avg_fragmentation_in_percent=2/3=66.6666
9. let's rebuild index
USE test;
GO
ALTER INDEX PK_mytest ON mytest
REBUILD;
GO
10. select data
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
GO
rowidPhysical RIDa_lengthb_lengthd_lengthabd
0x9E00000001000000(1:158:0)4500400111
0x9E00000001000100(1:158:1)4500400222
0x9E00000001000200(1:158:2)4500400333
0x9E00000001000300(1:158:3)4500400555
0x9E00000001000400(1:158:4)4500400777
0x9E00000001000500(1:158:5)4500400999
0x9E00000001000600(1:158:6)4500400111111
0x9E00000001000700(1:158:7)4500400131313
0xA800000001000000(1:168:0)4500400151515
0xA800000001000100(1:168:1)4500400171717
0xA800000001000200(1:168:2)4500400191919
0xA800000001000300(1:168:3)4500400212121
0xA800000001000400(1:168:4)4500400232323
0xA800000001000500(1:168:5)4500400252525
0xA800000001000600(1:168:6)4500400272727
0xA800000001000700(1:168:7)4500400292929
there are 2 pages now. 158 and 168. the are in logical order, looks good. if you check the page date, all rows are sorted in the page. so rebuild index will create new page and move all data from old page to new pages.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating