利用DBCC PAGE查看SQL Server中的表和索引数据

【 tulaoshi.com - 编程语言 】

　　问题

　　我读了很多关于数据库页和如何利用它们来存储表数据和索引数据的文章。有没有一种方法可以让我真正看到这方面的信息呢？

　　专家解答

　　在SQL Server中最糟的一个秘密是没有正式文件说明的DBCC PAGE命令，而这个命令可以让你查看数据和索引页的内容。其中，某些信息仍然是神秘的，而你喜爱的搜索引擎是一个让你开始着手查找输出结果背后展示的很多意思的好去处。尽管如此，我发现，当我遍历数据库来解决数据库问题而我只有通过页信息来继续摸索这个问题时，或者当我只是想看看当遇到某些数据库操作发生时数据库引擎如何处理数据和索引页时，我发现DBCC命令是很有用的。

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
)

　　首先，让我们来创建一个示例数据库和表，这将有利于我们描述通过DBCC PAGE你看到了什么。

　　USEMASTER
　　GO
　　CREATEDATABASEMSSQLTIPS
　　GO
　　USEMSSQLTIPS
　　GO
　　CREATETABLEDBO.EMPLOYEE
　　(
　　EMPLOYEEIDINTIDENTITY(1,1),
　　FIRSTNAMEVARCHAR(50)NOTNULL,
　　LASTNAMEVARCHAR(50)NOTNULL,
　　DATE_HIREDDATETIMENOTNULL,
　　IS_ACTIVEBITNOTNULLDEFAULT1,
　　CONSTRAINTPK_EMPLOYEEPRIMARYKEY(EMPLOYEEID),
　　CONSTRAINTUQ_EMPLOYEE_LASTNAMEUNIQUE(LASTNAME,FIRSTNAME)
　　)
　　GO
　　INSERTINTODBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)
　　SELECT'George','Washington','1999-03-15'
　　GO
　　INSERTINTODBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)
　　SELECT'Benjamin','Franklin','2001-07-05'
　　GO
　　INSERTINTODBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)
　　SELECT'Thomas','Jefferson','2002-11-10'
　　GO

　　现在，我们可以去看看SQL Server如何存储数据和索引页。但是我们该从哪里开始？我们能从哪里找到这张表的页和它的数据的所在？其实，这里还有另外的DBCC命令 – DBCC IND – 你可以用它来列出一张表的所有数据和索引页。

DBCC IND参数DBCC IND
(
['database name'|database id], -- the database to use
table name, -- the table name to list results
index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)

　　让我们通过运行下面的命令行来列出EMPLOYEE表的页结构。

　　ListdataandindexpagesallocatedtotheEMPLOYEEtable
　　DBCCIND('MSSQLTIPS',EMPLOYEE,-1)
　　GO

　　以下是我的数据库所输出的结果：

　　请注意，为了更简洁，上述的图像只列出了执行DBCC命令后输出的前面11列的结果。还有另外的列没有列出来，这些列包括可以让你看到这些页如何彼此关联的链接列表信息。

　　这一次的数据意味着什么呢？为了达到这篇文章讲述的方法的目的，我们专注于一些关键列。列PageFID和PagePID分别代表页所在的文件数目和数据在文件内的页数目。IndexID是在sys.indexes之中找到的索引的index_id。PageType表示页的类型，Type = 1是数据页，Type = 2是索引页，Type = 10是保存页本身的IAM页。IndexLevel是按页数时IAM结构的级别。如果 level = 0，那么这是索引的叶级别页。要想了解更多这些列的详细信息(毕竟这是一个没有在正式文件中说明的命令)，可以看看由微软前存储引擎专家Paul Randal写的MSDN blog，这个博客很详细地解释了这些。

　　有了这些信息，现在我们可以看看我们插入的Washington, Franklin和Jefferson三行是如何存储到EMPLOYEE表中的。EMPLOYEE表有一个聚簇索引(主键定义的结果)，它表示应该有一个由DBCC IND输出结果产生的IndexID = 1(index_id = 1指向聚簇索引，在这个聚簇索引中，对于表来说叶级别页是真正的数据)。观察由DBCC IND产生的输出结果，我们可以看到，这个PageType = 1聚簇索引可以在文件数(PageFID) = 1和页码(PagePID) = 143的地方找到。这里有四个不同的显示页数据的打印选项。我使用的是包含页标题信息和数据的打印选项3。

　　注意：在我们能够运行DBCC PAGE之前，要求跟踪标志3604设置成指导引擎去发送输出结果到控制台，否则你将什么都看不到。

　　DBCCTRACEON(3604)
　　DBCCPAGE('MSSQLTIPS',1,143,3)WITHTABLERESULTS
　　GO

　　滚动到这些结果的结尾处，我们可以看到，我们的数据已经存储了并且它存储在聚簇索引的列上。数据行存储在以零点偏移开始的槽变量上。

　　EMPLOYEE表也有一个非聚簇索引(通过在表中定义的约束)。让我们查看创建的非聚簇索引。再次观察DBCC IND输出，我们可以很容易确定非聚簇页，因为它是IndexID = 2 (PageType = 2)并且它可以在文件数(PageFID)= 1和页码(PagePID) = 153中找到。注意，如果我们有表上的不同索引，我们可以查看sys.indexes并且得到随后要用来查看具体索引的index_id。现在，让我们来看看索引数据：

　　DBCCPAGE('MSSQLTIPS',1,153,3)WITHTABLERESULTS
　　GO

　　滚动到这些结果的末端，我们可以看到我们的索引数据是按姓和名的逻辑排列来存储的。你也应该注意到，聚簇索引键也存储在索引行中。当需要一个书签查找(bookmark lookup)时，引擎可以用它来检索聚簇索引(这种类型的查找发生在索引栏没有包含需要用来满足一个查询的所有栏时)。

　　如果这里没有表上的聚簇索引，那么另外的栏将会指向实际的数据页。让我们重新创建作为非聚簇索引的主键并且重新检查由UNIQUE约束创建的非聚簇索引。注意，通过重新创建没有聚簇索引的表，基本页的数据已经改变了。通过DBCC IND，你可以看到这些页结构如何改变。

　　ALTERTABLEDBO.EMPLOYEEDROPCONSTRAINTPK_EMPLOYEE
　　GO
　　ALTERTABLEDBO.EMPLOYEEADDCONSTRAINTPK_EMPLOYEE
　　PRIMARYKEYNONCLUSTERED(EMPLOYEEID)
　　GO
　　DBCCIND('MSSQLTIPS',EMPLOYEE,-1)
　　DBCCPAGE('MSSQLTIPS',1,155,3)WITHTABLERESULTS
　　GO

　　正如你所看到的，在HEAP表中的索引(这张表不是聚簇的)存储了一个不同的指示器，这个指示器直接指向包含要求的另外的数据的页面。

　　这些都是很简单的例子，但是它们对给你关于如何和在哪里获得和显示数表和索引数据的想法是绰绰有余的。在以后的方法中，我将利用更多的例子来说明问题，这些例子将显示当一行改变并且它不适合某一页时将会发生什么，当行被删除时将发生什么，还有DBCC PAGE如何帮助解决阻塞和死锁问题。

　　我必须强调，DBCC IND和DBCC PAGE没有在正式文件中说明，它们可能在以后的SQL Server版本中会消失。在那出现之前，我会继续利用这些命令作为窥探引擎的数据存储技术和解决SQL Server问题的主要工具。