SQLSERVER中KeyHashValue的作用（下）

php中文网

发布时间：2016-06-07 15:45:45

1446人浏览过

来源于php中文网

原创

1 SELECT   %%lockres%% AS '数据页的keyhashvalue' FROM  表名

1 DBCC TRACEON(3604,-1)
2 GO
3 DBCC PAGE(testhashkey,1,126,3) 
4 GO

 1 USE master
 2 GO
 3 CREATE DATABASE testhashkey
 4 GO
 5 
 6 USE testhashkey
 7 GO
 8 
 9 --------------------------------------------
10 --测试聚集索引
11 CREATE TABLE testcluster
12     (
13       a NVARCHAR(3800) NOT NULL ,
14       b INT NOT NULL
15     )
16 GO
17 
18 --这里一定要是唯一的
19 CREATE UNIQUE  CLUSTERED INDEX ucl ON testcluster(b)
20 GO
21 
22 INSERT  testcluster
23 SELECT  CAST(11 AS VARCHAR(10))+replicate('a', 3500),1 UNION ALL
24 SELECT  CAST(22 AS VARCHAR(10))+replicate('a', 3500),2

 1 CREATE TABLE DBCCResult (
 2 PageFID NVARCHAR(200),
 3 PagePID NVARCHAR(200),
 4 IAMFID NVARCHAR(200),
 5 IAMPID NVARCHAR(200),
 6 ObjectID NVARCHAR(200),
 7 IndexID NVARCHAR(200),
 8 PartitionNumber NVARCHAR(200),
 9 PartitionID NVARCHAR(200),
10 iam_chain_type NVARCHAR(200),
11 PageType NVARCHAR(200),
12 IndexLevel NVARCHAR(200),
13 NextPageFID NVARCHAR(200),
14 NextPagePID NVARCHAR(200),
15 PrevPageFID NVARCHAR(200),
16 PrevPagePID NVARCHAR(200)
17 )
18 
19 TRUNCATE TABLE [dbo].[DBCCResult]
20 
21 INSERT INTO DBCCResult EXEC ('DBCC IND(testhashkey,testcluster,-1) ')
22 
23 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

1 select %%lockres%% AS '数据页的keyhashvalue' from testcluster

 1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 2 GO
 3 BEGIN TRANSACTION
 4 SELECT b FROM [dbo].[testcluster] WHERE [b]=1
 5 --COMMIT TRAN
 6 
 7 SELECT  resource_type ,
 8         resource_database_id ,
 9         resource_description ,
10         resource_associated_entity_id ,
11         request_mode ,
12         request_type ,
13         request_status
14 FROM    sys.dm_tran_locks
15 WHERE   [resource_database_id]=DB_ID('testhashkey')
16 ORDER BY [resource_type]

 1 USE master
 2 GO
 3 CREATE DATABASE practice
 4 GO
 5 
 6 USE practice
 7 GO
 8 
 9 --只有聚集索引
10 CREATE TABLE Department(
11     DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
12     Name NVARCHAR(200) NOT NULL,
13     GroupName NVARCHAR(200) NOT NULL,
14     Company NVARCHAR(300),
15     ModifiedDate datetime NOT NULL  DEFAULT (getdate())
16 )
17 
18 INSERT INTO Department(name,[Company],groupname) VALUES('销售部','中国你好有限公司XX分公司','销售组')
19 GO 100000
20 
21 CREATE TABLE DBCCResult (
22 PageFID NVARCHAR(200),
23 PagePID NVARCHAR(200),
24 IAMFID NVARCHAR(200),
25 IAMPID NVARCHAR(200),
26 ObjectID NVARCHAR(200),
27 IndexID NVARCHAR(200),
28 PartitionNumber NVARCHAR(200),
29 PartitionID NVARCHAR(200),
30 iam_chain_type NVARCHAR(200),
31 PageType NVARCHAR(200),
32 IndexLevel NVARCHAR(200),
33 NextPageFID NVARCHAR(200),
34 NextPagePID NVARCHAR(200),
35 PrevPageFID NVARCHAR(200),
36 PrevPagePID NVARCHAR(200)
37 )
38 
39 TRUNCATE TABLE [dbo].[DBCCResult]
40 
41 INSERT INTO DBCCResult EXEC ('DBCC IND(practice,Department,-1) ')
42 
43 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
44 
45 DBCC TRACEON(3604,-1)
46 GO
47 DBCC PAGE(practice,1,114,3) 
48 GO
49 
50 select %%lockres%% AS '数据页的keyhashvalue' from Department
51 
52 
53 
54 
55 
56 
57 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
58 GO
59 BEGIN TRANSACTION
60 SELECT DepartmentID FROM Department WHERE DepartmentID BETWEEN 0 AND 122 
61 --COMMIT TRAN
62 
63 SELECT  resource_type ,
64         resource_database_id ,
65         resource_description ,
66         resource_associated_entity_id ,
67         request_mode ,
68         request_type ,
69         request_status
70 FROM    sys.dm_tran_locks
71 WHERE   [resource_database_id]=DB_ID('practice') 
72 AND [resource_description] LIKE '%b03b%'
73 ORDER BY [resource_type]

 1 --测试非聚集索引
 2 USE [testhashkey]
 3 GO
 4 CREATE TABLE testnoncluster
 5     (
 6       a NVARCHAR(3800) NOT NULL ,
 7       b INT NOT NULL
 8     )
 9 GO
