前言:
聚集索引其概念为:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。那么怎样的字段作为聚集索引才好,无序字段(例如:uuid)是否会影响其性能?下面将做进一步分析。
一、数据准备
先建表:
create table test1 ( id int not null primary key, name varchar(2000) null, test varchar(max) null )
注:varchar(max)是为了产生lob-data类型,varchar(2000)是为了快速产生分页
然后插入数据,执行查询语句:
select %%physloc%% as [物理地址], *from test1
数据如下所示:
上面可以看到,id为11、13、15与17、19、21两组数据明显看出物理上是连续,但两组数据好像没有连续,所以接下来,得看下这个表的结构,执行以下语句:
dbcc ind('test','test1',-1)
结果如下:
注:PageType:1 = data page, 2 = index page, 3 = LOB_MIXED_PAGE, 4 = LOB_TREE_PAGE, 10 = IAM page
上面可以看到,它有两个IAM页,2个一般数据页,1个索引页,6个大字段数据页,这里可以看出,该表数据是进行了分页的,再结合上面,数据id为11、13、15存储在页114,id为17、19、21的存储在126页中。接下来就查看下这2页的详细信息,执行以下语句:
1 DBCC TRACEON(3604,-1) 2 dbcc page(test,1,114,3); 3 dbcc page(test,1,126,3)
分别得到结果,114页信息(只保留核心信息)为:
1 PAGE: (1:114) 2 3 4 BUFFER: 5 6 7 BUF @0x0000000083FB9E00 8 9 bpage = 0x0000000083450000 bhash = 0x0000000000000000 bpageno = (1:114) 10 bdbid = 7 breferences = 0 bcputicks = 0 11 bsampleCount = 0 bUse1 = 26230 bstat = 0xc0010b 12 blog = 0x212159bb bnext = 0x0000000000000000 13 14 PAGE HEADER: 15 16 17 Page @0x0000000083450000 18 19 m_pageId = (1:114) m_headerVersion = 1 m_type = 1 20 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0 21 m_objId (AllocUnitId.idObj) = 34 m_indexId (AllocUnitId.idInd) = 256 22 Metadata: AllocUnitId = 72057594040156160 23 Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1 24 Metadata: ObjectId = 149575571 m_prevPage = (0:0) m_nextPage = (1:126) 25 pminlen = 8 m_slotCnt = 3 m_freeCnt = 1967 26 m_freeData = 6219 m_reservedCnt = 0 m_lsn = (21:43:27) 27 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 28 m_tornBits = 0 29 30 Allocation Status 31 32 GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED 33 PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 34 ML (1:7) = NOT MIN_LOGGED 35 36 Slot 0 Offset 0x60 Length 2041 37 38 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 39 Record Size = 2041 40 Memory Dump @0x0000000014A1A060 41 42 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 43 44 id = 11 45 46 Slot 0 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 47 48 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 49 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 50 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 51 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 52 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 53 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 54 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 55 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 56 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 57 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 58 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 59 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 60 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 61 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 62 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 63 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 64 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 65 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 66 67 test = [BLOB Inline Root] Slot 0 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 68 69 Level = 0 Unused = 0 UpdateSeq = 1 70 TimeStamp = 1613889536 71 Link 0 72 73 Size = 8014 RowId = (1:109:0) 74 75 Slot 0 Offset 0x0 Length 0 Length (physical) 0 76 77 KeyHashValue = (29cf3326f583) 78 Slot 1 Offset 0x859 Length 2041 79 80 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 81 Record Size = 2041 82 Memory Dump @0x0000000014A1A859 83 84 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 85 86 id = 13 87 88 Slot 1 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 89 90 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 91 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 92 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 93 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 94 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 95 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 96 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 97 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 98 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 99 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 100 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 101 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 102 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 103 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 104 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 105 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 106 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 107 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 108 109 test = [BLOB Inline Root] Slot 1 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 110 111 Level = 0 Unused = 0 UpdateSeq = 1 112 TimeStamp = 742064128 113 Link 0 114 115 Size = 8014 RowId = (1:118:0) 116 117 Slot 1 Offset 0x0 Length 0 Length (physical) 0 118 119 KeyHashValue = (e8a66f387cfa) 120 Slot 2 Offset 0x1052 Length 2041 121 122 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 123 Record Size = 2041 124 Memory Dump @0x0000000014A1B052 125 126 Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4 127 128 id = 15 129 130 Slot 2 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 131 132 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 133 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 134 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 135 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 136 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 137 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 138 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 139 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 140 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 141 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 142 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 143 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 144 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 145 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 146 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 147 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 148 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 149 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 150 151 test = [BLOB Inline Root] Slot 2 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 152 153 Level = 0 Unused = 0 UpdateSeq = 1 154 TimeStamp = 362872832 155 Link 0 156 157 Size = 8014 RowId = (1:119:0) 158 159 Slot 2 Offset 0x0 Length 0 Length (physical) 0 160 161 KeyHashValue = (f1de2a205d4a)
126页信息(只保留核心信息):
1 PAGE: (1:126) 2 3 4 BUFFER: 5 6 7 BUF @0x0000000083FBA640 8 9 bpage = 0x0000000083466000 bhash = 0x0000000000000000 bpageno = (1:126) 10 bdbid = 7 breferences = 0 bcputicks = 0 11 bsampleCount = 0 bUse1 = 26503 bstat = 0xc0010b 12 blog = 0x212159bb bnext = 0x0000000000000000 13 14 PAGE HEADER: 15 16 17 Page @0x0000000083466000 18 19 m_pageId = (1:126) m_headerVersion = 1 m_type = 1 20 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 21 m_objId (AllocUnitId.idObj) = 34 m_indexId (AllocUnitId.idInd) = 256 22 Metadata: AllocUnitId = 72057594040156160 23 Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1 24 Metadata: ObjectId = 149575571 m_prevPage = (1:114) m_nextPage = (0:0) 25 pminlen = 8 m_slotCnt = 3 m_freeCnt = 1967 26 m_freeData = 6219 m_reservedCnt = 0 m_lsn = (21:90:14) 27 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 28 m_tornBits = 0 29 30 Allocation Status 31 32 GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED 33 PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 34 ML (1:7) = NOT MIN_LOGGED 35 36 Slot 0 Offset 0x60 Length 2041 37 38 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 39 Record Size = 2041 40 Memory Dump @0x00000000215DA060 41 42 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 43 44 id = 17 45 46 Slot 0 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 47 48 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 49 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 50 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 51 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 52 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 53 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 54 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 55 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 56 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 57 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 58 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 59 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 60 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 61 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 62 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 63 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 64 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 65 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 66 67 test = [BLOB Inline Root] Slot 0 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 68 69 Level = 0 Unused = 0 UpdateSeq = 1 70 TimeStamp = 1411514368 71 Link 0 72 73 Size = 8014 RowId = (1:120:0) 74 75 Slot 0 Offset 0x0 Length 0 Length (physical) 0 76 77 KeyHashValue = (0932b31cce2f) 78 Slot 1 Offset 0x859 Length 2041 79 80 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 81 Record Size = 2041 82 Memory Dump @0x00000000215DA859 83 84 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 85 86 id = 19 87 88 Slot 1 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 89 90 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 91 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 92 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 93 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 94 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 95 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 96 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 97 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 98 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 99 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 100 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 101 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 102 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 103 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 104 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 105 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 106 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 107 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 108 109 test = [BLOB Inline Root] Slot 1 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 110 111 Level = 0 Unused = 0 UpdateSeq = 1 112 TimeStamp = 1056309248 113 Link 0 114 115 Size = 8014 RowId = (1:127:0) 116 117 Slot 1 Offset 0x0 Length 0 Length (physical) 0 118 119 KeyHashValue = (104af604ef9f) 120 Slot 2 Offset 0x1052 Length 2041 121 122 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 123 Record Size = 2041 124 Memory Dump @0x00000000215DB052 125 126 Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4 127 128 id = 21 129 130 Slot 2 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 131 132 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 133 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 134 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 135 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 136 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 137 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 138 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 139 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 140 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 141 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 142 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 143 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 144 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 145 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 146 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 147 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 148 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 149 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 150 151 test = [BLOB Inline Root] Slot 2 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 152 153 Level = 0 Unused = 0 UpdateSeq = 1 154 TimeStamp = 136445952 155 Link 0 156 157 Size = 8014 RowId = (1:148:0) 158 159 Slot 2 Offset 0x0 Length 0 Length (physical) 0 160 161 KeyHashValue = (d123aa1a66e6)
在这两个信息中可看到,id=11的数据test信息存储在109页,13的在118,15的在119,17的在120页,19的在127页,21的在148页
二、分析
(一)、插入数据
插入id为12的数据,查看相关信息:
1.物理地址:
2.表结构:
3.页信息:
114:
1 PAGE: (1:114) 2 3 4 BUFFER: 5 6 7 BUF @0x0000000083FB9E00 8 9 bpage = 0x0000000083450000 bhash = 0x0000000000000000 bpageno = (1:114) 10 bdbid = 7 breferences = 0 bcputicks = 0 11 bsampleCount = 0 bUse1 = 29199 bstat = 0xc0010b 12 blog = 0x212159bb bnext = 0x0000000000000000 13 14 PAGE HEADER: 15 16 17 Page @0x0000000083450000 18 19 m_pageId = (1:114) m_headerVersion = 1 m_type = 1 20 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 21 m_objId (AllocUnitId.idObj) = 34 m_indexId (AllocUnitId.idInd) = 256 22 Metadata: AllocUnitId = 72057594040156160 23 Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1 24 Metadata: ObjectId = 149575571 m_prevPage = (0:0) m_nextPage = (1:173) 25 pminlen = 8 m_slotCnt = 2 m_freeCnt = 4010 26 m_freeData = 4178 m_reservedCnt = 0 m_lsn = (21:113:26) 27 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 28 m_tornBits = 0 29 30 Allocation Status 31 32 GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED 33 PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 34 ML (1:7) = NOT MIN_LOGGED 35 36 Slot 0 Offset 0x60 Length 2041 37 38 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 39 Record Size = 2041 40 Memory Dump @0x00000000293DA060 41 42 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 43 44 id = 11 45 46 Slot 0 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 47 48 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 49 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 50 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 51 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 52 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 53 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 54 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 55 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 56 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 57 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 58 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 59 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 60 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 61 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 62 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 63 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 64 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 65 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 66 67 test = [BLOB Inline Root] Slot 0 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 68 69 Level = 0 Unused = 0 UpdateSeq = 1 70 TimeStamp = 1613889536 71 Link 0 72 73 Size = 8014 RowId = (1:109:0) 74 75 Slot 0 Offset 0x0 Length 0 Length (physical) 0 76 77 KeyHashValue = (29cf3326f583) 78 Slot 1 Offset 0x859 Length 2041 79 80 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 81 Record Size = 2041 82 Memory Dump @0x00000000293DA859 83 84 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 85 86 id = 12 87 88 Slot 1 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 89 90 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 91 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 92 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 93 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 94 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 95 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 96 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 97 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 98 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 99 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 100 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 101 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 102 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 103 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 104 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 105 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 106 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 107 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 108 109 test = [BLOB Inline Root] Slot 1 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 110 111 Level = 0 Unused = 0 UpdateSeq = 1 112 TimeStamp = 2686976 113 Link 0 114 115 Size = 8014 RowId = (1:151:0) 116 117 Slot 1 Offset 0x0 Length 0 Length (physical) 0 118 119 KeyHashValue = (11ea04af99f6)
126:
1 PAGE: (1:126) 2 3 4 BUFFER: 5 6 7 BUF @0x0000000083FBA640 8 9 bpage = 0x0000000083466000 bhash = 0x0000000000000000 bpageno = (1:126) 10 bdbid = 7 breferences = 0 bcputicks = 0 11 bsampleCount = 0 bUse1 = 29732 bstat = 0xc0010b 12 blog = 0x212159bb bnext = 0x0000000000000000 13 14 PAGE HEADER: 15 16 17 Page @0x0000000083466000 18 19 m_pageId = (1:126) m_headerVersion = 1 m_type = 1 20 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 21 m_objId (AllocUnitId.idObj) = 34 m_indexId (AllocUnitId.idInd) = 256 22 Metadata: AllocUnitId = 72057594040156160 23 Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1 24 Metadata: ObjectId = 149575571 m_prevPage = (1:173) m_nextPage = (0:0) 25 pminlen = 8 m_slotCnt = 3 m_freeCnt = 1967 26 m_freeData = 6219 m_reservedCnt = 0 m_lsn = (21:113:23) 27 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 28 m_tornBits = 0 29 30 Allocation Status 31 32 GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED 33 PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 34 ML (1:7) = NOT MIN_LOGGED 35 36 Slot 0 Offset 0x60 Length 2041 37 38 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 39 Record Size = 2041 40 Memory Dump @0x00000000293DA060 41 42 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 43 44 id = 17 45 46 Slot 0 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 47 48 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 49 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 50 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 51 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 52 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 53 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 54 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 55 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 56 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 57 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 58 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 59 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 60 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 61 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 62 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 63 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 64 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 65 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 66 67 test = [BLOB Inline Root] Slot 0 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 68 69 Level = 0 Unused = 0 UpdateSeq = 1 70 TimeStamp = 1411514368 71 Link 0 72 73 Size = 8014 RowId = (1:120:0) 74 75 Slot 0 Offset 0x0 Length 0 Length (physical) 0 76 77 KeyHashValue = (0932b31cce2f) 78 Slot 1 Offset 0x859 Length 2041 79 80 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 81 Record Size = 2041 82 Memory Dump @0x00000000293DA859 83 84 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 85 86 id = 19 87 88 Slot 1 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 89 90 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 91 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 92 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 93 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 94 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 95 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 96 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 97 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 98 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 99 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 100 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 101 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 102 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 103 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 104 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 105 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 106 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 107 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 108 109 test = [BLOB Inline Root] Slot 1 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 110 111 Level = 0 Unused = 0 UpdateSeq = 1 112 TimeStamp = 1056309248 113 Link 0 114 115 Size = 8014 RowId = (1:127:0) 116 117 Slot 1 Offset 0x0 Length 0 Length (physical) 0 118 119 KeyHashValue = (104af604ef9f) 120 Slot 2 Offset 0x1052 Length 2041 121 122 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 123 Record Size = 2041 124 Memory Dump @0x00000000293DB052 125 126 Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4 127 128 id = 21 129 130 Slot 2 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 131 132 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 133 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 134 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 135 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 136 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 137 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 138 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 139 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 140 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 141 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 142 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 143 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 144 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 145 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 146 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 147 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 148 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 149 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 150 151 test = [BLOB Inline Root] Slot 2 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 152 153 Level = 0 Unused = 0 UpdateSeq = 1 154 TimeStamp = 136445952 155 Link 0 156 157 Size = 8014 RowId = (1:148:0) 158 159 Slot 2 Offset 0x0 Length 0 Length (physical) 0 160 161 KeyHashValue = (d123aa1a66e6)
173:
1 PAGE: (1:173) 2 3 4 BUFFER: 5 6 7 BUF @0x0000000083FC1900 8 9 bpage = 0x0000000083598000 bhash = 0x0000000000000000 bpageno = (1:173) 10 bdbid = 7 breferences = 0 bcputicks = 0 11 bsampleCount = 0 bUse1 = 29892 bstat = 0xc0010b 12 blog = 0x212159bb bnext = 0x0000000000000000 13 14 PAGE HEADER: 15 16 17 Page @0x0000000083598000 18 19 m_pageId = (1:173) m_headerVersion = 1 m_type = 1 20 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 21 m_objId (AllocUnitId.idObj) = 34 m_indexId (AllocUnitId.idInd) = 256 22 Metadata: AllocUnitId = 72057594040156160 23 Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1 24 Metadata: ObjectId = 149575571 m_prevPage = (1:114) m_nextPage = (1:126) 25 pminlen = 8 m_slotCnt = 2 m_freeCnt = 4010 26 m_freeData = 4178 m_reservedCnt = 0 m_lsn = (21:113:20) 27 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 28 m_tornBits = 0 29 30 Allocation Status 31 32 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 33 PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 34 ML (1:7) = NOT MIN_LOGGED 35 36 Slot 0 Offset 0x60 Length 2041 37 38 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 39 Record Size = 2041 40 Memory Dump @0x00000000293DA060 41 42 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 43 44 id = 13 45 46 Slot 0 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 47 48 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 49 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 50 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 51 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 52 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 53 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 54 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 55 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 56 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 57 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 58 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 59 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 60 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 61 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 62 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 63 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 64 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 65 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 66 67 test = [BLOB Inline Root] Slot 0 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 68 69 Level = 0 Unused = 0 UpdateSeq = 1 70 TimeStamp = 742064128 71 Link 0 72 73 Size = 8014 RowId = (1:118:0) 74 75 Slot 0 Offset 0x0 Length 0 Length (physical) 0 76 77 KeyHashValue = (e8a66f387cfa) 78 Slot 1 Offset 0x859 Length 2041 79 80 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 81 Record Size = 2041 82 Memory Dump @0x00000000293DA859 83 84 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 85 86 id = 15 87 88 Slot 1 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 89 90 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 91 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 92 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 93 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 94 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 95 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 96 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 97 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 98 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 99 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 100 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 101 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 102 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 103 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 104 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 105 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 106 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 107 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 108 109 test = [BLOB Inline Root] Slot 1 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 110 111 Level = 0 Unused = 0 UpdateSeq = 1 112 TimeStamp = 362872832 113 Link 0 114 115 Size = 8014 RowId = (1:119:0) 116 117 Slot 1 Offset 0x0 Length 0 Length (physical) 0 118 119 KeyHashValue = (f1de2a205d4a)
从以上信息可看到,在id=12数据插入后,114页数据插入新数据,因空间不足,新增页173存储13、15的数据,致使2页产生碎片;为何会转移13、15号数据至新页,而不是只转移15号数据呢?在插入14号数据后(此处不贴代码及图片)推测,可能跟页的数据平衡有关,可能跟索引填充有关,此处就不详细分析
(二)、删除数据
删除17号数据,查看相关信息:
1.物理地址:
2.表结构:
从上面可以看到,发生变化的只有126页,所以只看126的信息就好了:
1 PAGE: (1:126) 2 3 4 BUFFER: 5 6 7 BUF @0x0000000083FBA640 8 9 bpage = 0x0000000083466000 bhash = 0x0000000000000000 bpageno = (1:126) 10 bdbid = 7 breferences = 0 bcputicks = 0 11 bsampleCount = 0 bUse1 = 32875 bstat = 0xc0010b 12 blog = 0x212159bb bnext = 0x0000000000000000 13 14 PAGE HEADER: 15 16 17 Page @0x0000000083466000 18 19 m_pageId = (1:126) m_headerVersion = 1 m_type = 1 20 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0 21 m_objId (AllocUnitId.idObj) = 34 m_indexId (AllocUnitId.idInd) = 256 22 Metadata: AllocUnitId = 72057594040156160 23 Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1 24 Metadata: ObjectId = 149575571 m_prevPage = (1:173) m_nextPage = (0:0) 25 pminlen = 8 m_slotCnt = 2 m_freeCnt = 4010 26 m_freeData = 6219 m_reservedCnt = 0 m_lsn = (21:175:1) 27 m_xactReserved = 0 m_xdesId = (0:588) m_ghostRecCnt = 0 28 m_tornBits = 0 29 30 Allocation Status 31 32 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 33 PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 34 ML (1:7) = NOT MIN_LOGGED 35 36 Slot 0 Offset 0x859 Length 2041 37 38 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 39 Record Size = 2041 40 Memory Dump @0x00000000293DA859 41 42 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 43 44 id = 19 45 46 Slot 0 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 47 48 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 49 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 50 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 51 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 52 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 53 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 54 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 55 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 56 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 57 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 58 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 59 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 60 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 61 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 62 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 63 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 64 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 65 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 66 67 test = [BLOB Inline Root] Slot 0 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 68 69 Level = 0 Unused = 0 UpdateSeq = 1 70 TimeStamp = 1056309248 71 Link 0 72 73 Size = 8014 RowId = (1:127:0) 74 75 Slot 0 Offset 0x0 Length 0 Length (physical) 0 76 77 KeyHashValue = (104af604ef9f) 78 Slot 1 Offset 0x1052 Length 2041 79 80 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 81 Record Size = 2041 82 Memory Dump @0x00000000293DB052 83 84 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 85 86 id = 21 87 88 Slot 1 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 89 90 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 91 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 92 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 93 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 94 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 95 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 96 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 97 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 98 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 99 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 100 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 101 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 102 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 103 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 104 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 105 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 106 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 107 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 108 109 test = [BLOB Inline Root] Slot 1 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 110 111 Level = 0 Unused = 0 UpdateSeq = 1 112 TimeStamp = 136445952 113 Link 0 114 115 Size = 8014 RowId = (1:148:0) 116 117 Slot 1 Offset 0x0 Length 0 Length (physical) 0 118 119 KeyHashValue = (d123aa1a66e6)
从以上信息可以看到,在删除数据后,对应页的数据位置可能会发生变动,同时存储结构上,会删除相关的指引,但数据不一定会删除,例如17号对应的大字段数据120页还是存在的(可执行dbcc page(test,1,120,3)查看该页信息)
(三)、更新索引字段
将11号更新为20号,查看相关信息:
1.物理地址:
根据之前的分析,从上面内容可看出,11号数据转移之126页,12、21号数据顺序发生变更,接着将12号数据更新为17号,再查看其变化:
1.物理地址:
2.表结构:
3.页信息:
114:
1 PAGE: (1:114) 2 3 4 BUFFER: 5 6 7 BUF @0x0000000083FB9E00 8 9 bpage = 0x0000000083450000 bhash = 0x0000000000000000 bpageno = (1:114) 10 bdbid = 7 breferences = 0 bcputicks = 0 11 bsampleCount = 0 bUse1 = 36106 bstat = 0xc0010b 12 blog = 0x212159bb bnext = 0x0000000000000000 13 14 PAGE HEADER: 15 16 17 Page @0x0000000083450000 18 19 m_pageId = (1:114) m_headerVersion = 1 m_type = 1 20 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0 21 m_objId (AllocUnitId.idObj) = 34 m_indexId (AllocUnitId.idInd) = 256 22 Metadata: AllocUnitId = 72057594040156160 23 Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1 24 Metadata: ObjectId = 149575571 m_prevPage = (0:0) m_nextPage = (1:173) 25 pminlen = 8 m_slotCnt = 1 m_freeCnt = 6053 26 m_freeData = 4178 m_reservedCnt = 0 m_lsn = (21:185:5) 27 m_xactReserved = 0 m_xdesId = (0:590) m_ghostRecCnt = 1 28 m_tornBits = 0 29 30 Allocation Status 31 32 GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED 33 PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 34 ML (1:7) = NOT MIN_LOGGED 35 36 Slot 0 Offset 0x859 Length 2041 37 38 Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 39 Record Size = 2041 40 Memory Dump @0x000000002F1DC859 41 42 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 43 44 id = 12 45 46 Slot 0 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 47 48 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 49 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 50 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 51 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 52 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 53 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 54 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 55 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 56 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 57 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 58 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 59 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 60 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 61 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 62 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 63 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 64 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 65 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 66 67 test = [BLOB Inline Root] Slot 0 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 68 69 Level = 0 Unused = 0 UpdateSeq = 1 70 TimeStamp = 2686976 71 Link 0 72 73 Size = 8014 RowId = (1:151:0) 74 75 Slot 0 Offset 0x0 Length 0 Length (physical) 0 76 77 KeyHashValue = (11ea04af99f6)
126:
1 PAGE: (1:126) 2 3 4 BUFFER: 5 6 7 BUF @0x0000000083FBA640 8 9 bpage = 0x0000000083466000 bhash = 0x0000000000000000 bpageno = (1:126) 10 bdbid = 7 breferences = 0 bcputicks = 0 11 bsampleCount = 0 bUse1 = 36167 bstat = 0xc0010b 12 blog = 0x212159bb bnext = 0x0000000000000000 13 14 PAGE HEADER: 15 16 17 Page @0x0000000083466000 18 19 m_pageId = (1:126) m_headerVersion = 1 m_type = 1 20 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x4000 21 m_objId (AllocUnitId.idObj) = 34 m_indexId (AllocUnitId.idInd) = 256 22 Metadata: AllocUnitId = 72057594040156160 23 Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1 24 Metadata: ObjectId = 149575571 m_prevPage = (1:173) m_nextPage = (1:175) 25 pminlen = 8 m_slotCnt = 2 m_freeCnt = 4010 26 m_freeData = 4178 m_reservedCnt = 0 m_lsn = (21:185:18) 27 m_xactReserved = 0 m_xdesId = (0:588) m_ghostRecCnt = 0 28 m_tornBits = 0 29 30 Allocation Status 31 32 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 33 PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 34 ML (1:7) = NOT MIN_LOGGED 35 36 Slot 0 Offset 0x859 Length 2041 37 38 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 39 Record Size = 2041 40 Memory Dump @0x000000002F1DC859 41 42 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 43 44 id = 17 45 46 Slot 0 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 47 48 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 49 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 50 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 51 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 52 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 53 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 54 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 55 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 56 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 57 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 58 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 59 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 60 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 61 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 62 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 63 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 64 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 65 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 66 67 test = [BLOB Inline Root] Slot 0 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 68 69 Level = 0 Unused = 0 UpdateSeq = 1 70 TimeStamp = 2686976 71 Link 0 72 73 Size = 8014 RowId = (1:151:0) 74 75 Slot 0 Offset 0x0 Length 0 Length (physical) 0 76 77 KeyHashValue = (0932b31cce2f) 78 Slot 1 Offset 0x60 Length 2041 79 80 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 81 Record Size = 2041 82 Memory Dump @0x000000002F1DC060 83 84 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 85 86 id = 19 87 88 Slot 1 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 89 90 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 91 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 92 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 93 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 94 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 95 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 96 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 97 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 98 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 99 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 100 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 101 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 102 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 103 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 104 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 105 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 106 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 107 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 108 109 test = [BLOB Inline Root] Slot 1 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 110 111 Level = 0 Unused = 0 UpdateSeq = 1 112 TimeStamp = 1056309248 113 Link 0 114 115 Size = 8014 RowId = (1:127:0) 116 117 Slot 1 Offset 0x0 Length 0 Length (physical) 0 118 119 KeyHashValue = (104af604ef9f)
173:
1 PAGE: (1:173) 2 3 4 BUFFER: 5 6 7 BUF @0x0000000083FC1900 8 9 bpage = 0x0000000083598000 bhash = 0x0000000000000000 bpageno = (1:173) 10 bdbid = 7 breferences = 0 bcputicks = 0 11 bsampleCount = 0 bUse1 = 36230 bstat = 0xc0010b 12 blog = 0x212159bb bnext = 0x0000000000000000 13 14 PAGE HEADER: 15 16 17 Page @0x0000000083598000 18 19 m_pageId = (1:173) m_headerVersion = 1 m_type = 1 20 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0 21 m_objId (AllocUnitId.idObj) = 34 m_indexId (AllocUnitId.idInd) = 256 22 Metadata: AllocUnitId = 72057594040156160 23 Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1 24 Metadata: ObjectId = 149575571 m_prevPage = (1:114) m_nextPage = (1:126) 25 pminlen = 8 m_slotCnt = 3 m_freeCnt = 1967 26 m_freeData = 6219 m_reservedCnt = 0 m_lsn = (21:145:13) 27 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 28 m_tornBits = 0 29 30 Allocation Status 31 32 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 33 PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 34 ML (1:7) = NOT MIN_LOGGED 35 36 Slot 0 Offset 0x60 Length 2041 37 38 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 39 Record Size = 2041 40 Memory Dump @0x000000002F1DC060 41 42 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 43 44 id = 13 45 46 Slot 0 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 47 48 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 49 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 50 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 51 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 52 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 53 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 54 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 55 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 56 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 57 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 58 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 59 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 60 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 61 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 62 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 63 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 64 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 65 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 66 67 test = [BLOB Inline Root] Slot 0 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 68 69 Level = 0 Unused = 0 UpdateSeq = 1 70 TimeStamp = 742064128 71 Link 0 72 73 Size = 8014 RowId = (1:118:0) 74 75 Slot 0 Offset 0x0 Length 0 Length (physical) 0 76 77 KeyHashValue = (e8a66f387cfa) 78 Slot 1 Offset 0x1052 Length 2041 79 80 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 81 Record Size = 2041 82 Memory Dump @0x000000002F1DD052 83 84 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 85 86 id = 14 87 88 Slot 1 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 89 90 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 91 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 92 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 93 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 94 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 95 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 96 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 97 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 98 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 99 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 100 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 101 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 102 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 103 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 104 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 105 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 106 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 107 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 108 109 test = [BLOB Inline Root] Slot 1 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 110 111 Level = 0 Unused = 0 UpdateSeq = 1 112 TimeStamp = 2686976 113 Link 0 114 115 Size = 8014 RowId = (1:174:0) 116 117 Slot 1 Offset 0x0 Length 0 Length (physical) 0 118 119 KeyHashValue = (089241b7b846) 120 Slot 2 Offset 0x859 Length 2041 121 122 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 123 Record Size = 2041 124 Memory Dump @0x000000002F1DC859 125 126 Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4 127 128 id = 15 129 130 Slot 2 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 131 132 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 133 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 134 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 135 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 136 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 137 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 138 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 139 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 140 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 141 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 142 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 143 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 144 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 145 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 146 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 147 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 148 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 149 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 150 151 test = [BLOB Inline Root] Slot 2 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 152 153 Level = 0 Unused = 0 UpdateSeq = 1 154 TimeStamp = 362872832 155 Link 0 156 157 Size = 8014 RowId = (1:119:0) 158 159 Slot 2 Offset 0x0 Length 0 Length (physical) 0 160 161 KeyHashValue = (f1de2a205d4a)
175:
1 PAGE: (1:175) 2 3 4 BUFFER: 5 6 7 BUF @0x0000000083FC1300 8 9 bpage = 0x0000000083588000 bhash = 0x0000000000000000 bpageno = (1:175) 10 bdbid = 7 breferences = 0 bcputicks = 0 11 bsampleCount = 0 bUse1 = 36325 bstat = 0xc0010b 12 blog = 0x212159bb bnext = 0x0000000000000000 13 14 PAGE HEADER: 15 16 17 Page @0x0000000083588000 18 19 m_pageId = (1:175) m_headerVersion = 1 m_type = 1 20 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 21 m_objId (AllocUnitId.idObj) = 34 m_indexId (AllocUnitId.idInd) = 256 22 Metadata: AllocUnitId = 72057594040156160 23 Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1 24 Metadata: ObjectId = 149575571 m_prevPage = (1:126) m_nextPage = (0:0) 25 pminlen = 8 m_slotCnt = 2 m_freeCnt = 4010 26 m_freeData = 4178 m_reservedCnt = 0 m_lsn = (21:185:13) 27 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 28 m_tornBits = 0 29 30 Allocation Status 31 32 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 33 PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 34 ML (1:7) = NOT MIN_LOGGED 35 36 Slot 0 Offset 0x60 Length 2041 37 38 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 39 Record Size = 2041 40 Memory Dump @0x000000002F1DC060 41 42 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 43 44 id = 20 45 46 Slot 0 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 47 48 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 49 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 50 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 51 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 52 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 53 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 54 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 55 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 56 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 57 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 58 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 59 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 60 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 61 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 62 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 63 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 64 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 65 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 66 67 test = [BLOB Inline Root] Slot 0 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 68 69 Level = 0 Unused = 0 UpdateSeq = 1 70 TimeStamp = 1613889536 71 Link 0 72 73 Size = 8014 RowId = (1:109:0) 74 75 Slot 0 Offset 0x0 Length 0 Length (physical) 0 76 77 KeyHashValue = (286fc18d83ea) 78 Slot 1 Offset 0x859 Length 2041 79 80 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 81 Record Size = 2041 82 Memory Dump @0x000000002F1DC859 83 84 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 85 86 id = 21 87 88 Slot 1 Column 2 Offset 0x11 Length 2000 Length (physical) 2000 89 90 name = asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 91 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 92 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 93 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 94 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 95 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 96 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 97 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasda 98 sdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 99 asdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasd 100 asdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasd 101 asdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasd 102 aasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaas 103 dasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdas 104 dasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdas 105 daasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaa 106 sdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasda 107 sdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasdaasdasdasda 108 109 test = [BLOB Inline Root] Slot 1 Column 3 Offset 0x7e1 Length 24 Length (physical) 24 110 111 Level = 0 Unused = 0 UpdateSeq = 1 112 TimeStamp = 136445952 113 Link 0 114 115 Size = 8014 RowId = (1:148:0) 116 117 Slot 1 Offset 0x0 Length 0 Length (physical) 0 118 119 KeyHashValue = (d123aa1a66e6)
从上面可以看到,当12号数据更新为17号时,残留了114页,里面有12号的数据(详细原因可自行查询,此处不分析),其它的跟插入数据类似
三、结论
从以上分析可得:
(1)、聚集索引建议使用增长性的字段,用uuid等无序字段作为聚集索引时,可能会使索引碎片大增
(2)、聚集索引字段应该是尽可能的不要进行更新,否则会增加碎片量,降低查询效率
(3)、数据删除只会影响对应页的信息,不会影响整个结构以及数据的排序,当然,可能会有信息残留
(4)、lob-data数据是存储在另外一页的,索引数据页只记录指针(或许如果不查询该字段的话,那么这些大字段信息对查询是不怎么影响的,这个得去分析下)
(5)、较大的字段(例如varchar(2000)之类的),如果是比较少查询的话,建议不要放在主信息表中,避免分页以及减少碎片的产生