Tuesday, November 20, 2012

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
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 3 3 3
0x9900000001000200 (1:153:2) 4 500 400 5 5 5
0x9900000001000300 (1:153:3) 4 500 400 7 7 7
0x9900000001000400 (1:153:4) 4 500 400 9 9 9
0x9900000001000500 (1:153:5) 4 500 400 11 11 11
0x9900000001000600 (1:153:6) 4 500 400 13 13 13
0x9900000001000700 (1:153:7) 4 500 400 15 15 15
0x9C00000001000000 (1:156:0) 4 500 400 17 17 17
0x9C00000001000100 (1:156:1) 4 500 400 19 19 19
0x9C00000001000200 (1:156:2) 4 500 400 21 21 21
0x9C00000001000300 (1:156:3) 4 500 400 23 23 23
0x9C00000001000400 (1:156:4) 4 500 400 25 25 25
0x9C00000001000500 (1:156:5) 4 500 400 27 27 27
0x9C00000001000600 (1:156:6) 4 500 400 29 29 29



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

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 5 5                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    5
0x9900000001000400 (1:153:4) 4 500 400 7 7                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    7
0x9D00000001000000 (1:157:0) 4 500 400 9 9                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    9
0x9D00000001000100 (1:157:1) 4 500 400 11 11                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   11
0x9D00000001000200 (1:157:2) 4 500 400 13 13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   13
0x9D00000001000300 (1:157:3) 4 500 400 15 15                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   15
0x9C00000001000000 (1:156:0) 4 500 400 17 17                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   17
0x9C00000001000100 (1:156:1) 4 500 400 19 19                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   19
0x9C00000001000200 (1:156:2) 4 500 400 21 21                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   21
0x9C00000001000300 (1:156:3) 4 500 400 23 23                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   23
0x9C00000001000400 (1:156:4) 4 500 400 25 25                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   25
0x9C00000001000500 (1:156:5) 4 500 400 27 27                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   27
0x9C00000001000600 (1:156:6) 4 500 400 29 29                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   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_id index_level page_count avg_page_space_used_in_percent avg_fragmentation_in_percent
1 0 3 60.5304423029404 66.6666666666667
1 1 1 0.457128737336299 0

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
rowid Physical RID a_length b_length d_length a b d
0x9E00000001000000 (1:158:0) 4 500 400 1 1 1
0x9E00000001000100 (1:158:1) 4 500 400 2 2 2
0x9E00000001000200 (1:158:2) 4 500 400 3 3 3
0x9E00000001000300 (1:158:3) 4 500 400 5 5 5
0x9E00000001000400 (1:158:4) 4 500 400 7 7 7
0x9E00000001000500 (1:158:5) 4 500 400 9 9 9
0x9E00000001000600 (1:158:6) 4 500 400 11 11 11
0x9E00000001000700 (1:158:7) 4 500 400 13 13 13
0xA800000001000000 (1:168:0) 4 500 400 15 15 15
0xA800000001000100 (1:168:1) 4 500 400 17 17 17
0xA800000001000200 (1:168:2) 4 500 400 19 19 19
0xA800000001000300 (1:168:3) 4 500 400 21 21 21
0xA800000001000400 (1:168:4) 4 500 400 23 23 23
0xA800000001000500 (1:168:5) 4 500 400 25 25 25
0xA800000001000600 (1:168:6) 4 500 400 27 27 27
0xA800000001000700 (1:168:7) 4 500 400 29 29 29


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.
























No comments:

Post a Comment