利用DBCC PAGE查看SQL Server中的表和索引数据
16:08 2013-01-06 参考http://doc.chinaunix.net/sqlserver/200810/206034.shtml
DBCC PAGE|IND 参数
1 DBCC PAGE 参数 2 ( 3 ['database name'|database id], --can be the actual name or id of the database 4 file number, --the file number where the page is found 5 page number, --the page number within the file 6 print option=[0|1|2|3] --display option; each option provides differing levels of information 7 ) 8 DBCC IND 参数 9 ( 10 ['database name'|database id], --the database to use 11 table name, --the table name to list results 12 index id, --an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs 13 )
一、查看表的数据
在系统表sysindexes 的列 first中保存有 filenum,pagenum 的信息(当indid为0或者1), 列first 的数据类型为binary(6), 它是以16进制的形式储的,需要进行转换. 在16进制中,每两个进制数字表示一个字节,并且是逆序排列的. 转换完成,其中前2组数表示该表所在的文件编号; 后4组表示该表所在的页码.
根据sysindexes 中的列first返回表所在的filenum 、pagenum
1 declare @first binary(6) 2 select @first=first from sysindexes where id=object_id('fanr_city') and indid in(0,1) 3 declare @PageNum int 4 select @PageNum=convert(int,substring(@first,4,1)+substring(@first,3,1)+ 5 substring(@first,2,1)+substring(@first,1,1)) 6 declare @FileNum int 7 select @FileNum=convert(int,substring(@first,6,1)+substring(@first,5,1)) 8 select @FileNum,@PageNum
通过返回的@FileNum,@PageNum，查看表数据页结构
1 DBCC TRACEON (3604) 2 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 输出了错误信息，请与系统管理员联系。
二、查看索引的数据
1 --创建测试表，并添加索引 2 USE AdventureWorks 3 SELECT * 4 INTO dbo.Contacts_index 5 FROM Person.Contact 6 CREATE INDEX FullName ON Contacts_index(LastName,FirstName) 7 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，那么就是索引的叶级别页。
1 DBCC TRACEON(3604) 2 DBCC PAGE(AdventureWorks,1,15056,3) WITH TABLERESULTS 3 DBCC PAGE(AdventureWorks,1,14988,3) WITH TABLERESULTS
以下分别是中间级索引页15056和叶级索引页14988、14989的数据分布情况。索引条目按照索引键的值有序排列