根据《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 )执行下面语句，看一下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 输出了错误信息，请与系统管理员联系。数据页 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 输出了错误信息，请与系统管理员联系。
---------------------------------------------------------华丽的分割线------------------------------------------------
我们创建一个堆表以作为对比
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 输出了错误信息，请与系统管理员联系。看一下数据页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 输出了错误信息，请与系统管理员联系。看一下数据页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 输出了错误信息，请与系统管理员联系。------------------------------------------------华丽的分割线------------------------------------------------------------
现在向聚集索引表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 输出了错误信息，请与系统管理员联系。我们看一下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 输出了错误信息，请与系统管理员联系。原本一页可以容纳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 输出了错误信息，请与系统管理员联系。按道理，如果按照id字段的顺序插入记录的话，id为26的这条记录应该插入到数据页14545 里id为25的那条记录的后面，但是堆表的结果并非这样
他插入到了数据页37037的id为31的那条记录的后面
这说明堆表的记录插入只会插入到堆中的最后一个数据页里的最后，所以插入数据基本上没有开销
-------------------------------------------------华丽的分割线---------------------------------------------
而园子里有人针对聚集索引表作出下面讨论：
插入是会分配新的page，而不是在原来的有碎片的page里面插入数据
只有说经常更新行打小不固定的表的时候，碎片空隙保持一定的冗余，防止页拆分Splite有一定好处的
不过我不认同您对数据插入一定会分配新的页的说法，当页内空间足够是不用分配新的页的。如果每插入一条数据分配一个新的页。那岂不是一个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 输出了错误信息，请与系统管理员联系。现在我们插入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 输出了错误信息，请与系统管理员联系。堆表跟聚集索引表一样,当数据页面不能承载页面本身的数据量的时候就会分配一个新的数据页,由于篇幅关系,这里就不测试了
-----------------------------------------------------华丽的分割线------------------------------------------------------------
那么重组索引/重建索引对于上面解决那个页拆分有没有帮助呢? 对于数据页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 输出了错误信息，请与系统管理员联系。
数据页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 输出了错误信息，请与系统管理员联系。
重组索引之后无论是数据页还是索引页，里面的数据都按照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 输出了错误信息，请与系统管理员联系。再插入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 输出了错误信息，请与系统管理员联系。
现在来重建一下索引
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 输出了错误信息，请与系统管理员联系。数据页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 输出了错误信息，请与系统管理员联系。数据页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 输出了错误信息，请与系统管理员联系。
从上面数据页面和聚集索引页面的结果看，重建索引和重组索引基本上是一样的效果，只是，重建索引会把索引页和数据页删除，然后分配新的索引页和数据页
所以重建索引开销还是比较大的
但是重建索引是不是一定比重组索引的开销大呢？
本人觉得：相比重组索引，重建索引是分配新的数据页和索引页，把旧的数据页里的数据按索引字段 排好序，再放进去新的数据页里
而重组索引是把旧的数据页里的数据有页拆分的页面填充满，这样会造成数据页之间的数据移动，效率可能会比重建索引差一点
大家可以使用SET STATISTICS TIME ON来统计一下重组索引和重建索引所用的时间，由于篇幅问题我这里就不测试了o(∩_∩)o
1 SET STATISTICS TIME ON
2 USE [pratice]
3 GO
4 ALTER INDEX [CIX] ON ClusteredTable REBUILD
5 ---------------------------------------------------
6 SET STATISTICS TIME ON
7 USE [pratice]
8 GO
9 ALTER INDEX [CIX] ON ClusteredTable REORGANIZE
----------------------------------------------------------------------------------------------
这里宋大侠有一个建议：
当[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
-----------------------------------------------------
2013-7-25 补充
我把堆表HeapTable里的数据页37037里的3条记录全部删除，现在数据页37037里一条记录都没有但是数据页37037并没有释放掉
1 DELETE FROM [dbo].[HeapTable] WHERE [id] IN(29,31,26)
1 PAGE: (1:37037)
2
3
4 BUFFER:
5
6
7 BUF @0x03E5E684
8
9 bpage = 0x19B4C000 bhash = 0x00000000 bpageno = (1:37037)
10 bdbid = 5 breferences = 0 bUse1 = 4284
11 bstat = 0x1c0000b blog = 0x3212159 bnext = 0x00000000
12
13 PAGE HEADER:
14
15
16 Page @0x19B4C000
17
18 m_pageId = (1:37037) m_headerVersion = 1 m_type = 1
19 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8008
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 = 1 m_freeCnt = 8094
25 m_freeData = 3126 m_reservedCnt = 1010 m_lsn = (2776:34:2)
26 m_xactReserved = 1010 m_xdesId = (0:6906025) m_ghostRecCnt = 0
27 m_tornBits = -1598612873
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
36 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。在徐海蔚老师的《SQLSERVER企业级平台管理》第26页里说到：
我delete了数据，SQLSERVER却没有完全释放空间，这不是空间泄漏吗？久而久之，我的数据库里会不会充斥着这些“没用”的页面，
把我的空间都浪费掉了？这倒不必担心，虽然这些页面没有被释放掉，但当表格里插入新的数据时，这些页面是会被重新使用的。
所以这些页面并没有被“泄漏”掉，会留给SQLSERVER重用的
如果真的看着这些页面碍事，而表格又不能整个删除掉，处理起来有时候倒有点费事。如果表格有聚集索引，重建/重组一下索引就能
释放这些页面，还是挺简单的。但是如果没有，可能就要重建一张新表，把数据从旧表里倒过去，然后再删除旧表，释放空间；或者
在这张表上建立一个聚集索引。所以，如果表没有用了，就直接删除它；如果表还有用，那这些页面将来再有新数据插入时，还会被利用上的。
一般没有必要一定要逼着SQLSERVER把他们释放出来
现在我再向HeapTable表插入2条记录，看数据页37037能不能被重新利用
1 INSERT INTO [dbo].[HeapTable] ( [id], [col2], [col3] )
2 SELECT 40,'xxx','' UNION ALL
3 SELECT 41,'xxx','' 可以看到id为40和id为41这两条记录被插入到数据页37037中里,数据页37037又被重新利用了o(∩_∩)o
1 PAGE: (1:37037)
2
3
4 BUFFER:
5
6
7 BUF @0x03E5E684
8
9 bpage = 0x19B4C000 bhash = 0x00000000 bpageno = (1:37037)
10 bdbid = 5 breferences = 0 bUse1 = 5362
11 bstat = 0x1c0000b blog = 0x3212159 bnext = 0x00000000
12
13 PAGE HEADER:
14
15
16 Page @0x19B4C000
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 = 5146 m_reservedCnt = 0 m_lsn = (2776:51:4)
26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
27 m_tornBits = -1598612873
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 0xc36 Length 1010
36
37 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
38 Memory Dump @0x09DACC36
39
40
41 Slot 0 Column 0 Offset 0x4 Length 4
42
43 id = 40
44
45 Slot 0 Column 1 Offset 0x8 Length 999
46
47 col2 = xxx
48
49 col3 = [NULL]
50
51 Slot 1 Offset 0x1028 Length 1010
52
53 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
54 Memory Dump @0x09DAD028
55
56
57
58 Slot 1 Column 0 Offset 0x4 Length 4
59
60 id = 41
61
62 Slot 1 Column 1 Offset 0x8 Length 999
63
64 col2 = xxx
65
66 col3 = [NULL]
67
68
69 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-----------------------------------------------------
2013-8-17 补充：
注意：这里的槽号显示的顺序并不一定就是物理存储的顺序,
也就是说Slot 0（表中的数据行第一行） 并不一定是物理存储的第一条,即SLOT 0对应的行偏移量不一定是最小的.
就像下面的Slot1和Slot2，Slot1的行偏移量是Ox844，转换为十进制是2116
Slot2的行偏移量是Ox452，转换为十进制是1106
所以第三行（Slot2）存储的位置比第二行（Slot1）排在前面物理上
---------------------------------------------------------------------------------------------------
2013-10-19 补充
当我们删除了表中的记录之后，实际上SQLSERVER没有释放数据页，而且也没有删除表中的记录
只是将删除的记录标记为已删除
我们做下面实验：
drop掉HeapTable表
1 USE [pratice]
2 GO
3
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
18
19 SELECT * FROM [dbo].[HeapTable]
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 
我们看一下13571这个页面
1 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
2
3 PAGE: (1:13517)
4
5
6 BUFFER:
7
8
9 BUF @0x03E5ED24
10
11 bpage = 0x196B8000 bhash = 0x00000000 bpageno = (1:13517)
12 bdbid = 5 breferences = 0 bUse1 = 49895
13 bstat = 0x3c0000b blog = 0x2159bbbb bnext = 0x00000000
14
15 PAGE HEADER:
16
17
18 Page @0x196B8000
19
20 m_pageId = (1:13517) m_headerVersion = 1 m_type = 1
21 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
22 m_objId (AllocUnitId.idObj) = 622 m_indexId (AllocUnitId.idInd) = 256
23 Metadata: AllocUnitId = 72057594078691328
24 Metadata: PartitionId = 72057594061783040 Metadata: IndexId = 0
25 Metadata: ObjectId = 196195749 m_prevPage = (0:0) m_nextPage = (0:0)
26 pminlen = 1007 m_slotCnt = 2 m_freeCnt = 6072
27 m_freeData = 2116 m_reservedCnt = 0 m_lsn = (3047:25268:2)
28 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
29 m_tornBits = 0
30
31 Allocation Status
32
33 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
34 PFS (1:8088) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
35 ML (1:7) = NOT MIN_LOGGED
36
37 Slot 0 Offset 0x60 Length 1010
38
39 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
40 Memory Dump @0x0A70C060
41
42 00000000: 1000ef03 1d000000 78787820 20202020 †........xxx
43 00000010: 20202020 20202020 20202020 20202020 †
44 00000020: 20202020 20202020 20202020 20202020 †
45 00000030: 20202020 20202020 20202020 20202020 †
46 00000040: 20202020 20202020 20202020 20202020 †
47 00000050: 20202020 20202020 20202020 20202020 †
48 00000060: 20202020 20202020 20202020 20202020 †
49 00000070: 20202020 20202020 20202020 20202020 †
50 00000080: 20202020 20202020 20202020 20202020 †
51 00000090: 20202020 20202020 20202020 20202020 †
52 000000A0: 20202020 20202020 20202020 20202020 †
53 000000B0: 20202020 20202020 20202020 20202020 †
54 000000C0: 20202020 20202020 20202020 20202020 †
55 000000D0: 20202020 20202020 20202020 20202020 †
56 000000E0: 20202020 20202020 20202020 20202020 †
57 000000F0: 20202020 20202020 20202020 20202020 †
58 00000100: 20202020 20202020 20202020 20202020 †
59 00000110: 20202020 20202020 20202020 20202020 †
60 00000120: 20202020 20202020 20202020 20202020 †
61 00000130: 20202020 20202020 20202020 20202020 †
62 00000140: 20202020 20202020 20202020 20202020 †
63 00000150: 20202020 20202020 20202020 20202020 †
64 00000160: 20202020 20202020 20202020 20202020 †
65 00000170: 20202020 20202020 20202020 20202020 †
66 00000180: 20202020 20202020 20202020 20202020 †
67 00000190: 20202020 20202020 20202020 20202020 †
68 000001A0: 20202020 20202020 20202020 20202020 †
69 000001B0: 20202020 20202020 20202020 20202020 †
70 000001C0: 20202020 20202020 20202020 20202020 †
71 000001D0: 20202020 20202020 20202020 20202020 †
72 000001E0: 20202020 20202020 20202020 20202020 †
73 000001F0: 20202020 20202020 20202020 20202020 †
74 00000200: 20202020 20202020 20202020 20202020 †
75 00000210: 20202020 20202020 20202020 20202020 †
76 00000220: 20202020 20202020 20202020 20202020 †
77 00000230: 20202020 20202020 20202020 20202020 †
78 00000240: 20202020 20202020 20202020 20202020 †
79 00000250: 20202020 20202020 20202020 20202020 †
80 00000260: 20202020 20202020 20202020 20202020 †
81 00000270: 20202020 20202020 20202020 20202020 †
82 00000280: 20202020 20202020 20202020 20202020 †
83 00000290: 20202020 20202020 20202020 20202020 †
84 000002A0: 20202020 20202020 20202020 20202020 †
85 000002B0: 20202020 20202020 20202020 20202020 †
86 000002C0: 20202020 20202020 20202020 20202020 †
87 000002D0: 20202020 20202020 20202020 20202020 †
88 000002E0: 20202020 20202020 20202020 20202020 †
89 000002F0: 20202020 20202020 20202020 20202020 †
90 00000300: 20202020 20202020 20202020 20202020 †
91 00000310: 20202020 20202020 20202020 20202020 †
92 00000320: 20202020 20202020 20202020 20202020 †
93 00000330: 20202020 20202020 20202020 20202020 †
94 00000340: 20202020 20202020 20202020 20202020 †
95 00000350: 20202020 20202020 20202020 20202020 †
96 00000360: 20202020 20202020 20202020 20202020 †
97 00000370: 20202020 20202020 20202020 20202020 †
98 00000380: 20202020 20202020 20202020 20202020 †
99 00000390: 20202020 20202020 20202020 20202020 †
100 000003A0: 20202020 20202020 20202020 20202020 †
101 000003B0: 20202020 20202020 20202020 20202020 †
102 000003C0: 20202020 20202020 20202020 20202020 †
103 000003D0: 20202020 20202020 20202020 20202020 †
104 000003E0: 20202020 20202020 20202020 20202003 † .
105 000003F0: 00f8†††††††††††††††††††††††††††††††††..
106
107 Slot 0 Column 0 Offset 0x4 Length 4
108
109 id = 29
110
111 Slot 0 Column 1 Offset 0x8 Length 999
112
113 col2 = xxx
114
115
116
117
118
119
120
121
122 col3 = [NULL]
123
124 Slot 1 Offset 0x452 Length 1010
125
126 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
127 Memory Dump @0x0A70C452
128
129 00000000: 1000ef03 1f000000 78787820 20202020 †........xxx
130 00000010: 20202020 20202020 20202020 20202020 †
131 00000020: 20202020 20202020 20202020 20202020 †
132 00000030: 20202020 20202020 20202020 20202020 †
133 00000040: 20202020 20202020 20202020 20202020 †
134 00000050: 20202020 20202020 20202020 20202020 †
135 00000060: 20202020 20202020 20202020 20202020 †
136 00000070: 20202020 20202020 20202020 20202020 †
137 00000080: 20202020 20202020 20202020 20202020 †
138 00000090: 20202020 20202020 20202020 20202020 †
139 000000A0: 20202020 20202020 20202020 20202020 †
140 000000B0: 20202020 20202020 20202020 20202020 †
141 000000C0: 20202020 20202020 20202020 20202020 †
142 000000D0: 20202020 20202020 20202020 20202020 †
143 000000E0: 20202020 20202020 20202020 20202020 †
144 000000F0: 20202020 20202020 20202020 20202020 †
145 00000100: 20202020 20202020 20202020 20202020 †
146 00000110: 20202020 20202020 20202020 20202020 †
147 00000120: 20202020 20202020 20202020 20202020 †
148 00000130: 20202020 20202020 20202020 20202020 †
149 00000140: 20202020 20202020 20202020 20202020 †
150 00000150: 20202020 20202020 20202020 20202020 †
151 00000160: 20202020 20202020 20202020 20202020 †
152 00000170: 20202020 20202020 20202020 20202020 †
153 00000180: 20202020 20202020 20202020 20202020 †
154 00000190: 20202020 20202020 20202020 20202020 †
155 000001A0: 20202020 20202020 20202020 20202020 †
156 000001B0: 20202020 20202020 20202020 20202020 †
157 000001C0: 20202020 20202020 20202020 20202020 †
158 000001D0: 20202020 20202020 20202020 20202020 †
159 000001E0: 20202020 20202020 20202020 20202020 †
160 000001F0: 20202020 20202020 20202020 20202020 †
161 00000200: 20202020 20202020 20202020 20202020 †
162 00000210: 20202020 20202020 20202020 20202020 †
163 00000220: 20202020 20202020 20202020 20202020 †
164 00000230: 20202020 20202020 20202020 20202020 †
165 00000240: 20202020 20202020 20202020 20202020 †
166 00000250: 20202020 20202020 20202020 20202020 †
167 00000260: 20202020 20202020 20202020 20202020 †
168 00000270: 20202020 20202020 20202020 20202020 †
169 00000280: 20202020 20202020 20202020 20202020 †
170 00000290: 20202020 20202020 20202020 20202020 †
171 000002A0: 20202020 20202020 20202020 20202020 †
172 000002B0: 20202020 20202020 20202020 20202020 †
173 000002C0: 20202020 20202020 20202020 20202020 †
174 000002D0: 20202020 20202020 20202020 20202020 †
175 000002E0: 20202020 20202020 20202020 20202020 †
176 000002F0: 20202020 20202020 20202020 20202020 †
177 00000300: 20202020 20202020 20202020 20202020 †
178 00000310: 20202020 20202020 20202020 20202020 †
179 00000320: 20202020 20202020 20202020 20202020 †
180 00000330: 20202020 20202020 20202020 20202020 †
181 00000340: 20202020 20202020 20202020 20202020 †
182 00000350: 20202020 20202020 20202020 20202020 †
183 00000360: 20202020 20202020 20202020 20202020 †
184 00000370: 20202020 20202020 20202020 20202020 †
185 00000380: 20202020 20202020 20202020 20202020 †
186 00000390: 20202020 20202020 20202020 20202020 †
187 000003A0: 20202020 20202020 20202020 20202020 †
188 000003B0: 20202020 20202020 20202020 20202020 †
189 000003C0: 20202020 20202020 20202020 20202020 †
190 000003D0: 20202020 20202020 20202020 20202020 †
191 000003E0: 20202020 20202020 20202020 20202003 † .
192 000003F0: 00f8†††††††††††††††††††††††††††††††††..
193
194 Slot 1 Column 0 Offset 0x4 Length 4
195
196 id = 31
197
198 Slot 1 Column 1 Offset 0x8 Length 999
199
200 col2 = xxx
201
202
203
204
205
206
207
208
209 col3 = [NULL]
210
211
212 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。13571这个页面存储了id为29和id为31这两条记录
1 PAGE: (1:13517)
2
3
4
5 Slot 0 Offset 0x60 Length 1010
6
7
8
9 Slot 0 Column 0 Offset 0x4 Length 4
10
11 id = 29
12
13 Slot 0 Column 1 Offset 0x8 Length 999
14
15 col2 = xxx
16
17
18
19
20 col3 = [NULL]
21
22 Slot 1 Offset 0x452 Length 1010
23
24
25
26 Slot 1 Column 0 Offset 0x4 Length 4
27
28 id = 31
29
30 Slot 1 Column 1 Offset 0x8 Length 999
31
32 col2 = xxx 我们删除掉这两条记录
1 DELETE FROM [dbo].[HeapTable] WHERE [id] IN(29,31)
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 13517这个页面还在
再看一下13517这个页面
1 DBCC TRACEON(3604,-1)
2 GO
3 DBCC PAGE([pratice],1,13517,1)
4 GO 1 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
2
3 PAGE: (1:13517)
4
5
6 BUFFER:
7
8
9 BUF @0x03E5ED24
10
11 bpage = 0x196B8000 bhash = 0x00000000 bpageno = (1:13517)
12 bdbid = 5 breferences = 0 bUse1 = 50074
13 bstat = 0x3c0000b blog = 0x2159bbbb bnext = 0x00000000
14
15 PAGE HEADER:
16
17
18 Page @0x196B8000
19
20 m_pageId = (1:13517) m_headerVersion = 1 m_type = 1
21 m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8008
22 m_objId (AllocUnitId.idObj) = 622 m_indexId (AllocUnitId.idInd) = 256
23 Metadata: AllocUnitId = 72057594078691328
24 Metadata: PartitionId = 72057594061783040 Metadata: IndexId = 0
25 Metadata: ObjectId = 196195749 m_prevPage = (0:0) m_nextPage = (0:0)
26 pminlen = 1007 m_slotCnt = 2 m_freeCnt = 8092
27 m_freeData = 2116 m_reservedCnt = 2020 m_lsn = (3047:25281:3)
28 m_xactReserved = 2020 m_xdesId = (0:11673035) m_ghostRecCnt = 0
29 m_tornBits = 0
30
31 Allocation Status
32
33 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
34 PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
35 ML (1:7) = NOT MIN_LOGGED
36
37 DATA:
38
39
40 OFFSET TABLE:
41
42 Row - Offset
43 1 (0x1) - 0 (0x0)
44 0 (0x0) - 0 (0x0)
45
46
47 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。OFFSET TABLE:
Row - Offset
1 (0x1) - 0 (0x0)
0 (0x0) - 0 (0x0)
可以看到还有两行记录存在，只是没有这两行记录的行偏移，SQLSERVER修改了行偏移，我们可以认为这两行记录已经删除了
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。