Sql Server
以下是如何找出blocking session的方式。
▋dbo.syslockinfo : 找出blocking session
|
declare @tablename
sysname
select @tablename
= 'XXXXX' --放被lock住的Table name
select convert (smallint, req_spid) As spid,
rsc_dbid As
dbid,
rsc_objid As
ObjId,
rsc_indid As
IndId,
substring (v.name, 1,
4) As Type,
substring (rsc_text, 1,
32) as Resource,
substring (u.name, 1,
8) As Mode,
substring (x.name, 1,
5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and rsc_objid
= object_id(@tablename)
|
Example:
▋dm_os_waiting_tasks
& sys.dm_exec_sessions : 列出該session詳細資訊
|
USE [master]
GO
SELECT
w.session_id
,w.wait_duration_ms
,w.wait_type
,w.blocking_session_id
,w.resource_description
,s.program_name
,t.text
,t.dbid
,s.cpu_time
,s.memory_usage
FROM sys.dm_os_waiting_tasks
w
INNER JOIN
sys.dm_exec_sessions s
ON w.session_id = s.session_id
INNER JOIN
sys.dm_exec_requests r
ON s.session_id = r.session_id
OUTER APPLY
sys.dm_exec_sql_text (r.sql_handle) t
WHERE s.is_user_process = 1
AND w.session_id=451
GO
|
Example:
從text可以看到session的TSQL。
▋Kill session
|
KILL {
session ID | UOW } [ WITH STATUSONLY ]
|
Reference: Microsoft
DOC
▋查看活動監視器或Sql profiler
▌Reference
沒有留言:
張貼留言