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