通过DBCC IND分析表组织和索引组织

0.参考文献：

Sql Server中的表组织和索引组织（聚集索引结构，非聚集索引结构，堆结构）

1.DBCC IND跟DBCC PAGE简介

1.1.DBCC IND命令

DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid },
      { nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number]  )

1.2.DBCC输出字段描述

Column(列)	Meaning(含义)
PageFID	索引所在文件ID
PagePID	索引页ID
IAMFID	File ID of the IAM managing this page，IAM page的IAMFID=NULL
IAMPID	Page ID of the IAM managing this page，IAM page的IAMPID=NULL
ObjectID	对象ID
IndexID	索引类型ID，0表示堆，1表示聚集索引，2-250表示非聚集索引。可以在sys.indexs上查找
PartitionNumber	Partition number within the table or index for this page
PartitionID	ID for the partition containing this page (unique in the database)
iam_chain_type	Type of allocation unit this page belongs to: in-row data, row-overflow data, or LOB data
PageType	Page type: 1 = data page, 2 = index page, 3 = LOB_MIXED_PAGE, 4 = LOB_TREE_PAGE, 10 = IAM page
IndexLevel	索引级别，0表示叶子节点，根节点的级别最高
NextPageFID	File ID for next page at this level，同一等级上下一个索引页所在文件的ID
NextPagePID	Page ID for next page at this level
PrevPageFID	File ID for previous page at this level
PrevPagePID	Page ID for previous page at this level，同一等级上的上一个索引页，通过双向链表连接起来

1.3.DBCC PAGE

DBCC PAGE 参数DBCC PAGE
(
['database name'|database id], -- can be the actual name or id of the database
file number, -- the file number where the page is found
page number, -- the page number within the file
print option = [0|1|2|3] -- display option; each option provides differing levels of information
)

2.描述索引结构

在<inside sql server 2005：storage engine>的第七章的The Structure of Index Pages小结中提到了索引页的结构。本文将通过实验来描述索引页的结构，然后通过索引页的结构来更加深刻清晰得描述聚集索引、非聚集索引和多结构。

-----实验：查看索引页，推到聚集索引非聚集索引结构------------------------------------------------
use TESTDB3
--1.创建表,有主键,sql server默认设置为聚集索引
CREATE TABLE Suppliers
(
  supplierid   INT          NOT NULL IDENTITY,
  companyname  NVARCHAR(40) NOT NULL,
  CONSTRAINT PK_Suppliers PRIMARY KEY(supplierid)
);

 --2.查看页信息，结果为null,这是因为还没有数据.索引会根据数据的更新来更新.
dbcc ind ( TESTDB3, [dbo.Suppliers], -1)

--3.插入一条记录
insert into Suppliers values('zhangsan');

--4.查看页信息，结果不为null,有两条记录。发现IndexID=1表示聚集索引。
dbcc ind ( TESTDB3, [dbo.Suppliers], -1)

--5.创建非聚集索引
CREATE NONCLUSTERED INDEX idx_nc_companyname ON dbo.Suppliers(companyname);

--6.查看页面信息,发现多了两条indexID=2的记录，表示非聚集索引，如果继续创建非聚集索引，那么IndexID会继续增加，最大为250.
dbcc ind ( TESTDB3, [dbo.Suppliers], -1)

--7.插入一条记录
insert into Suppliers values('zhangsan');

--8.查看page信息发现没变，这是因为一个索引页可以存放多个索引行，一个数据页可以存放很多数据行。
dbcc ind ( TESTDB3, [dbo.Suppliers], -1)

--9.插入1000条记录，超过一个索引页跟数据页的容量。
SET NOCOUNT ON--不统计影响行数
declare @i int
set @i=1
while @i<=1000
begin 
    insert into Suppliers values('zhangsan');
    set @i=@i+1
end

--10.再次索引页信息
dbcc ind ( TESTDB3, [dbo.Suppliers], -1)

上述第10步的查询结果如下：

