Code
use tempdb
go
create table tb(a int primary key,b char(5000) default('bbbbb'))
go
declare @i int,@v int
set @i=1
while @i<=400
begin
reval:
set @v=abs(checksum(newid())%1000)
if exists(select * from tb where a=@v)
goto reval
insert into tb(a) values (@v)
set @i=@i+1
end
go
--找出IAM、Root、IAM指向的第一页的页面位置
select [first],[root],firstIAM,* from sysindexes where id=object_id('tb')
--计算分别从IAM开始扫描和从ROOT开始扫描所应该得出的结果
--1.从ROOT页开始扫描(Clustered Index Scan)
DBCC TRACEON(3604) --
DBCC PAGE(tempdb,1,110,3) --查看索引根页的内容
--打开set statistics io on ,
--发现select * from tb with (nolock)比select * from tb 少读取1个io,应该是略过了根索引页。
--DBCC Page ({dbid|dbname},filenum,pagenum[,printopt])
--
--具体参数描述如下:
--dbid 包含页面的数据库ID
--dbname 包含页面的数据库的名称
--filenum 包含页面的文件编号
--pagenum 文件内的页面
--printopt 可选的输出选项;选用其中一个值:
-- 0:默认值,输出缓冲区的标题和页面标题
-- 1:输出缓冲区的标题、页面标题(分别输出每一行),以及行偏移量表
-- 2:输出缓冲区的标题、页面标题(整体输出页面),以及行偏移量表
-- 3:输出缓冲区的标题、页面标题(分别输出每一行),以及行偏移量表;每一行
-- 后跟分别列出的它的列值
--
--要想看到这些输出的结果,还需要设置DBCC TRACEON(3604)。
--
--对于表来说,它所在的dbid或者dbname很容易知道,那么它的filenum和pagenum怎么知道呢?
--其实这个值存在系统表sysindexes的列first中(当indid为0或者1时,indid为0表示该表是一个堆表,
--没有任何聚集索引,indid为1表示该索引为聚集索引),列first的数据类型为binary(6),
--它是以16进制的形式储的,需要进行转换。在16进制中,每两个16进制数字表示一个字节,
--并且是逆序排列的。比如假设一个表的first的值为0xC70000000100,怎么转换呢?因为它是逆序排列的,
--并且每两个表示一个字节,所以每次都将剩下first值的最后两位移到转换后数据的最后,
--具体转换步骤如下:
--
--步骤 此时的first值 转换后的数据
--第0步 0xC70000000100
--第1步 0xC700000001 00
--第2步 0xC7000000 0001
--第3步 0xC70000 000100
--第4步 0xC700 00010000
--第5步 0xC7 0001000000
--第6步 0x 0001000000C7
--
--此时转换完成。数据0001000000C7即是我们需要的数据,其中它的前2组数(即0001)表示该表所在的文件编号;
--后4组(000000C7)表示该表所在的页码(注意该数据还要转换为10进制的数才能具体知道是多少)。
use tempdb
go
create table tb(a int primary key,b char(5000) default('bbbbb'))
go
declare @i int,@v int
set @i=1
while @i<=400
begin
reval:
set @v=abs(checksum(newid())%1000)
if exists(select * from tb where a=@v)
goto reval
insert into tb(a) values (@v)
set @i=@i+1
end
go
--找出IAM、Root、IAM指向的第一页的页面位置
select [first],[root],firstIAM,* from sysindexes where id=object_id('tb')
--计算分别从IAM开始扫描和从ROOT开始扫描所应该得出的结果
--1.从ROOT页开始扫描(Clustered Index Scan)
DBCC TRACEON(3604) --
DBCC PAGE(tempdb,1,110,3) --查看索引根页的内容
--打开set statistics io on ,
--发现select * from tb with (nolock)比select * from tb 少读取1个io,应该是略过了根索引页。
--DBCC Page ({dbid|dbname},filenum,pagenum[,printopt])
--
--具体参数描述如下:
--dbid 包含页面的数据库ID
--dbname 包含页面的数据库的名称
--filenum 包含页面的文件编号
--pagenum 文件内的页面
--printopt 可选的输出选项;选用其中一个值:
-- 0:默认值,输出缓冲区的标题和页面标题
-- 1:输出缓冲区的标题、页面标题(分别输出每一行),以及行偏移量表
-- 2:输出缓冲区的标题、页面标题(整体输出页面),以及行偏移量表
-- 3:输出缓冲区的标题、页面标题(分别输出每一行),以及行偏移量表;每一行
-- 后跟分别列出的它的列值
--
--要想看到这些输出的结果,还需要设置DBCC TRACEON(3604)。
--
--对于表来说,它所在的dbid或者dbname很容易知道,那么它的filenum和pagenum怎么知道呢?
--其实这个值存在系统表sysindexes的列first中(当indid为0或者1时,indid为0表示该表是一个堆表,
--没有任何聚集索引,indid为1表示该索引为聚集索引),列first的数据类型为binary(6),
--它是以16进制的形式储的,需要进行转换。在16进制中,每两个16进制数字表示一个字节,
--并且是逆序排列的。比如假设一个表的first的值为0xC70000000100,怎么转换呢?因为它是逆序排列的,
--并且每两个表示一个字节,所以每次都将剩下first值的最后两位移到转换后数据的最后,
--具体转换步骤如下:
--
--步骤 此时的first值 转换后的数据
--第0步 0xC70000000100
--第1步 0xC700000001 00
--第2步 0xC7000000 0001
--第3步 0xC70000 000100
--第4步 0xC700 00010000
--第5步 0xC7 0001000000
--第6步 0x 0001000000C7
--
--此时转换完成。数据0001000000C7即是我们需要的数据,其中它的前2组数(即0001)表示该表所在的文件编号;
--后4组(000000C7)表示该表所在的页码(注意该数据还要转换为10进制的数才能具体知道是多少)。
聚集索引与非聚集索引结构
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
)
CREATE TABLE DBO.EMPLOYEE
(
EMPLOYEEID INT IDENTITY(1,1),
FIRSTNAME VARCHAR(50) NOT NULL,
LASTNAME VARCHAR(50) NOT NULL,
DATE_HIRED DATETIME NOT NULL,
IS_ACTIVE BIT NOT NULL DEFAULT 1,
CONSTRAINT PK_EMPLOYEE PRIMARY KEY(EMPLOYEEID),
CONSTRAINT UQ_EMPLOYEE_LASTNAME UNIQUE(LASTNAME,FIRSTNAME)
)
GO
INSERT INTO DBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT'George','Washington','1999-03-15'
GO
INSERT INTO DBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT'Benjamin','Franklin','2001-07-05'
GO
INSERT INTO DBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT'Thomas','Jefferson','2002-11-10'
GO
--List data and index page sallocated to the EMPLOYEE table
DBCC IND('TEST',EMPLOYEE,-1)
GO
--PageType表示页的类型,1是数据页,2是索引页,10是保存页本身的IAM页
DBCC TRACEON(3604)
DBCC PAGE('TEST',1,203,3)WITH TABLERESULTS
GO
DBCC PAGE('TEST',1,205,3)WITH TABLERESULTS
GO
--将表修改为非聚焦索引
ALTER TABLE DBO.EMPLOYEE DROP CONSTRAINT PK_EMPLOYEE
GO
ALTER TABLE DBO.EMPLOYEE ADD CONSTRAINT PK_EMPLOYEE
PRIMARY KEY NONCLUSTERED(EMPLOYEEID)
GO
DBCC IND('TEST',EMPLOYEE,-1)
--select * from sys.indexes where object_id=object_id('test..employee')
select * from sys.system_internals_allocation_units a
inner join sys.partitions b on a.container_id=b.partition_id
where b.object_id=object_id('test..employee')
--FirstPage: FileID, PageID
DBCC PAGE('TEST',1,205,3)WITH TABLERESULTS
DBCC PAGE('TEST',1,203,3)WITH TABLERESULTS
--Heap RID: Slot, FileID, PageID
GO
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
)
CREATE TABLE DBO.EMPLOYEE
(
EMPLOYEEID INT IDENTITY(1,1),
FIRSTNAME VARCHAR(50) NOT NULL,
LASTNAME VARCHAR(50) NOT NULL,
DATE_HIRED DATETIME NOT NULL,
IS_ACTIVE BIT NOT NULL DEFAULT 1,
CONSTRAINT PK_EMPLOYEE PRIMARY KEY(EMPLOYEEID),
CONSTRAINT UQ_EMPLOYEE_LASTNAME UNIQUE(LASTNAME,FIRSTNAME)
)
GO
INSERT INTO DBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT'George','Washington','1999-03-15'
GO
INSERT INTO DBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT'Benjamin','Franklin','2001-07-05'
GO
INSERT INTO DBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT'Thomas','Jefferson','2002-11-10'
GO
--List data and index page sallocated to the EMPLOYEE table
DBCC IND('TEST',EMPLOYEE,-1)
GO
--PageType表示页的类型,1是数据页,2是索引页,10是保存页本身的IAM页
DBCC TRACEON(3604)
DBCC PAGE('TEST',1,203,3)WITH TABLERESULTS
GO
DBCC PAGE('TEST',1,205,3)WITH TABLERESULTS
GO
--将表修改为非聚焦索引
ALTER TABLE DBO.EMPLOYEE DROP CONSTRAINT PK_EMPLOYEE
GO
ALTER TABLE DBO.EMPLOYEE ADD CONSTRAINT PK_EMPLOYEE
PRIMARY KEY NONCLUSTERED(EMPLOYEEID)
GO
DBCC IND('TEST',EMPLOYEE,-1)
--select * from sys.indexes where object_id=object_id('test..employee')
select * from sys.system_internals_allocation_units a
inner join sys.partitions b on a.container_id=b.partition_id
where b.object_id=object_id('test..employee')
--FirstPage: FileID, PageID
DBCC PAGE('TEST',1,205,3)WITH TABLERESULTS
DBCC PAGE('TEST',1,203,3)WITH TABLERESULTS
--Heap RID: Slot, FileID, PageID
GO