SQL ProFile性能分析监控工具 的使用以及死锁查询与kill

SQL ProFile性能分析监控工具:

 

 

 

 

 

 

死锁模拟：新建两个执行页面并执行一下代码

BEGIN TRAN 

UPDATE   SysUserDept SET BatchNo='SADA'


ROLLBACK TRAN

 

死锁查询

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'


select t1.resource_type                                [资源锁定类型]
     , DB_NAME(resource_database_id)                as 数据库名
     , t1.resource_associated_entity_id                锁定对象
     , t1.request_mode                              as 等待者请求的锁定模式
     , t1.request_session_id                           等待者SID
     , t2.wait_duration_ms                             等待时间
     , (select TEXT
        from sys.dm_exec_requests r
               cross apply
             sys.dm_exec_sql_text(r.sql_handle)
        where r.session_id = t1.request_session_id) as 等待者要执行的SQL
     , t2.blocking_session_id                          [锁定者SID]
     , (select TEXT
        from sys.sysprocesses p
               cross apply
             sys.dm_exec_sql_text(p.sql_handle)
        where p.spid = t2.blocking_session_id
)                                                      锁定者执行语句
from sys.dm_tran_locks t1,
     sys.dm_os_waiting_tasks t2
where t1.lock_owner_address = t2.resource_address

 

 1 SELECT 
 2     t1.resource_type AS [资源锁定类型],
 3     DB_NAME(t1.resource_database_id) AS [数据库名],
 4     t1.resource_associated_entity_id AS [锁定对象],
 5     t1.request_mode AS [等待者请求的锁定模式],
 6     t1.request_session_id AS [等待者SID],
 7     -- 等待者信息：IP + 计算机名
 8     --ws.* AS [等待者IP地址],
 9     ws.host_name AS [等待者计算机名],
10     t2.wait_duration_ms AS [等待时间(ms)],
11     -- 等待者执行的SQL（处理NULL）
12     ISNULL((SELECT TEXT
13             FROM sys.dm_exec_requests r
14             CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
15             WHERE r.session_id = t1.request_session_id), '无') AS [等待者要执行的SQL],
16     t2.blocking_session_id AS [锁定者SID],
17     -- 锁定者信息：IP + 计算机名（处理锁定者SID为0的情况）
18    -- bs.client_net_address AS [锁定者IP地址],
19     bs.host_name AS [锁定者计算机名],
20     -- 锁定者执行的SQL（处理NULL）
21     ISNULL((SELECT TEXT
22             FROM sys.sysprocesses p
23             CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
24             WHERE p.spid = t2.blocking_session_id), '无') AS [锁定者执行语句]
25 FROM sys.dm_tran_locks t1
26 INNER JOIN sys.dm_os_waiting_tasks t2 
27     ON t1.lock_owner_address = t2.resource_address  -- 替换笛卡尔积为INNER JOIN，更规范
28 -- 关联等待者的会话信息（获取IP/计算机名）
29 LEFT JOIN sys.dm_exec_sessions ws 
30     ON t1.request_session_id = ws.session_id
31 -- 关联锁定者的会话信息（获取IP/计算机名）
32 LEFT JOIN sys.dm_exec_sessions bs 
33     ON t2.blocking_session_id = bs.session_id;

 

 

 

 

查询结果

 

 

 

杀死死锁

KILL spid

KILL 101

 

SQL ProFile 的使用

 

posted @ 2021-03-09 17:00  JackDDD  阅读(158)  评论(0)    收藏  举报