16:08 2013-01-06 参考http://doc.chinaunix.net/sqlserver/200810/206034.shtml
DBCC PAGE|IND 参数
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 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
)
一、查看表的数据
在系统表sysindexes 的列 first中保存有 filenum,pagenum 的信息(当indid为0或者1), 列first 的数据类型为binary(6), 它是以16进制的形式储的,需要进行转换. 在16进制中,每两个进制数字表示一个字节,并且是逆序排列的. 转换完成,其中前2组数表示该表所在的文件编号; 后4组表示该表所在的页码.
根据sysindexes 中的列first返回表所在的filenum 、pagenum
declare @first binary(6)
select @first=first from sysindexes where id=object_id('fanr_city') and indid in(0,1)
declare @PageNum int
select @PageNum=convert(int,substring(@first,4,1)+substring(@first,3,1)+
substring(@first,2,1)+substring(@first,1,1))
declare @FileNum int
select @FileNum=convert(int,substring(@first,6,1)+substring(@first,5,1))
select @FileNum,@PageNum
通过返回的@FileNum,@PageNum，查看表数据页结构
DBCC TRACEON (3604)
DBCC Page (7,1,227,1)
返回结果如下
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 PAGE: (1:227) BUFFER: BUF @0x03BE8618 bpage = 0x05554000 bhash = 0x00000000 bpageno = (1:227)
bdbid = 7 breferences = 0 bUse1 = 7194
bstat = 0x1c0000b blog = 0x212121bb bnext = 0x00000000 PAGE HEADER: Page @0x05554000 m_pageId = (1:227) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 114 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594045399040
Metadata: PartitionId = 72057594044350464 Metadata: IndexId = 0
Metadata: ObjectId = 1381579960 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 92 m_slotCnt = 0 m_freeCnt = 8096
m_freeData = 96 m_reservedCnt = 0 m_lsn = (71:349:76)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0 Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED DATA: OFFSET TABLE: DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
二、查看索引的数据
--创建测试表，并添加索引
USE AdventureWorks
SELECT *
INTO dbo.Contacts_index
FROM Person.Contact
CREATE INDEX FullName ON Contacts_index(LastName,FirstName)
DBCC IND(AdventureWorks,Contacts_index,-1)
列PageFID和PagePID分别代表页所在的文件数目和数据在文件内的页数目。IndexID是在sys.indexes之中找到的索引的index_id。PageType表示页的类型，Type=1是数据页，Type=2是索引页，Type=10是保存页本身的IAM页。IndexLevel是按页数时IAM结构的级别。如果 level=0，那么就是索引的叶级别页。
DBCC TRACEON(3604)
DBCC PAGE(AdventureWorks,1,15056,3) WITH TABLERESULTS
DBCC PAGE(AdventureWorks,1,14988,3) WITH TABLERESULTS
以下分别是中间级索引页15056和叶级索引页14988、14989的数据分布情况。索引条目按照索引键的值有序排列
