1 --如何监视锁的申请，持有和释放 锁的数量和数据库调优的关系
2
3 --在分析不同形式的语句执行对申请锁行为的影响之前，DBA要先了解怎麽去监视
4 --一个连接当前持有的锁，以及怎麽去监视一个语句的执行过程，SQLSERVER对锁
5 --的申请和释放行为
6
7 --1、检查一个连接当前所持有的锁
8 --通常可以使用sp_lock这个命令来列出当前SQL里所有的连接所持有的锁的内容要授予先可以获得锁
9 EXEC [sys].[sp_lock]
10
11
12 --在SQL2005之后，这个功能可以由直接查询sys.dm_tran_locks这张系统动态管理视图来实现
13 SELECT [request_session_id],
14 [resource_type],
15 [resource_associated_entity_id],
16 [request_type],
17 [request_mode],
18 [resource_description]
19 FROM sys.[dm_tran_locks]
20
21
22 --当然也可以结合其他动态管理视图,直接查出某个数据库上面的锁是在哪些表格,以及哪些索引上面
23 --例如:
24 USE [GPOSDB] --要查询锁的数据库
25 GO
26 SELECT
27 [request_session_id],
28 [resource_type],
29 [resource_associated_entity_id],
30 [request_status],
31 [request_mode],
32 [resource_description],
33 p.[object_id],
34 OBJECT_NAME(p.[object_id]) AS objectname,
35 p.*
36 FROM sys.[dm_tran_locks] LEFT JOIN sys.[partitions] p ON sys.[dm_tran_locks].[resource_associated_entity_id]
37 =p.[hobt_id]
38 WHERE [resource_database_id]=DB_ID('gposdb')
39 ORDER BY [request_session_id],[resource_type],[resource_associated_entity_id]
40
41
42
43 --2、监视语句执行过程中SQL对锁的申请和释放行为
44 --有很多锁是在语句运行的过程中申请和释放的，语句运行结束之后，这些锁就会消失。
45 --如果这些锁申请不到，也会产生阻塞。那么怎麽看一个语句执行过程中锁的申请和释放过程呢？
46
47 --DBA必须借助SQLSERVER PROFILER 在定义一个trace（跟踪）的时候，需要选取下面的event（事件）
48 --：lock:accquired,lock:released 选上show all columns显示所有列和显示所有事件
49 --选上show all columns显示所有列和显示所有事件!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
50
51 --要注意选上show all columns显示所有列和显示所有事件，再选择lock:accquired和lock:released
52 --通过这种方式就能看到一个语句在执行过程中锁的完整申请和释放过程。需要提醒的是：
53 --SQL锁的申请和释放是个非常复杂的行为，同样一句话在不同情况下执行，例如（是warm cache
54 --还是cold cache，是否需要编译等）申请的锁都会不一样。有时候申请的数目可能大大超过你的
55 --想象。所以这种跟踪方式只能在测试环境里，针对特定语句进行。如果在生产环境里这么做，
56 --会产生大量跟踪记录，影响SQL性能，是个危险的行为
57
58 --一般来说，用sys.dm_tran_locks或sp_lock的结果就能够做绝大部分的阻塞分析!!!!!!!!!!!!!!!!!!!!!!!
59
60
61 --锁的数量和数据库调优的关系
62 --另一个影响SQL申请锁数目的重要因素就是语句的执行计划。下面我们先创建两张同样结构的表
63 --但是索引不同的表格。分析执行计划为什麽会对锁申请的数目有很大影响
64
65 --建立两张表 测试之前确保你的机器有[AdventureWorks]数据库因为表数据要从这个库里的
66 --[HumanResources].[Employee]表导入
67
68 ----------------------------------------------B树表聚集索引表-------------------------------------------
69 USE [AdventureWorks]
70 GO
71 DROP TABLE Employee_Demo_BTree
72 GO
73 CREATE TABLE Employee_Demo_BTree(
74 EmployeeID INT NOT NULL PRIMARY KEY,
75 NationalIDNumber NVARCHAR(15) NOT NULL,
76 ContactID INT NOT NULL,
77 LoginID NVARCHAR(256) NOT NULL,
78 ManagerID INT NULL,
79 Title NVARCHAR(50) NOT NULL,
80 BirthDate DATETIME NOT NULL,
81 MaritalStatus NCHAR(1) NOT NULL,
82 Gender NCHAR(1) NOT NULL,
83 HireDate DATETIME NOT NULL,
84 ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()
85 )
86 GO
87 --主键就已经是聚集索引了,无需再指定
88 --CREATE CLUSTERED INDEX PK_Employee_EmployeeID_Demo_BTree ON Employee_Demo_BTree(EmployeeID ASC)
89 --添加非聚集索引
90 CREATE NONCLUSTERED INDEX IX_Employee_ManagerID_Demo_BTree ON Employee_Demo_BTree([ManagerID] ASC)
91
92 CREATE NONCLUSTERED INDEX IX_Employee_ModifiedDate_Demo_BTree ON Employee_Demo_BTree( [ModifiedDate] ASC)
93
94 --插入数据
95 INSERT [dbo].[Employee_Demo_BTree]
96 SELECT [EmployeeID],
97 [NationalIDNumber],
98 [ContactID],
99 [LoginID],
100 [ManagerID],
101 [Title],
102 [BirthDate],
103 [MaritalStatus],
104 [Gender],
105 [HireDate],
106 [ModifiedDate]
107 FROM [HumanResources].[Employee]
108 GO
109
110 ----------------------------------------------堆表非聚集索引表-------------------------------------------
111 USE [AdventureWorks]
112 GO
113 DROP TABLE Employee_Demo_Heap
114 GO
115 CREATE TABLE Employee_Demo_Heap(
116 EmployeeID INT NOT NULL,
117 NationalIDNumber NVARCHAR(15) NOT NULL,
118 ContactID INT NOT NULL,
119 LoginID NVARCHAR(256) NOT NULL,
120 ManagerID INT NULL,
121 Title NVARCHAR(50) NOT NULL,
122 BirthDate DATETIME NOT NULL,
123 MaritalStatus NCHAR(1) NOT NULL,
124 Gender NCHAR(1) NOT NULL,
125 HireDate DATETIME NOT NULL,
126 ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()
127 )
128 GO
129
130 --因为没有主键所以要指定非聚集索引
131 CREATE NONCLUSTERED INDEX PK_Employee_EmployeeID_Demo_Heap ON Employee_Demo_Heap( [EmployeeID] ASC)
132 --添加非聚集索引
133 CREATE NONCLUSTERED INDEX IX_Employee_ManagerID_Demo_Heap ON Employee_Demo_BTree([ManagerID] ASC)
134
135 CREATE NONCLUSTERED INDEX IX_Employee_ModifiedDate_Demo_Heap ON Employee_Demo_BTree( [ModifiedDate] ASC)
136
137 INSERT [dbo].[Employee_Demo_Heap]
138 SELECT
139 [EmployeeID],
140 [NationalIDNumber],
141 [ContactID],
142 [LoginID],
143 [ManagerID],
144 [Title],
145 [BirthDate],
146 [MaritalStatus],
147 [Gender],
148 [HireDate],
149 [ModifiedDate]
150 FROM [HumanResources].[Employee]
151 GO
