02.索引-堆表

堆表查询

 

首先在没有任何索引的情况下查看 分页情况

 

SELECT

database_id,

index_id,

index_type_desc,

index_depth,

index_level,

page_count

FROM sys.dm_db_index_physical_stats(DB_ID('IndexDB'),OBJECT_ID('Employee001'),null,null,null)

 

 

0 = Heap

1 = Clustered index

> 1 = Nonclustered index

 

总共有 1688 页

 

然后查看页的明细情况

 

TRUNCATE TABLE DBCCIndResult

INSERT INTO DBCCIndResult EXEC('DBCC IND(IndexDB,Employee001,-1)')

SELECT *

FROM [IndexDB].[dbo].[DBCCIndResult]

 

 

查看其中一笔数据页

 

--查看分页情况

SELECT * FROM DBCCIndResult

--查看页的详细数据

DBCC TRACEON(3604)

TRUNCATE TABLE DBCCPageResult

--选中一页 例如 145页

INSERT INTO DBCCPageResult EXEC ('DBCC PAGE (IndexDB, 1, 145, 3) WITH TABLERESULTS')

SELECT * FROM DBCCPageResult

WHERE Field IN('Id','Name','Department','Organization','Company')

 

 

 

在没有任何索引（聚集索引）的堆表中，所有字段都是无序排列的

 

 

查询

 

查一笔数据

 

打开包括实际的执行计划

 

 

并设置 STATISTICS IO ON 

 

SET STATISTICS IO ON

SELECT Name From Employee001

WHERE Id= '43107053D74E484EB02B5B395178F682'

 

 

查看IO

 

 

执行计划

 

 

 

另外，可以通过查看一次查询中申请的锁的情况，看看到底读取了哪些页

 

USE [IndexDB]

GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

GO

BEGIN TRAN

SET STATISTICS IO ON

SELECT Name From Employee001

WHERE Id= '43107053D74E484EB02B5B395178F682'

SET STATISTICS IO OFF

USE [IndexDB] --要查询申请锁的数据库

GO

SELECT

[request_session_id],

c.[program_name],

DB_NAME(c.[dbid]) AS dbname,

[resource_type],

[request_status],

[request_mode],

[resource_description],OBJECT_NAME(p.[object_id]) AS objectname,

p.[index_id]

FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p

ON a.[resource_associated_entity_id]=p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]

WHERE c.[dbid]=DB_ID('IndexDB') AND a.[request_session_id]=@@SPID

ORDER BY [request_session_id],[resource_type]

COMMIT TRAN

 

可以看到共申请了1692个锁，其中

页锁为1688 正好对应1688个数据页

 

1692=1688+3+1