根据《SQLSERVER聚集索引与非聚集索引的再次研究（上）》里说的，聚集索引维护着创建第一个聚集索引时的第一个字段的顺序来排序
当插入记录的时候，或者重新组织索引的时候都会按照字段顺序来排序
今天来做一个实验来验证一下
--------------------------------------------------华丽的分割线------------------------------------------------
先创建一个聚集索引表
1 USE [pratice] 2 GO 3 --DROP TABLE ClusteredTable 4 CREATE TABLE ClusteredTable 5 ( id INT, 6 col2 CHAR(999), 7 col3 VARCHAR(10) 8 )
这个表每个行由int(4字节),char(999字节)和varchar(0字节组成），所以每行为1003个字节,
则8行占用空间1003*8=8024字节加上一些内部开销，可以容纳在一个页面
1 --创建聚集索引 2 CREATE CLUSTERED INDEX CIX ON ClusteredTable(id ASC)
插入数据
1 --由于聚集索引需要有2个数据页才会出现一个索引页，所以这里插入16条记录，16条记录就需要2个数据页 2 DECLARE @i INT 3 SET @i=1 4 WHILE(@i<32) 5 BEGIN 6 INSERT INTO [dbo].[ClusteredTable] ( [id], [col2], [col3] ) 7 VALUES(@i,'xxx','') 8 SET @i=@i+2 9 END
看一下所用的数据页数量
1 SELECT [index_id], [index_type_desc], [alloc_unit_type_desc], [index_depth], 2 [index_level], [avg_fragmentation_in_percent], [fragment_count], 3 [page_count] 4 FROM [sys].[dm_db_index_physical_stats](DB_ID('pratice'), 5 OBJECT_ID('pratice.dbo.ClusteredTable'), 6 NULL, NULL, NULL)
再创建一个表，用来保存DBCC IND的结果
1 CREATE TABLE DBCCResult ( 2 PageFID NVARCHAR(200), 3 PagePID NVARCHAR(200), 4 IAMFID NVARCHAR(200), 5 IAMPID NVARCHAR(200), 6 ObjectID NVARCHAR(200), 7 IndexID NVARCHAR(200), 8 PartitionNumber NVARCHAR(200), 9 PartitionID NVARCHAR(200), 10 iam_chain_type NVARCHAR(200), 11 PageType NVARCHAR(200), 12 IndexLevel NVARCHAR(200), 13 NextPageFID NVARCHAR(200), 14 NextPagePID NVARCHAR(200), 15 PrevPageFID NVARCHAR(200), 16 PrevPagePID NVARCHAR(200) 17 )View Code
执行下面语句，看一下DBCC IND的结果
先说明一下：
PageType 分页类型：1：数据页面；2：索引页面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM页面
IndexID 索引ID，0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引 大于250就是text或image字段
1 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,ClusteredTable,-1) ') 2 3 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
看一下页面内容
1 DBCC TRACEON(3604,-1) 2 GO 3 DBCC PAGE([pratice],1,14623,3) --索引页 4 GO 5 DBCC PAGE([pratice],1,14544,3)--数据页 6 GO 7 DBCC PAGE([pratice],1,37036,3)--数据页 8 GO
聚集索引页面
数据页 14544 可以看到14544页里面保存的是1~15的记录 其中字段m_slotCnt = 8 表示本页面存储了8条记录
1 PAGE: (1:14544) 2 3 4 BUFFER: 5 6 7 BUF @0x03F577E0 8 9 bpage = 0x19108000 bhash = 0x00000000 bpageno = (1:14544) 10 bdbid = 5 breferences = 0 bUse1 = 12315 11 bstat = 0x2c0000b blog = 0x32159bb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x19108000 17 18 m_pageId = (1:14544) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0 20 m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063486976 22 Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1 23 Metadata: ObjectId = 427148567 m_prevPage = (0:0) m_nextPage = (1:37036) 24 pminlen = 1007 m_slotCnt = 8 m_freeCnt = 0 25 m_freeData = 8176 m_reservedCnt = 0 m_lsn = (2773:397:15) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x095CC060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 1 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 52 col3 = [NULL] 53 54 Slot 1 Offset 0x452 Length 1010 55 56 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 57 Memory Dump @0x095CC452 58 59 60 UNIQUIFIER = [NULL] 61 62 Slot 1 Column 1 Offset 0x4 Length 4 63 64 id = 3 65 66 Slot 1 Column 2 Offset 0x8 Length 999 67 68 col2 = xxx 69 70 col3 = [NULL] 71 72 Slot 2 Offset 0x844 Length 1010 73 74 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 75 Memory Dump @0x095CC844 76 77 78 UNIQUIFIER = [NULL] 79 80 Slot 2 Column 1 Offset 0x4 Length 4 81 82 id = 5 83 84 Slot 2 Column 2 Offset 0x8 Length 999 85 86 col2 = xxx 87 88 col3 = [NULL] 89 90 Slot 3 Offset 0xc36 Length 1010 91 92 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 93 Memory Dump @0x095CCC36 94 95 96 UNIQUIFIER = [NULL] 97 98 Slot 3 Column 1 Offset 0x4 Length 4 99 100 id = 7 101 102 Slot 3 Column 2 Offset 0x8 Length 999 103 104 col2 = xxx 105 106 col3 = [NULL] 107 108 Slot 4 Offset 0x1028 Length 1010 109 110 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 111 Memory Dump @0x095CD028 112 113 114 UNIQUIFIER = [NULL] 115 116 Slot 4 Column 1 Offset 0x4 Length 4 117 118 id = 9 119 120 Slot 4 Column 2 Offset 0x8 Length 999 121 122 col2 = xxx 123 124 col3 = [NULL] 125 126 Slot 5 Offset 0x141a Length 1010 127 128 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 129 Memory Dump @0x095CD41A 130 131 UNIQUIFIER = [NULL] 132 133 Slot 5 Column 1 Offset 0x4 Length 4 134 135 id = 11 136 137 Slot 5 Column 2 Offset 0x8 Length 999 138 139 col2 = xxx 140 141 col3 = [NULL] 142 143 Slot 6 Offset 0x180c Length 1010 144 145 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 146 Memory Dump @0x095CD80C 147 148 149 UNIQUIFIER = [NULL] 150 151 Slot 6 Column 1 Offset 0x4 Length 4 152 153 id = 13 154 155 Slot 6 Column 2 Offset 0x8 Length 999 156 157 col2 = xxx 158 159 160 col3 = [NULL] 161 162 Slot 7 Offset 0x1bfe Length 1010 163 164 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 165 Memory Dump @0x095CDBFE 166 167 UNIQUIFIER = [NULL] 168 169 Slot 7 Column 1 Offset 0x4 Length 4 170 171 id = 15 172 173 Slot 7 Column 2 Offset 0x8 Length 999 174 175 col2 = xxx 176 177 col3 = [NULL] 178 179 180 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
数据页 37036可以看到14544页里面保存的是17~31的记录 其中字段m_slotCnt = 8 表示本页面存储了8条记录
1 PAGE: (1:37036) 2 3 4 BUFFER: 5 6 7 BUF @0x03F6F5F4 8 9 bpage = 0x19CD0000 bhash = 0x00000000 bpageno = (1:37036) 10 bdbid = 5 breferences = 0 bUse1 = 12635 11 bstat = 0x2c0000b blog = 0x212121bb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x19CD0000 17 18 m_pageId = (1:37036) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 20 m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063486976 22 Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1 23 Metadata: ObjectId = 427148567 m_prevPage = (1:14544) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 8 m_freeCnt = 0 25 m_freeData = 8176 m_reservedCnt = 0 m_lsn = (2773:421:2) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x0A33C060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 17 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 Slot 1 Offset 0x452 Length 1010 54 55 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 56 Memory Dump @0x0A33C452 57 58 59 UNIQUIFIER = [NULL] 60 61 Slot 1 Column 1 Offset 0x4 Length 4 62 63 id = 19 64 65 Slot 1 Column 2 Offset 0x8 Length 999 66 67 col2 = xxx 68 69 col3 = [NULL] 70 71 Slot 2 Offset 0x844 Length 1010 72 73 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 74 Memory Dump @0x0A33C844 75 76 77 UNIQUIFIER = [NULL] 78 79 Slot 2 Column 1 Offset 0x4 Length 4 80 81 id = 21 82 83 Slot 2 Column 2 Offset 0x8 Length 999 84 85 col2 = xxx 86 87 col3 = [NULL] 88 89 Slot 3 Offset 0xc36 Length 1010 90 91 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 92 Memory Dump @0x0A33CC36 93 94 95 UNIQUIFIER = [NULL] 96 97 Slot 3 Column 1 Offset 0x4 Length 4 98 99 id = 23 100 101 Slot 3 Column 2 Offset 0x8 Length 999 102 103 col2 = xxx 104 105 col3 = [NULL] 106 107 Slot 4 Offset 0x1028 Length 1010 108 109 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 110 Memory Dump @0x0A33D028 111 112 113 UNIQUIFIER = [NULL] 114 115 Slot 4 Column 1 Offset 0x4 Length 4 116 117 id = 25 118 119 Slot 4 Column 2 Offset 0x8 Length 999 120 121 col2 = xxx 122 123 col3 = [NULL] 124 125 Slot 5 Offset 0x141a Length 1010 126 127 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 128 Memory Dump @0x0A33D41A 129 130 131 UNIQUIFIER = [NULL] 132 133 Slot 5 Column 1 Offset 0x4 Length 4 134 135 id = 27 136 137 Slot 5 Column 2 Offset 0x8 Length 999 138 139 col2 = xxx 140 141 col3 = [NULL] 142 143 Slot 6 Offset 0x180c Length 1010 144 145 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 146 Memory Dump @0x0A33D80C 147 148 149 UNIQUIFIER = [NULL] 150 151 Slot 6 Column 1 Offset 0x4 Length 4 152 153 id = 29 154 155 Slot 6 Column 2 Offset 0x8 Length 999 156 157 col2 = xxx 158 159 160 col3 = [NULL] 161 162 Slot 7 Offset 0x1bfe Length 1010 163 164 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 165 Memory Dump @0x0A33DBFE 166 167 168 UNIQUIFIER = [NULL] 169 170 Slot 7 Column 1 Offset 0x4 Length 4 171 172 id = 31 173 174 Slot 7 Column 2 Offset 0x8 Length 999 175 176 col2 = xxx 177 178 col3 = [NULL] 179 180 181 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
---------------------------------------------------------华丽的分割线------------------------------------------------
我们创建一个堆表以作为对比
1 USE [pratice] 2 GO 3 --DROP TABLE HeapTable 4 CREATE TABLE HeapTable 5 ( id INT, 6 col2 CHAR(999), 7 col3 VARCHAR(10) 8 ) 9 10 DECLARE @i INT 11 SET @i=1 12 WHILE(@i<32) 13 BEGIN 14 INSERT INTO [dbo].[HeapTable] ( [id], [col2], [col3] ) 15 VALUES(@i,'xxx','') 16 SET @i=@i+2 17 END
看一下所用的数据页数量
1 SELECT [index_id], [index_type_desc], [alloc_unit_type_desc], [index_depth], 2 [index_level], [avg_fragmentation_in_percent], [fragment_count], 3 [page_count] 4 FROM [sys].[dm_db_index_physical_stats](DB_ID('pratice'), 5 OBJECT_ID('pratice.dbo.HeapTable'), 6 NULL, NULL, NULL)
再看一下DBCC IND的结果
1 --先清空[DBCCResult]的数据 2 TRUNCATE TABLE [dbo].[DBCCResult] 3 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,HeapTable,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
看一下数据页14529里的数据 id从1~13 其中字段m_slotCnt = 7 表示本页面存储了7条记录
1 PAGE: (1:14529) 2 3 4 BUFFER: 5 6 7 BUF @0x03F83188 8 9 bpage = 0x1A516000 bhash = 0x00000000 bpageno = (1:14529) 10 bdbid = 5 breferences = 0 bUse1 = 13252 11 bstat = 0x2c0000b blog = 0x2159bbbb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1A516000 17 18 m_pageId = (1:14529) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 20 m_objId (AllocUnitId.idObj) = 391 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063552512 22 Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 0 23 Metadata: ObjectId = 443148624 m_prevPage = (0:0) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 7 m_freeCnt = 1012 25 m_freeData = 7166 m_reservedCnt = 0 m_lsn = (2773:486:2) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:8088) = 0x63 MIXED_EXT ALLOCATED 95_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x095CC060 39 40 41 42 Slot 0 Column 0 Offset 0x4 Length 4 43 44 id = 1 45 46 Slot 0 Column 1 Offset 0x8 Length 999 47 48 col2 = xxx 49 50 col3 = [NULL] 51 52 Slot 1 Offset 0x452 Length 1010 53 54 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 55 Memory Dump @0x095CC452 56 57 58 59 Slot 1 Column 0 Offset 0x4 Length 4 60 61 id = 3 62 63 Slot 1 Column 1 Offset 0x8 Length 999 64 65 col2 = xxx 66 67 col3 = [NULL] 68 69 Slot 2 Offset 0x844 Length 1010 70 71 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 72 Memory Dump @0x095CC844 73 74 75 Slot 2 Column 0 Offset 0x4 Length 4 76 77 id = 5 78 79 Slot 2 Column 1 Offset 0x8 Length 999 80 81 col2 = xxx 82 83 col3 = [NULL] 84 85 Slot 3 Offset 0xc36 Length 1010 86 87 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 88 Memory Dump @0x095CCC36 89 90 91 92 Slot 3 Column 0 Offset 0x4 Length 4 93 94 id = 7 95 96 Slot 3 Column 1 Offset 0x8 Length 999 97 98 col2 = xxx 99 100 col3 = [NULL] 101 102 Slot 4 Offset 0x1028 Length 1010 103 104 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 105 Memory Dump @0x095CD028 106 107 108 109 Slot 4 Column 0 Offset 0x4 Length 4 110 111 id = 9 112 113 Slot 4 Column 1 Offset 0x8 Length 999 114 115 col2 = xxx 116 117 col3 = [NULL] 118 119 Slot 5 Offset 0x141a Length 1010 120 121 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 122 Memory Dump @0x095CD41A 123 124 125 126 Slot 5 Column 0 Offset 0x4 Length 4 127 128 id = 11 129 130 Slot 5 Column 1 Offset 0x8 Length 999 131 132 col2 = xxx 133 134 col3 = [NULL] 135 136 Slot 6 Offset 0x180c Length 1010 137 138 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 139 Memory Dump @0x095CD80C 140 141 142 143 Slot 6 Column 0 Offset 0x4 Length 4 144 145 id = 13 146 147 Slot 6 Column 1 Offset 0x8 Length 999 148 149 col2 = xxx 150 151 col3 = [NULL] 152 153 154 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
看一下数据页14545里的数据 id从15~27 其中字段m_slotCnt = 7 表示本页面存储了7条记录
1 PAGE: (1:14545) 2 3 4 BUFFER: 5 6 7 BUF @0x03F5782C 8 9 bpage = 0x1910A000 bhash = 0x00000000 bpageno = (1:14545) 10 bdbid = 5 breferences = 0 bUse1 = 13392 11 bstat = 0x2c0000b blog = 0x32159bb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1910A000 17 18 m_pageId = (1:14545) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 20 m_objId (AllocUnitId.idObj) = 391 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063552512 22 Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 0 23 Metadata: ObjectId = 443148624 m_prevPage = (0:0) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 7 m_freeCnt = 1012 25 m_freeData = 7166 m_reservedCnt = 0 m_lsn = (2774:28:2) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:8088) = 0x63 MIXED_EXT ALLOCATED 95_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x0A33C060 39 40 41 42 Slot 0 Column 0 Offset 0x4 Length 4 43 44 id = 15 45 46 Slot 0 Column 1 Offset 0x8 Length 999 47 48 col2 = xxx 49 50 col3 = [NULL] 51 52 Slot 1 Offset 0x452 Length 1010 53 54 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 55 Memory Dump @0x0A33C452 56 57 58 59 Slot 1 Column 0 Offset 0x4 Length 4 60 61 id = 17 62 63 Slot 1 Column 1 Offset 0x8 Length 999 64 65 col2 = xxx 66 67 col3 = [NULL] 68 69 Slot 2 Offset 0x844 Length 1010 70 71 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 72 Memory Dump @0x0A33C844 73 74 75 76 Slot 2 Column 0 Offset 0x4 Length 4 77 78 id = 19 79 80 Slot 2 Column 1 Offset 0x8 Length 999 81 82 col2 = xxx 83 84 col3 = [NULL] 85 86 Slot 3 Offset 0xc36 Length 1010 87 88 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 89 Memory Dump @0x0A33CC36 90 91 92 93 Slot 3 Column 0 Offset 0x4 Length 4 94 95 id = 21 96 97 Slot 3 Column 1 Offset 0x8 Length 999 98 99 col2 = xxx 100 101 col3 = [NULL] 102 103 Slot 4 Offset 0x1028 Length 1010 104 105 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 106 Memory Dump @0x0A33D028 107 108 109 110 Slot 4 Column 0 Offset 0x4 Length 4 111 112 id = 23 113 114 Slot 4 Column 1 Offset 0x8 Length 999 115 116 col2 = xxx 117 118 col3 = [NULL] 119 120 Slot 5 Offset 0x141a Length 1010 121 122 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 123 Memory Dump @0x0A33D41A 124 125 126 Slot 5 Column 0 Offset 0x4 Length 4 127 128 id = 25 129 130 Slot 5 Column 1 Offset 0x8 Length 999 131 132 col2 = xxx 133 134 col3 = [NULL] 135 136 Slot 6 Offset 0x180c Length 1010 137 138 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 139 Memory Dump @0x0A33D80C 140 141 142 Slot 6 Column 0 Offset 0x4 Length 4 143 144 id = 27 145 146 Slot 6 Column 1 Offset 0x8 Length 999 147 148 col2 = xxx 149 150 col3 = [NULL] 151 152 153 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
看一下数据页37037里的数据 只有id为29 和31这两条记录 其中字段m_slotCnt = 2 表示本页面存储了2条记录
1 PAGE: (1:37037) 2 3 4 BUFFER: 5 6 7 BUF @0x03F83ABC 8 9 bpage = 0x1A4E8000 bhash = 0x00000000 bpageno = (1:37037) 10 bdbid = 5 breferences = 0 bUse1 = 13476 11 bstat = 0x2c0000b blog = 0x212121bb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1A4E8000 17 18 m_pageId = (1:37037) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 20 m_objId (AllocUnitId.idObj) = 391 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063552512 22 Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 0 23 Metadata: ObjectId = 443148624 m_prevPage = (0:0) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 2 m_freeCnt = 6072 25 m_freeData = 2116 m_reservedCnt = 0 m_lsn = (2774:35:2) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:32352) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x0A33C060 39 40 41 42 Slot 0 Column 0 Offset 0x4 Length 4 43 44 id = 29 45 46 Slot 0 Column 1 Offset 0x8 Length 999 47 48 col2 = xxx 49 50 col3 = [NULL] 51 52 Slot 1 Offset 0x452 Length 1010 53 54 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 55 Memory Dump @0x0A33C452 56 57 58 59 Slot 1 Column 0 Offset 0x4 Length 4 60 61 id = 31 62 63 Slot 1 Column 1 Offset 0x8 Length 999 64 65 col2 = xxx 66 67 col3 = [NULL] 68 69 70 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
------------------------------------------------华丽的分割线------------------------------------------------------------
现在向聚集索引表ClusteredTable插入一条记录，插入id为30的记录
1 INSERT INTO [dbo].[ClusteredTable] ( [id], [col2], [col3] ) 2 SELECT 30,'xxx',''
1 --清空DBCCResult表数据 2 TRUNCATE TABLE [dbo].[DBCCResult] 3 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,ClusteredTable,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
可以看到新增加了一页37042
我们看一下聚集索引页14623
可以看到聚集索引页增加了一行
我们看一下37036页 ，可以看到该页本身存放8条记录，现在存放6条记录 m_slotCnt = 6
1 PAGE: (1:37036) 2 3 4 BUFFER: 5 6 7 BUF @0x03F6F5F4 8 9 bpage = 0x19CD0000 bhash = 0x00000000 bpageno = (1:37036) 10 bdbid = 5 breferences = 0 bUse1 = 14314 11 bstat = 0x2c0000b blog = 0x212121bb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x19CD0000 17 18 m_pageId = (1:37036) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0 20 m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063486976 22 Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1 23 Metadata: ObjectId = 427148567 m_prevPage = (1:14544) m_nextPage = (1:37042) 24 pminlen = 1007 m_slotCnt = 6 m_freeCnt = 2024 25 m_freeData = 8176 m_reservedCnt = 0 m_lsn = (2774:96:9) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x095CC060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 17 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 Slot 1 Offset 0x452 Length 1010 54 55 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 56 Memory Dump @0x095CC452 57 58 59 UNIQUIFIER = [NULL] 60 61 Slot 1 Column 1 Offset 0x4 Length 4 62 63 id = 19 64 65 Slot 1 Column 2 Offset 0x8 Length 999 66 67 col2 = xxx 68 69 col3 = [NULL] 70 71 Slot 2 Offset 0x844 Length 1010 72 73 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 74 Memory Dump @0x095CC844 75 76 77 UNIQUIFIER = [NULL] 78 79 Slot 2 Column 1 Offset 0x4 Length 4 80 81 id = 21 82 83 Slot 2 Column 2 Offset 0x8 Length 999 84 85 col2 = xxx 86 87 col3 = [NULL] 88 89 Slot 3 Offset 0xc36 Length 1010 90 91 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 92 Memory Dump @0x095CCC36 93 94 95 UNIQUIFIER = [NULL] 96 97 Slot 3 Column 1 Offset 0x4 Length 4 98 99 id = 23 100 101 Slot 3 Column 2 Offset 0x8 Length 999 102 103 col2 = xxx 104 105 col3 = [NULL] 106 107 Slot 4 Offset 0x1028 Length 1010 108 109 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 110 Memory Dump @0x095CD028 111 112 113 UNIQUIFIER = [NULL] 114 115 Slot 4 Column 1 Offset 0x4 Length 4 116 117 id = 25 118 119 Slot 4 Column 2 Offset 0x8 Length 999 120 121 col2 = xxx 122 123 col3 = [NULL] 124 125 Slot 5 Offset 0x141a Length 1010 126 127 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 128 Memory Dump @0x095CD41A 129 130 UNIQUIFIER = [NULL] 131 132 Slot 5 Column 1 Offset 0x4 Length 4 133 134 id = 27 135 136 Slot 5 Column 2 Offset 0x8 Length 999 137 138 col2 = xxx 139 140 col3 = [NULL] 141 142 143 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
我们看一下37042页，可以看到m_slotCnt = 3 ，该页面只有3条记录
1 PAGE: (1:37042) 2 3 4 BUFFER: 5 6 7 BUF @0x03F68738 8 9 bpage = 0x1997A000 bhash = 0x00000000 bpageno = (1:37042) 10 bdbid = 5 breferences = 0 bUse1 = 14543 11 bstat = 0x2c0000b blog = 0x21bbbbbb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1997A000 17 18 m_pageId = (1:37042) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0 20 m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063486976 22 Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1 23 Metadata: ObjectId = 427148567 m_prevPage = (1:37036) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 3 m_freeCnt = 5060 25 m_freeData = 3126 m_reservedCnt = 0 m_lsn = (2774:96:12) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x0A33C060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 29 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 Slot 1 Offset 0x844 Length 1010 54 55 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 56 Memory Dump @0x0A33C844 57 58 59 UNIQUIFIER = [NULL] 60 61 Slot 1 Column 1 Offset 0x4 Length 4 62 63 id = 30 64 65 Slot 1 Column 2 Offset 0x8 Length 999 66 67 col2 = xxx 68 69 col3 = [NULL] 70 71 Slot 2 Offset 0x452 Length 1010 72 73 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 74 Memory Dump @0x0A33C452 75 76 77 UNIQUIFIER = [NULL] 78 79 Slot 2 Column 1 Offset 0x4 Length 4 80 81 id = 31 82 83 Slot 2 Column 2 Offset 0x8 Length 999 84 85 col2 = xxx 86 87 col3 = [NULL] 88 89 90 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
原本一页可以容纳8条记录，现在37036页面只能容纳6条记录，造成还有2条记录的剩余空间没有利用，造成浪费
而且聚集索引页的主键列的id字段的范围值也打乱了，需要增加一行来保持索引键列的有序
其实这也属于页拆分的一种，详细可以看一下宋大侠的文章里面关于内部碎片和外部碎片的说法，这里就不讨论了
T-SQL查询高级—SQL Server索引中的碎片和填充因子
------------------------------------------------------------华丽的分割线-----------------------------------------------
其实这里插入到聚集索引表里的记录是会维护数据页和索引页使得id字段保持有序
id为30这条记录还是插在了id为31的这条记录前面
这样的话，插入一条记录的同时需要维护索引页和数据页所以开销是比较大的
--------------------------------------------------华丽的分割线----------------------------------------------
那么堆表是怎样处理数据的插入的呢？
现在向堆表[HeapTable]插入一条记录，插入id为26的记录
1 INSERT INTO [dbo].[HeapTable] ( [id], [col2], [col3] ) 2 SELECT 26,'xxx',''
1 --先清空[DBCCResult]的数据 2 TRUNCATE TABLE [dbo].[DBCCResult] 3 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,HeapTable,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
可以看到什么页面都没有增加
我们看一下数据页37037里的内容 可以看到m_slotCnt = 3 ，说明这个数据页比没有插入数据之前多了一条记录
1 PAGE: (1:37037) 2 3 4 BUFFER: 5 6 7 BUF @0x03F83ABC 8 9 bpage = 0x1A4E8000 bhash = 0x00000000 bpageno = (1:37037) 10 bdbid = 5 breferences = 0 bUse1 = 15670 11 bstat = 0x2c0000b blog = 0x212121bb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1A4E8000 17 18 m_pageId = (1:37037) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 20 m_objId (AllocUnitId.idObj) = 391 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063552512 22 Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 0 23 Metadata: ObjectId = 443148624 m_prevPage = (0:0) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 3 m_freeCnt = 5060 25 m_freeData = 3126 m_reservedCnt = 0 m_lsn = (2774:121:2) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:32352) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x0A33C060 39 40 41 42 Slot 0 Column 0 Offset 0x4 Length 4 43 44 id = 29 45 46 Slot 0 Column 1 Offset 0x8 Length 999 47 48 col2 = xxx 49 50 col3 = [NULL] 51 52 Slot 1 Offset 0x452 Length 1010 53 54 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 55 Memory Dump @0x0A33C452 56 57 58 59 Slot 1 Column 0 Offset 0x4 Length 4 60 61 id = 31 62 63 Slot 1 Column 1 Offset 0x8 Length 999 64 65 col2 = xxx 66 67 68 col3 = [NULL] 69 70 Slot 2 Offset 0x844 Length 1010 71 72 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 73 Memory Dump @0x0A33C844 74 75 76 77 Slot 2 Column 0 Offset 0x4 Length 4 78 79 id = 26 80 81 Slot 2 Column 1 Offset 0x8 Length 999 82 83 col2 = xxx 84 85 86 col3 = [NULL] 87 88 89 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
按道理，如果按照id字段的顺序插入记录的话，id为26的这条记录应该插入到数据页14545 里id为25的那条记录的后面，但是堆表的结果并非这样
他插入到了数据页37037的id为31的那条记录的后面
这说明堆表的记录插入只会插入到堆中的最后一个数据页里的最后，所以插入数据基本上没有开销
-------------------------------------------------华丽的分割线---------------------------------------------
而园子里有人针对聚集索引表作出下面讨论：
插入是会分配新的page，而不是在原来的有碎片的page里面插入数据
只有说经常更新行打小不固定的表的时候，碎片空隙保持一定的冗余，防止页拆分Shiite有一定好处的
不过我不认同您对数据插入一定会分配新的页的说法，当页内空间足够是不用分配新的页的。如果每插入一条数据分配一个新的页。那岂不是一个100W行的表会有100W页？那SQL Server可以退出历史舞台了.....
可以看到刚才我在聚集索引表里插入id为30的那条记录，结果造成分配了一个新的page，为什麽？？因为原本只能容纳8条记录的页面
不能再容纳新的记录了，只能够分配一个新的page，分配的时候也造成了页拆分
而插入是不是一定会分配新的page呢？
证明：证明的方法很简单，只需要在数据页37042里再插入一条记录，看会不会分配新的page就可以了
数据页37042现在有3条记录，id分别为29，30，31
现在插入id为32这条记录
1 INSERT INTO [dbo].[ClusteredTable] ( [id], [col2], [col3] ) 2 SELECT 32,'xxx',''
1 SELECT [index_id], [index_type_desc], [alloc_unit_type_desc], [index_depth], 2 [index_level], [avg_fragmentation_in_percent], [fragment_count], 3 [page_count] 4 FROM [sys].[dm_db_index_physical_stats](DB_ID('pratice'), 5 OBJECT_ID('pratice.dbo.ClusteredTable'), 6 NULL, NULL, NULL) 7 8 9 --先清空[DBCCResult]表的数据 10 TRUNCATE TABLE [dbo].[DBCCResult] 11 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,ClusteredTable,-1) ') 12 13 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
看一下数据页37042这个页面 m_slotCnt = 4 说明37042这个页面有4条记录
1 PAGE: (1:37042) 2 3 4 BUFFER: 5 6 7 BUF @0x03F68738 8 9 bpage = 0x1997A000 bhash = 0x00000000 bpageno = (1:37042) 10 bdbid = 5 breferences = 0 bUse1 = 16744 11 bstat = 0x2c0000b blog = 0x21bbbbbb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1997A000 17 18 m_pageId = (1:37042) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 20 m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063486976 22 Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1 23 Metadata: ObjectId = 427148567 m_prevPage = (1:37036) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 4 m_freeCnt = 4048 25 m_freeData = 4136 m_reservedCnt = 0 m_lsn = (2774:138:2) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x0A33C060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 29 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 Slot 1 Offset 0x844 Length 1010 54 55 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 56 Memory Dump @0x0A33C844 57 58 59 UNIQUIFIER = [NULL] 60 61 Slot 1 Column 1 Offset 0x4 Length 4 62 63 id = 30 64 65 Slot 1 Column 2 Offset 0x8 Length 999 66 67 col2 = xxx 68 69 70 col3 = [NULL] 71 72 Slot 2 Offset 0x452 Length 1010 73 74 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 75 Memory Dump @0x0A33C452 76 77 78 UNIQUIFIER = [NULL] 79 80 Slot 2 Column 1 Offset 0x4 Length 4 81 82 id = 31 83 84 Slot 2 Column 2 Offset 0x8 Length 999 85 86 col2 = xxx 87 88 col3 = [NULL] 89 90 Slot 3 Offset 0xc36 Length 1010 91 92 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 93 Memory Dump @0x0A33CC36 94 95 96 UNIQUIFIER = [NULL] 97 98 Slot 3 Column 1 Offset 0x4 Length 4 99 100 id = 32 101 102 Slot 3 Column 2 Offset 0x8 Length 999 103 104 col2 = xxx 105 106 col3 = [NULL] 107 108 109 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
现在我们插入6条记录，让数据超过数据页面本身的承载能力，让他分配新的数据页
1 INSERT INTO [dbo].[ClusteredTable] ( [id], [col2], [col3] ) 2 SELECT 33,'xxx','' UNION ALL 3 SELECT 34,'xxx','' UNION ALL 4 SELECT 35,'xxx','' UNION ALL 5 SELECT 36,'xxx','' UNION ALL 6 SELECT 37,'xxx',''
聚集索引页面
从上面两张图可以看到,分配了一个新的数据页14537,因为数据页37042已经超过8条记录了,所以必须要分配一个新的数据页,同时聚集索引页也会增加一条记录
数据页14537的内容,m_slotCnt = 1 只有一条记录
1 PAGE: (1:14537) 2 3 4 BUFFER: 5 6 7 BUF @0x03F79EE0 8 9 bpage = 0x1A16E000 bhash = 0x00000000 bpageno = (1:14537) 10 bdbid = 5 breferences = 0 bUse1 = 17088 11 bstat = 0x2c0000b blog = 0x159bbbbb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1A16E000 17 18 m_pageId = (1:14537) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0xc000 20 m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063486976 22 Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1 23 Metadata: ObjectId = 427148567 m_prevPage = (1:37042) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 1 m_freeCnt = 7084 25 m_freeData = 1106 m_reservedCnt = 0 m_lsn = (2774:212:10) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x095CC060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 37 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 54 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
堆表跟聚集索引表一样,当数据页面不能承载页面本身的数据量的时候就会分配一个新的数据页,由于篇幅关系,这里就不测试了
-----------------------------------------------------华丽的分割线------------------------------------------------------------
那么重组索引/重建索引对于上面解决那个页拆分有没有帮助呢? 对于数据页37036，页面数据还没有填满的情况
我们先使用重组索引
其实对于数据页37036的情况，我们也可以理解为在数据页37036里删除了两条记录，使得数据页37036里的记录数不足8条
很多书本上说，如果表空间还剩下很多的话，最好在表上建立一个聚集索引，然后重组索引，重组索引之后一些数据页面剩余的页面空间
就可以利用起来
先重组一下索引
1 USE [pratice] 2 GO 3 ALTER INDEX [CIX] ON ClusteredTable REORGANIZE
我们看一下重组索引之后,数据页和聚集索引页面变成什么样子了？？？
1 --先清空[DBCCResult]表的数据 2 TRUNCATE TABLE [dbo].[DBCCResult] 3 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,ClusteredTable,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
聚集索引页面
可以看到数据页14537这个页面不见了，聚集索引页的索引键列（id）的范围值重新排序了
因为刚才在数据页37036里遭遇了页拆分，所以这里只需要查看数据页37036和数据页37042里的内容就可以了
数据页37036，可以看到 m_slotCnt = 8 ，数据页37036里的记录又填充满了，id的范围从17~30
1 PAGE: (1:37036) 2 3 4 BUFFER: 5 6 7 BUF @0x03F6F5F4 8 9 bpage = 0x19CD0000 bhash = 0x00000000 bpageno = (1:37036) 10 bdbid = 5 breferences = 0 bUse1 = 18572 11 bstat = 0x2c0000b blog = 0x212121bb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x19CD0000 17 18 m_pageId = (1:37036) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 20 m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063486976 22 Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1 23 Metadata: ObjectId = 427148567 m_prevPage = (1:14544) m_nextPage = (1:37042) 24 pminlen = 1007 m_slotCnt = 8 m_freeCnt = 0 25 m_freeData = 8176 m_reservedCnt = 0 m_lsn = (2774:232:3) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x095CC060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 17 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 Slot 1 Offset 0x452 Length 1010 54 55 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 56 Memory Dump @0x095CC452 57 58 59 UNIQUIFIER = [NULL] 60 61 Slot 1 Column 1 Offset 0x4 Length 4 62 63 id = 19 64 65 Slot 1 Column 2 Offset 0x8 Length 999 66 67 col2 = xxx 68 69 col3 = [NULL] 70 71 Slot 2 Offset 0x844 Length 1010 72 73 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 74 Memory Dump @0x095CC844 75 76 77 UNIQUIFIER = [NULL] 78 79 Slot 2 Column 1 Offset 0x4 Length 4 80 81 id = 21 82 83 Slot 2 Column 2 Offset 0x8 Length 999 84 85 col2 = xxx 86 87 col3 = [NULL] 88 89 Slot 3 Offset 0xc36 Length 1010 90 91 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 92 Memory Dump @0x095CCC36 93 94 95 UNIQUIFIER = [NULL] 96 97 Slot 3 Column 1 Offset 0x4 Length 4 98 99 id = 23 100 101 Slot 3 Column 2 Offset 0x8 Length 999 102 103 col2 = xxx 104 105 col3 = [NULL] 106 107 Slot 4 Offset 0x1028 Length 1010 108 109 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 110 Memory Dump @0x095CD028 111 112 113 UNIQUIFIER = [NULL] 114 115 Slot 4 Column 1 Offset 0x4 Length 4 116 117 id = 25 118 119 Slot 4 Column 2 Offset 0x8 Length 999 120 121 col2 = xxx 122 123 col3 = [NULL] 124 125 Slot 5 Offset 0x141a Length 1010 126 127 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 128 Memory Dump @0x095CD41A 129 130 131 UNIQUIFIER = [NULL] 132 133 Slot 5 Column 1 Offset 0x4 Length 4 134 135 id = 27 136 137 Slot 5 Column 2 Offset 0x8 Length 999 138 139 col2 = xxx 140 141 col3 = [NULL] 142 143 Slot 6 Offset 0x180c Length 1010 144 145 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 146 Memory Dump @0x095CD80C 147 148 149 UNIQUIFIER = [NULL] 150 151 Slot 6 Column 1 Offset 0x4 Length 4 152 153 id = 29 154 155 Slot 6 Column 2 Offset 0x8 Length 999 156 157 col2 = xxx 158 159 col3 = [NULL] 160 161 Slot 7 Offset 0x1bfe Length 1010 162 163 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 164 Memory Dump @0x095CDBFE 165 166 167 UNIQUIFIER = [NULL] 168 169 Slot 7 Column 1 Offset 0x4 Length 4 170 171 id = 30 172 173 Slot 7 Column 2 Offset 0x8 Length 999 174 175 col2 = xxx 176 177 col3 = [NULL] 178 179 180 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
数据页37042，可以看到 m_slotCnt = 7，id的范围从31~37
1 PAGE: (1:37042) 2 3 4 BUFFER: 5 6 7 BUF @0x03F68738 8 9 bpage = 0x1997A000 bhash = 0x00000000 bpageno = (1:37042) 10 bdbid = 5 breferences = 0 bUse1 = 18756 11 bstat = 0x2c0000b blog = 0x21bbbbbb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1997A000 17 18 m_pageId = (1:37042) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000 20 m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063486976 22 Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1 23 Metadata: ObjectId = 427148567 m_prevPage = (1:37036) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 7 m_freeCnt = 1012 25 m_freeData = 7166 m_reservedCnt = 0 m_lsn = (2774:232:12) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x095CC060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 31 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 Slot 1 Offset 0x452 Length 1010 54 55 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 56 Memory Dump @0x095CC452 57 58 59 UNIQUIFIER = [NULL] 60 61 Slot 1 Column 1 Offset 0x4 Length 4 62 63 id = 32 64 65 Slot 1 Column 2 Offset 0x8 Length 999 66 67 col2 = xxx 68 69 col3 = [NULL] 70 71 Slot 2 Offset 0x844 Length 1010 72 73 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 74 Memory Dump @0x095CC844 75 76 77 UNIQUIFIER = [NULL] 78 79 Slot 2 Column 1 Offset 0x4 Length 4 80 81 id = 33 82 83 Slot 2 Column 2 Offset 0x8 Length 999 84 85 col2 = xxx 86 87 col3 = [NULL] 88 89 Slot 3 Offset 0xc36 Length 1010 90 91 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 92 Memory Dump @0x095CCC36 93 94 95 UNIQUIFIER = [NULL] 96 97 Slot 3 Column 1 Offset 0x4 Length 4 98 99 id = 34 100 101 Slot 3 Column 2 Offset 0x8 Length 999 102 103 col2 = xxx 104 105 col3 = [NULL] 106 107 Slot 4 Offset 0x1028 Length 1010 108 109 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 110 Memory Dump @0x095CD028 111 112 113 UNIQUIFIER = [NULL] 114 115 Slot 4 Column 1 Offset 0x4 Length 4 116 117 id = 35 118 119 Slot 4 Column 2 Offset 0x8 Length 999 120 121 col2 = xxx 122 123 col3 = [NULL] 124 125 Slot 5 Offset 0x141a Length 1010 126 127 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 128 Memory Dump @0x095CD41A 129 130 131 UNIQUIFIER = [NULL] 132 133 Slot 5 Column 1 Offset 0x4 Length 4 134 135 id = 36 136 137 Slot 5 Column 2 Offset 0x8 Length 999 138 139 col2 = xxx 140 141 col3 = [NULL] 142 143 Slot 6 Offset 0x180c Length 1010 144 145 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 146 Memory Dump @0x095CD80C 147 148 149 UNIQUIFIER = [NULL] 150 151 Slot 6 Column 1 Offset 0x4 Length 4 152 153 id = 37 154 155 Slot 6 Column 2 Offset 0x8 Length 999 156 157 col2 = xxx 158 159 col3 = [NULL] 160 161 162 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
重组索引之后无论是数据页还是索引页，里面的数据都按照id列的顺序填充满并排好序了
那么“重组索引之后一些数据页面剩余的页面空间就可以利用起来”是真的
------------------------------------------------------------华丽的分割线--------------------------------------------------------
那么重建索引呢？？跟重组索引有什么区别？？
先drop掉ClusteredTable表，然后又创建ClusteredTable表，插入数据，建表和建索引的步骤跟文章开头一样，这里就不写了
按照前面的做法，令第二个数据页面只剩下两条记录没有填满，这里由于篇幅关系就不写了，文章开头都有步骤
下面这幅图是我插入了id为30的记录之后，ClusteredTable表中存在的索引页和数据页，可以看到增加了一个数据页37036
可以看到数据页37036里的内容， m_slotCnt = 3 记录有3条，id分别为：29，30，31
1 PAGE: (1:37036) 2 3 4 BUFFER: 5 6 7 BUF @0x03F6F5F4 8 9 bpage = 0x19CD0000 bhash = 0x00000000 bpageno = (1:37036) 10 bdbid = 5 breferences = 0 bUse1 = 19590 11 bstat = 0x2c0000b blog = 0x21bbbbbb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x19CD0000 17 18 m_pageId = (1:37036) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0 20 m_objId (AllocUnitId.idObj) = 395 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063814656 22 Metadata: PartitionId = 72057594054574080 Metadata: IndexId = 1 23 Metadata: ObjectId = 491148795 m_prevPage = (1:14544) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 3 m_freeCnt = 5060 25 m_freeData = 3126 m_reservedCnt = 0 m_lsn = (2775:67:32) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x095CC060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 29 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 Slot 1 Offset 0x844 Length 1010 54 55 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 56 Memory Dump @0x095CC844 57 58 59 UNIQUIFIER = [NULL] 60 61 Slot 1 Column 1 Offset 0x4 Length 4 62 63 id = 30 64 65 Slot 1 Column 2 Offset 0x8 Length 999 66 67 col2 = xxx 68 69 col3 = [NULL] 70 71 Slot 2 Offset 0x452 Length 1010 72 73 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 74 Memory Dump @0x095CC452 75 76 77 UNIQUIFIER = [NULL] 78 79 Slot 2 Column 1 Offset 0x4 Length 4 80 81 id = 31 82 83 Slot 2 Column 2 Offset 0x8 Length 999 84 85 col2 = xxx 86 87 col3 = [NULL] 88 89 90 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
再插入6条记录
1 INSERT INTO [dbo].[ClusteredTable] ( [id], [col2], [col3] ) 2 SELECT 33,'xxx','' UNION ALL 3 SELECT 34,'xxx','' UNION ALL 4 SELECT 35,'xxx','' UNION ALL 5 SELECT 36,'xxx','' UNION ALL 6 SELECT 37,'xxx','' UNION ALL 7 SELECT 38,'xxx',''
可以看到多了一个数据页面14539
数据页面14539只有一条记录，m_slotCnt = 1
1 PAGE: (1:14539) 2 3 4 BUFFER: 5 6 7 BUF @0x03F79CCC 8 9 bpage = 0x1A17C000 bhash = 0x00000000 bpageno = (1:14539) 10 bdbid = 5 breferences = 0 bUse1 = 20082 11 bstat = 0x2c0000b blog = 0x212159bb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1A17C000 17 18 m_pageId = (1:14539) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0xc000 20 m_objId (AllocUnitId.idObj) = 395 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063814656 22 Metadata: PartitionId = 72057594054574080 Metadata: IndexId = 1 23 Metadata: ObjectId = 491148795 m_prevPage = (1:37036) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 1 m_freeCnt = 7084 25 m_freeData = 1106 m_reservedCnt = 0 m_lsn = (2775:120:10) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x095CC060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 38 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 54 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
现在来重建一下索引
1 USE [pratice] 2 GO 3 ALTER INDEX [CIX] ON ClusteredTable REBUILD
我们看一下重组索引之后,数据页和聚集索引页面变成什么样子了？？？
数据页14539这个页面不见了，更重要的是原本4个数据页面现在只有3个，还有大家留意一下索引页和数据页的pageid，跟之前完全不一样
我们看一下聚集索引页面和数据页面有什么变化
聚集索引页面
数据页14623， m_slotCnt = 8
1 PAGE: (1:14623) 2 3 4 BUFFER: 5 6 7 BUF @0x03F692CC 8 9 bpage = 0x19A0E000 bhash = 0x00000000 bpageno = (1:14623) 10 bdbid = 5 breferences = 0 bUse1 = 20925 11 bstat = 0x2c0000b blog = 0x21bbbbcb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x19A0E000 17 18 m_pageId = (1:14623) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4 20 m_objId (AllocUnitId.idObj) = 396 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063880192 22 Metadata: PartitionId = 72057594054639616 Metadata: IndexId = 1 23 Metadata: ObjectId = 491148795 m_prevPage = (0:0) m_nextPage = (1:37042) 24 pminlen = 1007 m_slotCnt = 8 m_freeCnt = 0 25 m_freeData = 8176 m_reservedCnt = 0 m_lsn = (2775:140:33) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x095CC060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 1 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 Slot 1 Offset 0x452 Length 1010 54 55 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 56 Memory Dump @0x095CC452 57 58 59 UNIQUIFIER = [NULL] 60 61 Slot 1 Column 1 Offset 0x4 Length 4 62 63 id = 3 64 65 Slot 1 Column 2 Offset 0x8 Length 999 66 67 col2 = xxx 68 69 col3 = [NULL] 70 71 Slot 2 Offset 0x844 Length 1010 72 73 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 74 Memory Dump @0x095CC844 75 76 77 UNIQUIFIER = [NULL] 78 79 Slot 2 Column 1 Offset 0x4 Length 4 80 81 id = 5 82 83 Slot 2 Column 2 Offset 0x8 Length 999 84 85 col2 = xxx 86 87 col3 = [NULL] 88 89 Slot 3 Offset 0xc36 Length 1010 90 91 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 92 Memory Dump @0x095CCC36 93 94 95 UNIQUIFIER = [NULL] 96 97 Slot 3 Column 1 Offset 0x4 Length 4 98 99 id = 7 100 101 Slot 3 Column 2 Offset 0x8 Length 999 102 103 col2 = xxx 104 105 col3 = [NULL] 106 107 Slot 4 Offset 0x1028 Length 1010 108 109 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 110 Memory Dump @0x095CD028 111 112 113 UNIQUIFIER = [NULL] 114 115 Slot 4 Column 1 Offset 0x4 Length 4 116 117 id = 9 118 119 Slot 4 Column 2 Offset 0x8 Length 999 120 121 col2 = xxx 122 123 col3 = [NULL] 124 125 Slot 5 Offset 0x141a Length 1010 126 127 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 128 Memory Dump @0x095CD41A 129 130 131 UNIQUIFIER = [NULL] 132 133 Slot 5 Column 1 Offset 0x4 Length 4 134 135 id = 11 136 137 Slot 5 Column 2 Offset 0x8 Length 999 138 139 col2 = xxx 140 141 col3 = [NULL] 142 143 Slot 6 Offset 0x180c Length 1010 144 145 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 146 Memory Dump @0x095CD80C 147 148 149 UNIQUIFIER = [NULL] 150 151 Slot 6 Column 1 Offset 0x4 Length 4 152 153 id = 13 154 155 Slot 6 Column 2 Offset 0x8 Length 999 156 157 col2 = xxx 158 159 col3 = [NULL] 160 161 Slot 7 Offset 0x1bfe Length 1010 162 163 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 164 Memory Dump @0x095CDBFE 165 166 167 UNIQUIFIER = [NULL] 168 169 Slot 7 Column 1 Offset 0x4 Length 4 170 171 id = 15 172 173 Slot 7 Column 2 Offset 0x8 Length 999 174 175 col2 = xxx 176 177 col3 = [NULL] 178 179 180 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
数据页37042， m_slotCnt = 8
1 PAGE: (1:37042) 2 3 4 BUFFER: 5 6 7 BUF @0x03F68738 8 9 bpage = 0x1997A000 bhash = 0x00000000 bpageno = (1:37042) 10 bdbid = 5 breferences = 0 bUse1 = 21031 11 bstat = 0x2c0000b blog = 0xbbbbbbcb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1997A000 17 18 m_pageId = (1:37042) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4 20 m_objId (AllocUnitId.idObj) = 396 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063880192 22 Metadata: PartitionId = 72057594054639616 Metadata: IndexId = 1 23 Metadata: ObjectId = 491148795 m_prevPage = (1:14623) m_nextPage = (1:13572) 24 pminlen = 1007 m_slotCnt = 8 m_freeCnt = 0 25 m_freeData = 8176 m_reservedCnt = 0 m_lsn = (2775:140:42) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x095CC060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 17 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 Slot 1 Offset 0x452 Length 1010 54 55 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 56 Memory Dump @0x095CC452 57 58 59 UNIQUIFIER = [NULL] 60 61 Slot 1 Column 1 Offset 0x4 Length 4 62 63 id = 19 64 65 Slot 1 Column 2 Offset 0x8 Length 999 66 67 col2 = xxx 68 69 col3 = [NULL] 70 71 Slot 2 Offset 0x844 Length 1010 72 73 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 74 Memory Dump @0x095CC844 75 76 77 UNIQUIFIER = [NULL] 78 79 Slot 2 Column 1 Offset 0x4 Length 4 80 81 id = 21 82 83 Slot 2 Column 2 Offset 0x8 Length 999 84 85 col2 = xxx 86 87 col3 = [NULL] 88 89 Slot 3 Offset 0xc36 Length 1010 90 91 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 92 Memory Dump @0x095CCC36 93 94 95 UNIQUIFIER = [NULL] 96 97 Slot 3 Column 1 Offset 0x4 Length 4 98 99 id = 23 100 101 Slot 3 Column 2 Offset 0x8 Length 999 102 103 col2 = xxx 104 105 col3 = [NULL] 106 107 Slot 4 Offset 0x1028 Length 1010 108 109 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 110 Memory Dump @0x095CD028 111 112 113 UNIQUIFIER = [NULL] 114 115 Slot 4 Column 1 Offset 0x4 Length 4 116 117 id = 25 118 119 Slot 4 Column 2 Offset 0x8 Length 999 120 121 col2 = xxx 122 123 col3 = [NULL] 124 125 Slot 5 Offset 0x141a Length 1010 126 127 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 128 Memory Dump @0x095CD41A 129 130 131 UNIQUIFIER = [NULL] 132 133 Slot 5 Column 1 Offset 0x4 Length 4 134 135 id = 27 136 137 Slot 5 Column 2 Offset 0x8 Length 999 138 139 col2 = xxx 140 141 col3 = [NULL] 142 143 Slot 6 Offset 0x180c Length 1010 144 145 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 146 Memory Dump @0x095CD80C 147 148 149 UNIQUIFIER = [NULL] 150 151 Slot 6 Column 1 Offset 0x4 Length 4 152 153 id = 29 154 155 Slot 6 Column 2 Offset 0x8 Length 999 156 157 col2 = xxx 158 159 col3 = [NULL] 160 161 Slot 7 Offset 0x1bfe Length 1010 162 163 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 164 Memory Dump @0x095CDBFE 165 166 167 UNIQUIFIER = [NULL] 168 169 Slot 7 Column 1 Offset 0x4 Length 4 170 171 id = 30 172 173 Slot 7 Column 2 Offset 0x8 Length 999 174 175 col2 = xxx 176 177 col3 = [NULL] 178 179 180 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
数据页14623， m_slotCnt = 7
1 PAGE: (1:13572) 2 3 4 BUFFER: 5 6 7 BUF @0x03F369D0 8 9 bpage = 0x1845C000 bhash = 0x00000000 bpageno = (1:13572) 10 bdbid = 5 breferences = 0 bUse1 = 21131 11 bstat = 0x3c0000b blog = 0x159bbbcb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1845C000 17 18 m_pageId = (1:13572) m_headerVersion = 1 m_type = 1 19 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4 20 m_objId (AllocUnitId.idObj) = 396 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594063880192 22 Metadata: PartitionId = 72057594054639616 Metadata: IndexId = 1 23 Metadata: ObjectId = 491148795 m_prevPage = (1:37042) m_nextPage = (0:0) 24 pminlen = 1007 m_slotCnt = 7 m_freeCnt = 1012 25 m_freeData = 7166 m_reservedCnt = 0 m_lsn = (2775:140:44) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 Slot 0 Offset 0x60 Length 1010 36 37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38 Memory Dump @0x095CC060 39 40 41 UNIQUIFIER = [NULL] 42 43 Slot 0 Column 1 Offset 0x4 Length 4 44 45 id = 31 46 47 Slot 0 Column 2 Offset 0x8 Length 999 48 49 col2 = xxx 50 51 col3 = [NULL] 52 53 Slot 1 Offset 0x452 Length 1010 54 55 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 56 Memory Dump @0x095CC452 57 58 59 UNIQUIFIER = [NULL] 60 61 Slot 1 Column 1 Offset 0x4 Length 4 62 63 id = 33 64 65 Slot 1 Column 2 Offset 0x8 Length 999 66 67 col2 = xxx 68 69 col3 = [NULL] 70 71 Slot 2 Offset 0x844 Length 1010 72 73 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 74 Memory Dump @0x095CC844 75 76 77 UNIQUIFIER = [NULL] 78 79 Slot 2 Column 1 Offset 0x4 Length 4 80 81 id = 34 82 83 Slot 2 Column 2 Offset 0x8 Length 999 84 85 col2 = xxx 86 87 col3 = [NULL] 88 89 Slot 3 Offset 0xc36 Length 1010 90 91 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 92 Memory Dump @0x095CCC36 93 94 95 UNIQUIFIER = [NULL] 96 97 Slot 3 Column 1 Offset 0x4 Length 4 98 99 id = 35 100 101 Slot 3 Column 2 Offset 0x8 Length 999 102 103 col2 = xxx 104 105 col3 = [NULL] 106 107 Slot 4 Offset 0x1028 Length 1010 108 109 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 110 Memory Dump @0x095CD028 111 112 113 UNIQUIFIER = [NULL] 114 115 Slot 4 Column 1 Offset 0x4 Length 4 116 117 id = 36 118 119 Slot 4 Column 2 Offset 0x8 Length 999 120 121 col2 = xxx 122 123 col3 = [NULL] 124 125 Slot 5 Offset 0x141a Length 1010 126 127 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 128 Memory Dump @0x095CD41A 129 130 131 UNIQUIFIER = [NULL] 132 133 Slot 5 Column 1 Offset 0x4 Length 4 134 135 id = 37 136 137 Slot 5 Column 2 Offset 0x8 Length 999 138 139 col2 = xxx 140 141 col3 = [NULL] 142 143 Slot 6 Offset 0x180c Length 1010 144 145 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 146 Memory Dump @0x095CD80C 147 148 149 UNIQUIFIER = [NULL] 150 151 Slot 6 Column 1 Offset 0x4 Length 4 152 153 id = 38 154 155 Slot 6 Column 2 Offset 0x8 Length 999 156 157 col2 = xxx 158 159 col3 = [NULL] 160 161 162 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。View Code
从上面数据页面和聚集索引页面的结果看，重建索引和重组索引基本上是一样的效果，只是，重建索引会把索引页和数据页删除，然后分配新的索引页和数据页
所以重建索引开销还是比较大的
这里宋大侠有一个建议：
当[dm_db_index_physical_stats]DMV里的[avg_fragmentation_in_percent]字段大于30%的时候使用索引重建
当[avg_fragmentation_in_percent]等于小于30%的时候使用索引重组
[avg_fragmentation_in_percent]>30%就重建索引
[avg_fragmentation_in_percent]<=30%就重组索引
1 SELECT [avg_fragmentation_in_percent] 2 FROM [sys].[dm_db_index_physical_stats](DB_ID('pratice'), 3 OBJECT_ID('pratice.dbo.ClusteredTable'), 4 NULL, NULL, NULL)
---------------------------------------------------华丽的分割线--------------------------------------------
文章写完了，本人知道还有不足的地方
还是那一句：
如有不对的地方，欢迎强烈拍砖哦o(∩_∩)o
相关文章
