sp_lock2

use master

 

go

 

if object_id('sp_lock2') is not null

  drop proc sp_lock2

 go

 

 

 

create proc sp_lock2

as

 

set nocount on

set transaction isolation level read uncommitted

-- CTRL - T 모드로 변경 하세요

-- 도구 > 옵션 > 결과 텍스트 > 글꼴 > 굴림체

 -- 마스터 에서 돌리세요

-- 최초 김민석

 -- SQL Server MVP 2006~2009

 -- by minsouk@hotmail.com

-- 수정 하만철

 -- 20100624 세션정보 추가 김민석

-- 20110826 세션정보 수정 김민석

-- 20120919 varchar(max) 변경 김민석

 

 

 

/**** object view 생성을 위로 올렸습니다~!! ****/

 

if object_id('dbo.v_objlist') is not null

 drop view v_objlist

 

declare @viewheader varchar(max), @viewbody varchar(max)

 select @viewheader ='' , @viewbody =''

 if object_id('v_objlist') is not null

drop view v_objlist

 set @viewheader = 'create view dbo.v_objlist as '

 select

@viewbody = @viewbody + 'union all select db_id('''+quotename(name)+''') dbid

  , name collate database_default name

, id

  from '+quotename(name)+'.dbo.sysobjects '+char(13)+char(10)

 from master.dbo.sysdatabases

where dbid > 4

 select @viewbody = stuff(@viewbody, 1,10, '')

 exec (@viewheader + @viewbody)

 

print @viewheader + @viewbody

 

 

 

 

print N'######################################################################'

 print N'세션정보'

 print N'######################################################################'

 

DECLARE @VERSION INT

SELECT @VERSION = SUBSTRING(CAST(SERVERPROPERTY('PRODUCTVERSION') AS VARCHAR(100))

  , 1, CHARINDEX('.',CAST(SERVERPROPERTY('PRODUCTVERSION') AS VARCHAR(100)))-1)

 

 

 

IF @VERSION >= 9 BEGIN

SELECT SESSION_ID

  , CASE TRANSACTION_ISOLATION_LEVEL

WHEN 0 THEN '지정되지 않음'

WHEN 1 THEN '커밋되지 않은 읽기'

 WHEN 2 THEN '커밋된 읽기'

 WHEN 3 THEN '##반복 읽기##'

 WHEN 4 THEN '@@직렬화 가능@@'

 WHEN 5 THEN 'XX스냅숏XX' ELSE '?' END

, *

FROM SYS.DM_EXEC_SESSIONS

  WHERE SESSION_ID > 50

 END

 

print N'######################################################################'

 print N'락인포 어뷰징 확인 200개 ver 0.1'

 print N'######################################################################'

 

select top 200

  rsc_text

  , count(*) cnt

  , case req_status

when 1 then N'허가됨'

 when 2 then N'변환중'

 when 3 then N'대기중'

  end req_status

  , max(left(db_name(rsc_dbid),30)+case when len(c.name) > 30 then '...' else '' end) dbname

, max(left(c.name,30)+case when len(c.name) > 30 then '...' else '' end) objname

, max(rsc_indid) IndId

  , max(case rsc_type

when 1 then null

  when 2 then 'DB'

  when 3 then 'File'

  when 4 then 'Index'

  when 5 then 'Table'

  when 6 then 'Page'

  when 7 then 'Key'

  when 8 then 'Extent'

  when 9 then 'RID'

  when 10 then 'App'

  end) Type

  , max(case req_mode --(0,3,6,7,8,9)

  when 0 then null

  when 1 then N'Sch-S:스키마 안전성'

 when 2 then N'Sch-M:스키마 수정'

 when 3 then N'S:공유'

 when 4 then N'U:업데이트'

 when 5 then N'X:단독'

 when 6 then N'IS:내재 공유'

 when 7 then N'IU:내재 업데이트'

 when 8 then N'IX:내재 단독'

 when 9 then N'SIU:공유 내재 업데이트'

 when 10 then N'SIX:공유 내재 단독'

 when 11 then N'UIX:업데이트 내재 단독'

 when 12 then N'BU:대량 작업'

 when 13 then N'RangeS_S:공유 키 범위 및 공유 리소스'

 when 14 then N'RangeS_U:공유 키 범위 및 업데이트 리소스'

 when 15 then N'RangeI_N:삽입 키 범위 및 Null 리소스'

 when 16 then N'RangeI_S:RangeI_N 및 S 잠금의 겹침으로 만들어진 키 범위 변환'

 when 17 then N'RangeI_U:RangeI_N 및 U 잠금의 겹침으로 만들어진 키 범위 변환'

 when 18 then N'RangeI_X:RangeI_N 및 X 잠금의 겹침으로 만들어진 키 범위 변환'

 when 19 then N'RangeX_S:RangeI_N 및 RangeS_S. 잠금의 겹침으로 만들어진 키 범위 변환'

 when 20 then N'RangeX_U:RangeI_N 및 RangeS_U 잠금의 겹침으로 만들어진 키 범위 변환'

 when 21 then N'RangeX_X:단독 키 범위 및 단독 리소스'

  end) Mode

  , max(case req_ownertype

when 1 then N'트랜잭션'

 when 2 then N'커서'

 when 3 then N'세션'

 when 4 then N'ExSession'

  end) req_ownertype

 from

master.dbo.syslockinfo a with (nolock)

  left join master.dbo.v_objlist c with (nolock)

  on c.dbid = a.rsc_dbid

  and c.id = a.rsc_objid

 where

req_spid <> @@spid

 -- and req_status = 1

  and rsc_type <> 2

group by req_status, rsc_text

 order by req_status, count(*) desc

 

/**** N' 추가했습니다~!! ****/

 print N'######################################################################'

 print N'헤드블럭만 보기 by minsouk@hotmail.com ver 0.1'

 print N'######################################################################'

 

select *

 from master.dbo.sysprocesses

where blocked = 0

and spid in (select blocked from master.dbo.sysprocesses where blocked <> 0)

 

print N'######################################################################'

 print N'헤드블럭 쿼리보기 by minsouk@hotmail.com ver 0.1'

 print N'######################################################################'

 

 

 

/**** adhoc 경우 dbid, objectid 가 null 이라 dbname 보여주기위해 dbid 추가 했습니다!! ****/

 declare cur_headblock cursor fast_forward

for

select spid, sql_handle, dbid

  from master.dbo.sysprocesses

where blocked = 0

and spid in (select blocked from master.dbo.sysprocesses where blocked <> 0)

 declare @spid varchar(6)

 declare @dbid int

 declare @handle varbinary(64);

 open cur_headblock

 fetch next from cur_headblock into @spid, @handle, @dbid

 while (@@fetch_status != -1)

 begin

  print '#########################'

  print 'dbcc inputbuffer for spid ' + @spid

print '#########################'

 

 /***** adhoc, proc 구분하고 objname 보게 바꿔봤습니다~!! ****/

  select case when fn.dbid is null then 'AdHoc' else 'Proc' end as qry_type, db_name(@dbid)

dbname, vo.name as objname, [text]

from ::fn_get_sql(@handle) fn

  left outer join v_objlist vo on fn.dbid = vo.dbid and fn.objectid = vo.id

  exec ('dbcc inputbuffer (' + @spid + ')')

  fetch next from cur_headblock into @spid, @handle, @dbid

 end

 deallocate cur_headblock

 

print N'######################################################################'

 print N'락트리 보기 by minsouk@hotmail.com ver 0.2'

 print N'######################################################################'

 

if object_id ('tempdb..#tbl_sysprocesses') is not null

  drop table #tbl_sysprocesses

create table #tbl_sysprocesses

 (

depth int

  , tree varchar(7000)

  , spid int

  , blocked int

  --, sql_handle varbinary(64)

 )

 

insert into #tbl_sysprocesses (depth, tree, spid, blocked)

select 0, cast(spid as varchar(100)) spid , spid, blocked

 from master.dbo.sysprocesses

where blocked = 0

and spid in (select blocked from master.dbo.sysprocesses where blocked <> 0)

 

declare @max_depth int

 set @max_depth = 5

 

while (1=1)

 begin

insert into #tbl_sysprocesses (depth, tree, spid, blocked)

select a.depth + 1 depth , a.tree + ' > ' +cast(b.spid as varchar(8000)) tree , b.spid, b.blocked

  from #tbl_sysprocesses a

  inner join master.dbo.sysprocesses b

  on a.spid = b.blocked

  where depth in (select max(depth) from #tbl_sysprocesses)

  and b.spid <> b.blocked

  if @@rowcount = 0 break

  set @max_depth = @max_depth - 1

  if @max_depth <= 1 break

end

declare @cnt varchar(10)

 select @cnt = cast(cnt as varchar(10)) from ( select count(*) cnt from sysprocesses where blocked <> 0 ) a

 

print N'######################################################################'

 print N'블럭카운트 : '+@cnt

print N'######################################################################'

 

select convert(char(10), cast((b.waittime / 1000) * 1.1574074074074073E-5 as datetime) , 108) as[hh:mm:ss]

  , left(a.tree, 40)+case when len(a.tree) > 40 then '...' else '' end locktree, b.*

 from #tbl_sysprocesses a

  inner join master.dbo.sysprocesses b

  on a.spid = b.spid

order by tree

 

print N'######################################################################'

 print N'######################################################################'

 print N'######################################################################'

 print N'락인포 보기 by minsouk@hotmail.com ver 0.5'

 print N'######################################################################'

 print N'######################################################################'

 print N'######################################################################'

 print N''

 

/*

 if object_id ('dbo.usp_create_v_objlist') is not null

 drop proc dbo.usp_create_v_objlist

 */

 

--exec dbo.usp_create_v_objlist

 

--set rowcount 200

 

print N'######################################################################'

 print N'락인포 허가 200개 exclude rsc_type db by minsouk@hotmail.com ver 0.6'

 print N'######################################################################'

 

select top 200

  req_spid spid

  , left(db_name(rsc_dbid),30)+case when len(c.name) > 30 then '...' else '' end dbname

, left(c.name,30)+case when len(c.name) > 30 then '...' else '' end objname

, rsc_indid IndId

  , case rsc_type

when 1 then null

  when 2 then 'DB'

  when 3 then 'File'

  when 4 then 'Index'

  when 5 then 'Table'

  when 6 then 'Page'

  when 7 then 'Key'

  when 8 then 'Extent'

  when 9 then 'RID'

  when 10 then 'App'

  end Type

  , rsc_type

  , rsc_text

  , case req_mode --(0,3,6,7,8,9)

  when 0 then null

  when 1 then N'Sch-S:스키마 안전성'

 when 2 then N'Sch-M:스키마 수정'

 when 3 then N'S:공유'

 when 4 then N'U:업데이트'

 when 5 then N'X:단독'

 when 6 then N'IS:내재 공유'

 when 7 then N'IU:내재 업데이트'

 when 8 then N'IX:내재 단독'

 when 9 then N'SIU:공유 내재 업데이트'

 when 10 then N'SIX:공유 내재 단독'

 when 11 then N'UIX:업데이트 내재 단독'

 when 12 then N'BU:대량 작업'

 when 13 then N'RangeS_S:공유 키 범위 및 공유 리소스'

 when 14 then N'RangeS_U:공유 키 범위 및 업데이트 리소스'

 when 15 then N'RangeI_N:삽입 키 범위 및 Null 리소스'

 when 16 then N'RangeI_S:RangeI_N 및 S 잠금의 겹침으로 만들어진 키 범위 변환'

 when 17 then N'RangeI_U:RangeI_N 및 U 잠금의 겹침으로 만들어진 키 범위 변환'

 when 18 then N'RangeI_X:RangeI_N 및 X 잠금의 겹침으로 만들어진 키 범위 변환'

 when 19 then N'RangeX_S:RangeI_N 및 RangeS_S. 잠금의 겹침으로 만들어진 키 범위 변환'

 when 20 then N'RangeX_U:RangeI_N 및 RangeS_U 잠금의 겹침으로 만들어진 키 범위 변환'

 when 21 then N'RangeX_X:단독 키 범위 및 단독 리소스'

  end Mode

  , req_mode

  , case req_status

when 1 then N'허가됨'

 when 2 then N'변환중'

 when 3 then N'대기중'

  end req_status

  , req_refcnt

, req_lifetime

, req_ecid [req_ecid (isParallel)]

  , case req_ownertype

when 1 then N'트랜잭션'

 when 2 then N'커서'

 when 3 then N'세션'

 when 4 then N'ExSession'

  end req_ownertype

  , req_transactionID

  , req_transactionUOW [req_transactionUOW (isDTC)]

 from

master.dbo.syslockinfo a with (nolock)

  left join master.dbo.v_objlist c with (nolock)

  on c.dbid = a.rsc_dbid

  and c.id = a.rsc_objid

 where

req_spid <> @@spid

  and req_status = 1

  and rsc_type <> 2

order by

spid -- 정렬

 

print N'######################################################################'

 print N'락인포 변환 200개 by minsouk@hotmail.com ver 0.5'

 print N'######################################################################'

 

select top 200

  req_spid spid

  , left(db_name(rsc_dbid),30)+case when len(c.name) > 30 then '...' else '' end dbname

, left(c.name,30)+case when len(c.name) > 30 then '...' else '' end objname

, rsc_indid IndId

  , case rsc_type

when 1 then null

  when 2 then 'DB'

  when 3 then 'File'

  when 4 then 'Index'

  when 5 then 'Table'

  when 6 then 'Page'

  when 7 then 'Key'

  when 8 then 'Extent'

  when 9 then 'RID'

  when 10 then 'App'

  end Type

  , rsc_type

  , rsc_text

  , case req_mode --(0,3,6,7,8,9)

  when 0 then null

  when 1 then N'Sch-S:스키마 안전성'

 when 2 then N'Sch-M:스키마 수정'

 when 3 then N'S:공유'

 when 4 then N'U:업데이트'

 when 5 then N'X:단독'

 when 6 then N'IS:내재 공유'

 when 7 then N'IU:내재 업데이트'

 when 8 then N'IX:내재 단독'

 when 9 then N'SIU:공유 내재 업데이트'

 when 10 then N'SIX:공유 내재 단독'

 when 11 then N'UIX:업데이트 내재 단독'

 when 12 then N'BU:대량 작업'

 when 13 then N'RangeS_S:공유 키 범위 및 공유 리소스'

 when 14 then N'RangeS_U:공유 키 범위 및 업데이트 리소스'

 when 15 then N'RangeI_N:삽입 키 범위 및 Null 리소스'

 when 16 then N'RangeI_S:RangeI_N 및 S 잠금의 겹침으로 만들어진 키 범위 변환'

 when 17 then N'RangeI_U:RangeI_N 및 U 잠금의 겹침으로 만들어진 키 범위 변환'

 when 18 then N'RangeI_X:RangeI_N 및 X 잠금의 겹침으로 만들어진 키 범위 변환'

 when 19 then N'RangeX_S:RangeI_N 및 RangeS_S. 잠금의 겹침으로 만들어진 키 범위 변환'

 when 20 then N'RangeX_U:RangeI_N 및 RangeS_U 잠금의 겹침으로 만들어진 키 범위 변환'

 when 21 then N'RangeX_X:단독 키 범위 및 단독 리소스'

  end Mode

  , req_mode

  , case req_status

when 1 then N'허가됨'

 when 2 then N'변환중'

 when 3 then N'대기중'

  end req_status

  , req_refcnt

, req_lifetime

, req_ecid [req_ecid (isParallel)]

  , case req_ownertype

when 1 then N'트랜잭션'

 when 2 then N'커서'

 when 3 then N'세션'

 when 4 then N'ExSession'

  end req_ownertype

  , req_transactionID

  , req_transactionUOW [req_transactionUOW (isDTC)]

 from

master.dbo.syslockinfo a with (nolock)

  left join master.dbo.v_objlist c with (nolock)

  on c.dbid = a.rsc_dbid

  and c.id = a.rsc_objid

 where

req_spid <> @@spid and req_status = 2

 order by

spid -- 정렬

 

print N'######################################################################'

 print N'락인포 대기 200개 by minsouk@hotmail.com ver 0.5'

 print N'######################################################################'

 

select top 200

  req_spid spid

  , left(db_name(rsc_dbid),30)+case when len(c.name) > 30 then '...' else '' end dbname

, left(c.name,30)+case when len(c.name) > 30 then '...' else '' end objname

, rsc_indid IndId

  , case rsc_type

when 1 then null

  when 2 then 'DB'

  when 3 then 'File'

  when 4 then 'Index'

  when 5 then 'Table'

  when 6 then 'Page'

  when 7 then 'Key'

  when 8 then 'Extent'

  when 9 then 'RID'

  when 10 then 'App'

  end Type

  , rsc_type

  , rsc_text

  , case req_mode --(0,3,6,7,8,9)

  when 0 then null

  when 1 then N'Sch-S:스키마 안전성'

 when 2 then N'Sch-M:스키마 수정'

 when 3 then N'S:공유'

 when 4 then N'U:업데이트'

 when 5 then N'X:단독'

 when 6 then N'IS:내재 공유'

 when 7 then N'IU:내재 업데이트'

 when 8 then N'IX:내재 단독'

 when 9 then N'SIU:공유 내재 업데이트'

 when 10 then N'SIX:공유 내재 단독'

 when 11 then N'UIX:업데이트 내재 단독'

 when 12 then N'BU:대량 작업'

 when 13 then N'RangeS_S:공유 키 범위 및 공유 리소스'

 when 14 then N'RangeS_U:공유 키 범위 및 업데이트 리소스'

 when 15 then N'RangeI_N:삽입 키 범위 및 Null 리소스'

 when 16 then N'RangeI_S:RangeI_N 및 S 잠금의 겹침으로 만들어진 키 범위 변환'

 when 17 then N'RangeI_U:RangeI_N 및 U 잠금의 겹침으로 만들어진 키 범위 변환'

 when 18 then N'RangeI_X:RangeI_N 및 X 잠금의 겹침으로 만들어진 키 범위 변환'

 when 19 then N'RangeX_S:RangeI_N 및 RangeS_S. 잠금의 겹침으로 만들어진 키 범위 변환'

 when 20 then N'RangeX_U:RangeI_N 및 RangeS_U 잠금의 겹침으로 만들어진 키 범위 변환'

 when 21 then N'RangeX_X:단독 키 범위 및 단독 리소스'

  end Mode

  , req_mode

  , case req_status

when 1 then N'허가됨'

 when 2 then N'변환중'

 when 3 then N'대기중'

  end req_status

  , req_refcnt

, req_lifetime

, req_ecid [req_ecid (isParallel)]

  , case req_ownertype

when 1 then N'트랜잭션'

 when 2 then N'커서'

 when 3 then N'세션'

 when 4 then N'ExSession'

  end req_ownertype

  , req_transactionID

  , req_transactionUOW [req_transactionUOW (isDTC)]

 from

master.dbo.syslockinfo a with (nolock)

  left join master.dbo.v_objlist c with (nolock)

  on c.dbid = a.rsc_dbid

  and c.id = a.rsc_objid

 where

req_spid <> @@spid

  and req_status = 3

 order by

spid -- 정렬

 

set rowcount 0

 

print N'######################################################################'

 print N'블럭되는 쿼리보기 sql_handle 별 50개 by minsouk@hotmail.com ver 0.2'

 print N'######################################################################'

 

declare cur_blocked cursor fast_forward

for

select top 50 max(spid) spid, sql_handle, max(dbid) dbid from sysprocesses where blocked <> 0

group by sql_handle

 --declare @spid varchar(6)

 --declare @handle varbinary(64)

 open cur_blocked

 fetch next from cur_blocked into @spid, @handle, @dbid

 while (@@fetch_status != -1)

 begin

  print '|||||||||||||||||||||||||'

  print 'dbcc inputbuffer for spid ' + @spid

  print '|||||||||||||||||||||||||'

  select case when fn.dbid is null then 'AdHoc' else 'Proc' end as qry_type, db_name(@dbid)

dbname, vo.name as objname, [text]

from ::fn_get_sql(@handle) fn

  left outer join v_objlist vo on fn.dbid = vo.dbid and fn.objectid = vo.id

  exec ('dbcc inputbuffer (' + @spid + ')')

  fetch next from cur_blocked into @spid, @handle, @dbid

 end

 deallocate cur_blocked

 

go

 

exec dbo.sp_lock2

 

go

출처 : http://sqlsql.tistory.com/8