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