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

 

 

 

 

查询结果

 

 

 

杀死死锁

KILL spid

KILL 101

 

SQL ProFile 的使用