聚集索引表插入数据和删除数据的方式是怎样的_.NET_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > .NET > 聚集索引表插入数据和删除数据的方式是怎样的

聚集索引表插入数据和删除数据的方式是怎样的

 2013/7/25 11:42:43  桦仔  博客园  我要评论(0)
  • 摘要:聚集索引表插入数据和删除数据的方式是怎样的根据《SQLSERVER聚集索引与非聚集索引的再次研究(上)》里说的,聚集索引维护着创建第一个聚集索引时的第一个字段的顺序来排序当插入记录的时候,或者重新组织索引的时候都会按照字段顺序来排序今天来做一个实验来验证一下--------------------------------------------------华丽的分割线-----------------------------------------------
  • 标签:索引 数据 方式

聚集索引表插入数据和删除数据的方式是怎样的

 根据《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的结果

class="code_img_closed" src="/Upload/Images/2013072511/0015B68B3C38AA5B.gif" alt="" />logs_code_hide('2ccbecb8-3baf-4597-9208-0e516877a125',event)" src="/Upload/Images/2013072511/2B1B950FA3DF188F.gif" alt="" />
 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 

发表评论
用户名: 匿名