(─━┘_└━─)/ 머리만1톤(Head1Ton)

데이터전송최적화_script.sql

병렬처리_실전_쿼리_튜닝_2012_04_23.sql

[스크립트 13-4]

[스크립트 13-5]

[스크립트 13-6

[스크립트 11-10]

[스크립트 11-11]

[스크립트 11-12]

[스크립트 11-13]

[스크립트 11-14]

[스크립트 11-15]

[스크립트 11-16]

[스크립트 11-17]

[스크립트 11-18]

[스크립트 11-19]

[스크립트 11-20]

[스크립트 11-27]

[예제실습 소스 코드]

블럭관련 정보 출력

-- @@ 바뀐 내용
- 어뷰징 확인을 위해 동일한 리소스를 얼마나 많은 사람들이 대기하고 있는지 보여줌
- set rowcount로 페이지를 자르던 부분을 top 구문으로 변경
- 락인포 5000건 -> 200건

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 세션정보 추가 김민석

-- 20110825 세션정보 버전정보 연산자 수정

/**** object view 생성을 위로 올렸습니다~!! ****/
declare @viewheader varchar(8000), @viewbody varchar(8000)
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 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 sp_lock2