SQLSERVER中KeyHashValue的作用（上）

SQLSERVER中KeyHashValue的作用（上）

SQLSERVER中KeyHashValue的作用（下）

原文的标题是：SQLSERVER在索引下如何找到哈希值的随想

现在知道KeyHashValue的作用了，所以就改了标题~

 

测试环境：SQLSERVER2005 开发者版

问题是这样的：

当通过聚集索引查找和非聚集索引查找的时候，通过哈希码来匹配，然后找到相应记录的

既然通过哈希码来匹配，那么就需要一个hash bucket把所有索引页面的所有key/value全部加载到hash bucket

既然要全部加载到hash bucket就需要读取所有的索引页

我的测试脚本，我使用SET STATISTICS IO ON来测试是否有读取索引页的情况，但是到最后还是找不到规律

  1 --sql在聚集索引下如何找到哈希值的随想
  2 
  3 USE master
  4 GO
  5 --新建数据库IAMDB
  6 CREATE DATABASE SCANDB
  7 GO
  8 
  9 USE SCANDB
 10 GO
 11 
 12 
 13 
 14 --DROP TABLE clusteredtable
 15 --DROP TABLE nonclusteredtable
 16 
 17 
 18 --建立测试表
 19 CREATE TABLE clusteredtable(c1 INT IDENTITY(1,1), c2 VARCHAR (900))
 20 GO
 21 CREATE TABLE nonclusteredtable(c1 INT IDENTITY(1,1), c2 VARCHAR (900))
 22 GO
 23 
 24 
 25 --建立索引
 26 CREATE CLUSTERED INDEX cix_clusteredtable ON clusteredtable([C2])
 27 GO
 28 CREATE  INDEX ix_nonclusteredtable ON nonclusteredtable([C2])
 29 GO
 30 
 31 
 32 --插入测试数据
 33 DECLARE @a INT;
 34 SELECT @a = 1;
 35 WHILE (@a <= 100)
 36 BEGIN
 37     INSERT INTO clusteredtable VALUES ( CAST(@a AS NVARCHAR(2))+replicate('a', 880))
 38     SELECT @a = @a + 1
 39 END
 40 
 41 
 42 DECLARE @a INT;
 43 SELECT @a = 1;
 44 WHILE (@a <= 100)
 45 BEGIN
 46     INSERT INTO nonclusteredtable VALUES ( CAST(@a AS NVARCHAR(2))+replicate('a', 880))
 47     SELECT @a = @a + 1
 48 END
 49 
 50 
 51 
 52 
 53 --查询数据
 54 SELECT * FROM clusteredtable  ORDER BY [c1] ASC
 55 SELECT * FROM nonclusteredtable  ORDER BY [c1] ASC
 56 
 57 
 58 CREATE TABLE DBCCResult (
 59 PageFID NVARCHAR(200),
 60 PagePID NVARCHAR(200),
 61 IAMFID NVARCHAR(200),
 62 IAMPID NVARCHAR(200),
 63 ObjectID NVARCHAR(200),
 64 IndexID NVARCHAR(200),
 65 PartitionNumber NVARCHAR(200),
 66 PartitionID NVARCHAR(200),
 67 iam_chain_type NVARCHAR(200),
 68 PageType NVARCHAR(200),
 69 IndexLevel NVARCHAR(200),
 70 NextPageFID NVARCHAR(200),
 71 NextPagePID NVARCHAR(200),
 72 PrevPageFID NVARCHAR(200),
 73 PrevPagePID NVARCHAR(200)
 74 )
 75 
 76 TRUNCATE TABLE [dbo].[DBCCResult]
 77 
 78 INSERT INTO DBCCResult EXEC ('DBCC IND(SCANDB,nonclusteredtable,-1) ')
 79 
 80 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
 81 
 82 DBCC TRACEON(3604,-1)
 83 GO
 84 DBCC PAGE(SCANDB,1,89,3) 
 85 GO
 86 
 87 checkpoint 
 88 DBCC DROPCLEANBUFFERS
 89 DBCC freesystemcache('all')
 90 GO
 91 -----------------------------------
 92 SET STATISTICS IO ON
 93 GO
 94 --聚集索引查找
 95 SELECT * FROM clusteredtable WHERE [c2]='18aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
 96 SET STATISTICS IO OFF
 97 GO
 98 
 99 
100 
101 (1 行受影响)
102 表 'clusteredtable'。扫描计数 1，逻辑读取 4 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
103 
104 
105 
106 
107 ----------------------------------------------------------------------------------------
108 checkpoint 
109 DBCC DROPCLEANBUFFERS
110 DBCC freesystemcache('all')
111 GO
112 -----------------------------------
113 SET STATISTICS IO ON
114 GO
115 --索引查找  、RID查找 、嵌套循环
116 SELECT * FROM nonclusteredtable WHERE [c2]='17aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
117 SET STATISTICS IO OFF
118 GO
119 
120 
121 
122 (1 行受影响)
123 表 'nonclusteredtable'。扫描计数 1，逻辑读取 5 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
View Code

聚集索引表的情况

非聚集索引表的情况

 

 

 

总结

下面查询中，除了常规的列选择外，%%lockres%% 被用作 KeyHashValue，
而 sys.fn_physlocformatter(%%physloc%%) 用于获取物理位置信息，例如文件、页面和槽位号，可以帮助您了解数据的物理存储细节。

select name,%%lockres%% as KeyHashValue
,sys.fn_physlocformatter(%%physloc%%) as file_page_slot
from tab with(index(idx_tab_name))


%%lockress%%：得到数据行对应的物理路径的哈希值（ KeyHashValue），是一个虚拟列，用来代表行级锁定资源，当 SQL Server 对数据行执行锁定时，%%lockres%% 列可用于确定特定行的锁定资源。
这个哈希值在排查锁定问题时特别有用，例如，在分析死锁图或者锁定争用的情况时。

%%physloc%%：得到数据行对应的物理路径，是一个undocument的内置变量，使用sys.fn_PhysLocFormatter 将得到的binary数据转换为一个易于阅读的格式

sys.fn_physlocformatter(%%physloc%%) 函数：将数据行对应的物理位置（%%physloc%% 提供的）转换为一个易于阅读的格式，类似格式为（1:8880:0）文件号:页面号:行号




 

posted @ 2013-12-12 21:55  桦仔  阅读(3505)  评论(2)  编辑  收藏  举报