上图包含了非常丰富的信息。

数据页与索引页的大小

我们首先来看聚集索引的7个page。其中有5个是PageType=1的，也就是说有5个data page（data page是一种特殊的index page），而剩余2个IAM page和6个index page。

--ps:2012-7-18----------------------------

“Index pages fall into three basic types: leaf level for nonclustered indexes, node (nonleaf) level for clustered indexes, and node level for nonclustered indexes. There isn't really a separate structure for leaf level pages of a clustered index because those are the data pages, which we've already seen in detail. There is, however, one special case for leaf-level clustered index pages which I'll tell you about now.”——from《inside sql server 2005,The Structure of Index Pages》

正如上面因为文献中提到的，index page 有三种类型：

非聚集索引的叶子节点
聚集索引的非叶子节点
非聚集索引的非叶子节点

而聚集索引的叶子节点是data page。虽然我们可以使用dbcc ind找出data page，但是我们不能讲data page看做是index page。

--------------------------------------

我们执行如下命令

--查看表Suppliers的大小
sp_spaceused Suppliers

查询结果如下图所示：

我们发现data=40KB，刚好是5个data page的大小，而index_size=64KB，刚好是2个IAM page加上6个index page的大小。

聚集索引与非聚集索引的结构

聚集索引

首先我们查看聚集索引的结构。上图提供了丰富的信息，我们可以

然后利用利用NextPagePID跟PrevPagePID,我们可以画出index page的双向链表
利用Index Level我们可以画出索引层级
利用PageType我们可以找出数据页和索引页.

例如，从上图中我们可以发现，PagePID=2190的这个index page，他的PageType=2，index level=1，表示他是一个索引等级为1的索引页，不是叶子节点。而PageID=(2184,2191,2194,2196,2198)的这5个index page，他们的PageType=1，index level=0，表明这是5个叶子节点，并且都是data page。因此聚集索引结构如下图所示。

非聚集索引

利用同样方法，首先找出非聚集索引的根节点，然后找出叶子节点，叶子节点上有双向链表，如下图示所示：

我们发现非聚集索引的所有索引节点都是index page，而没有data page。通过上面的实例，我们再回过头去看之前写过的Sql Server中的表组织和索引组织（聚集索引结构，非聚集索引结构，堆结构）这篇文章，会有更深刻的体会。

我们可以通过dbcc page 更加直观地显示上述聚集索引和非聚集索引的结构，执行下面的命令

DBCC TRACEON (3604);
GO
DBCC PAGE (TESTDB3,1,2190, 3);--也可以是DBCC PAGE (TESTDB3,1,2190, 1);
DBCC PAGE (TESTDB3,1,2192, 3);--也可以是DBCC PAGE (TESTDB3,1,2190, 1);

查询结果如下所示：

总结上图：

对聚集索引的非叶子节点使用dbcc page，可以求出它的ChildPage。
对非聚集索引的非叶子节点使用dbcc page，也可以求出它的ChildPage，而且我们可以看到非聚集索引的键值。
如果对聚集索引的叶子节点使用dbcc page，我们可到data page上存储的数据的二进制代码。

3.描述堆结构

运行如下实验

---描述堆结构------------------------
--1.创建堆heap
CREATE TABLE Student
(
  stuid    INT          NOT NULL,
  stuname  NVARCHAR(40) NOT NULL,
);

--2.插入一条记录
insert into Student values(1,'zhangsan');

--3.查看索引页信息，发现IndexID=0，表示这是堆结构heap
dbcc ind ( TESTDB3, [Student], -1)

--4.插入1000条记录，超过一个索引页跟数据页的容量。
SET NOCOUNT ON--不统计影响行数
declare @i int
set @i=1
while @i<=1000
begin 
    insert into Student values(1,'zhangsan');
    set @i=@i+1
end

--5.查询索引页信息发现只有两种类型的索引页,一种IAM page,还有一个是data page.
dbcc ind ( TESTDB3, [Student], -1)