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

 

转载于:https://www.cnblogs.com/ywnwa417/p/6035239.html