10 
11 CREATE UNIQUE NONCLUSTERED INDEX ucil ON testnoncluster(b)
12 GO
13 
14 INSERT  testnoncluster
15 SELECT  CAST(11 AS VARCHAR(10))+replicate('a', 3500),1 UNION ALL
16 SELECT  CAST(22 AS VARCHAR(10))+replicate('a', 3500),2
17 
18 
19 SELECT * FROM [dbo].[testnoncluster]

 1 TRUNCATE TABLE [dbo].[DBCCResult]
 2 
 3 INSERT INTO DBCCResult EXEC ('DBCC IND(testhashkey,testnoncluster,-1) ')
 4 
 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
 6 
 7 DBCC TRACEON(3604,-1)
 8 GO
 9 DBCC PAGE(testhashkey,1,77,3) 
10 GO

1 select %%lockres%% AS '数据页的keyhashvalue' from testnoncluster

 1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 2 GO
 3 BEGIN TRANSACTION
 4 SELECT b FROM [dbo].[testnoncluster] WHERE [b]=1
 5 --COMMIT TRAN
 6 
 7 SELECT  resource_type ,
 8         resource_database_id ,
 9         resource_description ,
10         resource_associated_entity_id ,
11         request_mode ,
12         request_type ,
13         request_status
14 FROM    sys.dm_tran_locks
15 WHERE   [resource_database_id]=DB_ID('testhashkey')
16 ORDER BY [resource_type]

 1 --测试堆表和非聚集索引表的数据页也有keyhashvalue
 2 USE [testhashkey]
 3 GO
 4 
 5 
 6 CREATE TABLE testnoncluster2
 7     (
 8       a NVARCHAR(3800) NOT NULL ,
 9       b INT NOT NULL,
10       c INT NOT NULL 
11     )
12 GO
13 
14 
15 
16 CREATE UNIQUE NONCLUSTERED INDEX ucil ON testnoncluster2(b)
17 GO
18 
19 INSERT  testnoncluster2
20 SELECT  CAST(11 AS VARCHAR(10))+replicate('a', 3500),1,1 UNION ALL
21 SELECT  CAST(22 AS VARCHAR(10))+replicate('a', 3500),2,2
22 
23 
24 
25 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
26 GO
27 BEGIN TRANSACTION
28 DELETE FROM [dbo].[testnoncluster2]  WHERE [c]=1
29 --ROLLBACK TRAN
30 
31 SELECT  resource_type ,
32         resource_database_id ,
33         resource_description ,
34         resource_associated_entity_id ,
35         request_mode ,
36         request_type ,
37         request_status
38 FROM    sys.dm_tran_locks
39 WHERE   [resource_database_id]=DB_ID('testhashkey') 
40 ORDER BY [resource_type]
41 
42 
43 
44 
45 select %%lockres%% AS '数据页的keyhashvalue' from testnoncluster2

 1 --测试堆表和非聚集索引表的数据页也有keyhashvalue
 2 USE [testhashkey]
 3 GO
 4 
 5 
 6 
 7 CREATE TABLE testnoncluster2
 8     (
 9       a NVARCHAR(3800) NOT NULL ,
10       b INT NOT NULL,
11       c INT NOT NULL 
12     )
13 GO
14 
15 
16 
17 CREATE UNIQUE NONCLUSTERED INDEX ucil ON testnoncluster2(b)
18 GO
19 
20 INSERT  testnoncluster2
21 SELECT  CAST(11 AS VARCHAR(10))+replicate('a', 3500),1,1 UNION ALL
22 SELECT  CAST(22 AS VARCHAR(10))+replicate('a', 3500),2,2
23 
24 
25 
26 
27 
28 TRUNCATE TABLE [dbo].[DBCCResult]
29 
30 INSERT INTO DBCCResult EXEC ('DBCC IND(testhashkey,testnoncluster2,-1) ')
31 
32 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
33 
34 DBCC TRACEON(3604,-1)
35 GO
36 DBCC PAGE(testhashkey,1,165,3) 
37 GO