SQL SERVER 存储原理 数据存储

select * from sys.indexes
select * from sys.partitions
select * from sys.allocation_units
select * from sys.system_internals_allocation_units

select * from sys.system_internals_partition_columns

 

DBCC IND

DBCC PAGE

 

-------------------------------------------------------------------------------------------------------------------------
select object_name(object_id) as name,partition_id,partition_number as pnum,rows,
 allocation_unit_id as au_id,type_desc as page_type_desc,total_pages as pages
from sys.partitions p join sys.allocation_units a
 on p.partition_id = a.container_id
where object_id = object_id('dbo.employee')

select convert(char(8),object_name(i.object_id)) as table_name,
 i.name as index_name,i.index_id,i.type_desc as index_type,
 partition_id,partition_number as pnum,rows,
 allocation_unit_id as au_id,a.type_desc as page_type_desc,total_pages as pages
from sys.indexes i join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
 join sys.allocation_units a on p.partition_id = a.container_id
where i.object_id = object_id('dbo.employee')

select convert(char(8),object_name(i.object_id)) as table_name,
 indexproperty(i.object_id,i.name,'minlen') as minlen,
 i.name as index_name,i.index_id,i.type_desc as index_type,
 partition_id,partition_number as pnum,first_page,rows,
 a.allocation_unit_id as au_id,a.type_desc as page_type_desc,a.total_pages as pages
from sys.indexes i join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
 join sys.system_internals_allocation_units a on p.partition_id = a.container_id
where i.object_id = object_id('dbo.employee')

CREATE FUNCTION convert_page_nums (@page_num binary(6))
   RETURNS varchar(11)
AS
  BEGIN
 RETURN(convert(varchar(2), (convert(int, substring(@page_num, 6, 1))
          * power(2, 8)) +
             (convert(int, substring(@page_num, 5, 1)))) + ':' +
               convert(varchar(11),
 (convert(int, substring(@page_num, 4, 1)) * power(2, 24)) +
 (convert(int, substring(@page_num, 3, 1)) * power(2, 16)) +
 (convert(int, substring(@page_num, 2, 1)) * power(2, 8)) +
 (convert(int, substring(@page_num, 1, 1)))) )
  END;
GO
SELECT dbo.convert_page_nums(0xB60000000100);

dbcc ind (testdenny,fixed,-1)
go

dbcc traceon(3604)
go
dbcc page (testdenny,1,182,1)
go

select c.name as column_name,column_id,max_inrow_length,
 pc.system_type_id,leaf_offset
from sys.system_internals_partition_columns pc
 join sys.partitions p on p.partition_id = pc.partition_id
 join sys.columns c on column_id = partition_column_id and c.object_id = p.object_id
where p.object_id = object_id('dbo.fixed')

 

 

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

DBCC IND has three parameters. The first parameter is the database name or the database ID. The second parameter is the object name or object ID within the database. The third parameter is a specific index ID or one of the values 0, -1, or −2.

Here's the syntax:

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

 
 
0
Displays the page numbers for all IAMs and data pages.
-1
Displays the page numbers for all IAMs, data pages, and index pages.
-2
Displays the page numbers for all IAMs.
indid
Displays the page numbers for all IAMs and index pages for this index. If the index ID is 1 (meaning the clustered index), the data pages are also displayed.

The columns mean:

• PageFID - the file ID of the page
• PagePID - the page number in the file
• IAMFID - the file ID of the IAM page that maps this page (this will be NULL for IAM pages themselves as they're not self-referential)
• IAMPID - the page number in the file of the IAM page that maps this page
• ObjectID - the ID of the object this page is part of
• IndexID - the ID of the index this page is part of
• PartitionNumber - the partition number (as defined by the partitioning scheme for the index) of the partition this page is part of
• PartitionID - the internal ID of the partition this page is part of
• iam_chain_type - see IAM chains and allocation units in SQL Server 2005
• PageType - the page type. Some common ones are:
  • 1 - data page
  • 2 - index page
  • 3 and 4 - text pages
  • 8 - GAM page
  • 9 - SGAM page
  • 10 - IAM page
  • 11 - PFS page
• IndexLevel - what level the page is at in the index (if at all). Remember that index levels go from 0 at the leaf to N at the root page (except in clustered indexes in SQL Server 2000 and 7.0 - where there's a 0 at the leaf level (data pages) and a 0 at the next level up (first level of index pages))
• NextPageFID and NextPagePID - the page ID of the next page in the doubly-linked list of pages at this level of the index
• PrevPageFID and PrevPagePID - the page ID of the previous page in the doubly-linked list of pages at this level of the index

 

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

 

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

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

　　现在，我们可以去看看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问题的主要工具。