堆表查询
首先在没有任何索引的情况下查看 分页情况
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