블로그 이미지
SQL Server VS. 석이 minsouk@hotmail.com MSSQL 쿼리성능 관련해 궁금한 사항이 있다면 언제나 누구나 TeamViewer + Line (네이버 japan 메신저) 에 minsouk1 추가 후 연락주세요~ 010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (441)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total196,518
Today21
Yesterday61

달력

« » 2017.12
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31            

공지사항


미리읽기란? 

I/O 와 CPU 연산을 동시에 수행해 그 수행 성능을 최대로 만들어 보겠다 라는 최적화 알고리즘이다.
상식적으로 읽고 연산 해야 하지만, "미리 쓰일것 같아, 먼저 읽어 두겠다." 는 것이고, 그로 인해 연산을 먼저 시작할 수 있으므로 성능은 더 낳아진다. 라는 이론이다. 하지만 side effect 로 영원히 읽지 않아도 되는 것을 미리 읽어 I/O 성능을 갉아 먹을 수 있는 역 기능을 수행 할 수도 있다.

미리읽기는 IAM 을 이용하는 방법과 index 의 non-leaf 를 이용하는 방법이 있다. 이것의 작동 방식은 BOL에 상세히 나와 있다. 조금만 살펴 본다면, IAM 이 bit 단위로 extents 할당 단위를 표현하고 있고, 이 IAM 1Byte를 읽어 (8 extents = 64K * 8 = 512K) 씩 물리적 방향에 맞게 ASync 방식으로 읽을수 있다. 또 Index 의 경우,  index 의 non-leaf 를 읽어 모여있는 페이지를 몇개를 Async 로 순서에 맞게 읽을 수 있으므로, 그 성능을 좋게 할 수 있다. 핵심은 역시 I/O 와 CPU 연산을 동시에 할 수 있다는 것이 가장 크다. SQL Server EE 의 경우 미리 읽기를 더 잘 할 수 있다.

 


 

2008 R2

http://msdn.microsoft.com/en-us/library/ms191475(v=sql.105).aspx

The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file.

 

 

2000

http://technet.microsoft.com/en-us/library/cc966500.aspx

SQL Server uses the Win32 API calls as follows.

 

 

API

Common Usage

CreateFile

Used to create and open database and log files. The flags FILE_FLAG_OVERLAPPED, FILE_FLAG_WRITETHROUGH, and FILE_FLAG_NO_BUFFERING are specified to avoid nonstable media caching.

WriteFile

Primarily used by the log manager and backup manager to handle I/Os.

ReadFile

Primarily used by the log manager and backup manager to handle I/Os.

WriteFileGather

Primarily used by the buffer pool to write page groups (up to sixteen 8-KB pages in a group).

ReadFileScatter

Primarily used by the buffer pool to read pages into the buffer pool. Can be used for single page requests as well as read-ahead requests. Read-ahead requests are generally 128 pages for each group but can be as many as 1,024 pages when running Microsoft SQL Server Enterprise Edition.

HasOverlappedIoCompleted

Used to determine the status of I/O requests.

GetOverlappedResults

Used to determine success of the I/O requests.

 

 

디버깅 하니....다 나오는구만....-_- 궁금해 할 필요 없이 보면 된다....!

 


미리 읽기의 비 활성화 방법

관련 TRACE FLAG
652 Disables read ahead for the server.
653 Disables read ahead for the current connection.
3222 Disables the read ahead that is used by the recovery operation during roll forward operations.

BOL
http://msdn.microsoft.com/ko-kr/library/ms191475.aspx

좋은 아티클 테스트 포함

http://sqlblog.com/blogs/linchi_shea/archive/2008/07/04/performance-impact-some-data-points-on-read-ahead.aspx
http://technet.microsoft.com/en-us/library/cc966500.aspx
http://technet.microsoft.com/en-us/library/cc917726.aspx

위 아티클을 읽으면, 읽기의 물리적인 단위는 extents 이고 논리적인 읽기의 단위는 page 이다. 쓰기의 물리적인 단위는 page 이다. 그리고, 823,824 등등 오류의 정의도 볼 수 있다.

Object ID

This is the ID of the object to which the page is assigned within the schema of the database. A page can be assigned to only a single object. When the page is read from disk, the object ID is checked on the page. If the object ID does not match the expected object ID, SQL Server will generate Error 605.

SQL Server often performs its writes on page-sized, 8-KB, or larger boundaries.

Extents

SQL Server generally (except for nonmixed extents) allocates space an extent at a time. An extent is eight 8-KB pages, or 64 KB. SQL Server often performs reads in extent-sized (64 KB or 128 KB) boundaries as well.

Read-Ahead

SQL Server 2000 uses ReadFileScatter to perform read-ahead operations. SQL Server uses sophisticated algorithms to retrieve data pages that are going to be used in the immediate future.

For example, if you run a query that can use an index to determine applicable rows, a read-ahead may occur on the actual data pages that are needed to complete the select list. As index entries are identified, SQL Server can post OVERLAPPED (async) I/O operations for the data pages that will be used in upcoming steps of the query plan. This is how a query using a bookmark lookup operator uses read-ahead.

This example is just one of many read-ahead situations that SQL Server can use. Allowing the index searching to continue while the data page I/O is in progress maximizes the CPU and I/O of the system. The I/O is often completed by the time it is needed so other steps in the plan have direct memory access to the needed data and do not have to stall while waiting on I/O.

When a read-ahead is posted, it can be from 1 to 1,024 pages. SQL Server limits a single read-ahead request depth to 128 pages on most editions. However, Microsoft SQL Server Enterprise Edition raises the limit to 1,024 pages.

SQL Server uses the following steps to set up read-ahead.

  1. Obtain the requested amount of buffers from the free list.

  2. For each page:

    1. Determine the in-memory status of the page by doing a hash search.

    2. If found to be already in memory, set up the read-ahead request to immediately return the buffer to the free list upon I/O completion.

    3. Establish the proper I/O request information for ReadFileScatter invocation.

    4. Acquire I/O latch to protect buffer from further access.

    5. If the page is not found in hash search then insert it into the hash table.

  3. Issue the ReadFileScatter operation to read the data.

When the I/O operation is complete, each page is sanity checked for a valid page number and torn page errors. In addition, various other data integrity and safety checks are performed. The I/O latch is then released so the page is available for use if it is located on the hash chain. If the page was determined to be already in memory, the page is immediately discarded to the free list.

This process shows the key factors of SQL Server I/O patterns. Read-ahead goes after pages that can already be in memory or not allocated. Because SQL Server maintains the in-memory buffers and hash chains, SQL Server tracks the page’s state. Importantly, read-ahead processing opens the door for overlapping read and write requests at the hardware level.

If a page is already in memory when the read-ahead request is posted, the contiguous read is still needed and is faster than breaking up read requests into multiple physical requests. SQL Server considers the read to be unusable for the page in question, but many of the pages around it may be usable. However, if a write operation is in progress when the read is posted, the subsystem has to determine which image of the read to return. Some implementations return the current version of the page before the write is complete; others make the read wait until the write completes; and yet others return a combination, showing partially new data and partially old data. The key is that SQL Server will discard the read as unusable but the subsystem needs to maintain the proper image for subsequent read operations. The in-progress write, when completed, must be the next read image returned to the server running SQL Server.

Do not confuse read-ahead with parallel query plans. Read-ahead occurs independently of the parallel query plan selection. The parallel plan may drive I/O harder because multiple workers are driving the load, but read-ahead occurs for serial and parallel plans. To ensure that parallel workers do not work on the same data sets, SQL Server implements the parallel page supplier to help segment the data requests.

Reads
When a page or log block is read from disk, the checksum (page audit) value is calculated and compared to the checksum value that was stored on the page or log block. If the values do not match, the data is considered to be damaged and an error message is generated.

SQL Server uses read-ahead logic to avoid query stalls caused by I/O waits. The read-ahead design tries to keep the physical reads and checksum comparisons out of the critical path of the active query, decreasing the performance effects of checksum activity.

    Read-ahead enhanced

In SQL Server 2005, the read-ahead design is enhanced so that it reduces physical data transfer requirements by trimming the leading and trailing pages from the request if the data page(s) are already in the buffer pool.

For more information on SQL Server read-ahead logic, see SQL Server I/O Basics (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx).

For example, a read-ahead request is to be issued for pages 1 through 128 but pages 1 and 128 are already located in the SQL Server buffer pool. The read-ahead request would be for pages 2 through 127 in SQL Server 2005. In comparison, SQL Server 2000 requests pages 1 through 128 and ignores the data that is returned for pages 1 and 128.

신고
Posted by 보미아빠


SQL Server는 기동할 때 마다 모든 트랜잭션을 커밋 또는 롤백 시키기 위해서 복구(recovery) 작업을 수행한다. 이 복구 과정은 보통 수 초에서 수 분정도 소요되지만 한창 수정 작업이 진행되는 도중에 서버가 중단되었다면 복구 작업은 최소한 수정 작업이 진행된 시간 또는 로그 디바이스에 대한 경쟁 때문에 그 이상 걸릴 수 있다.

 SQL Server가 복구할 수 있는 충분한 시간을 주도록 하고 현재와 과거의 에러 로그 파일 및 NT의 에러 로그를 점검하여 어떤 문제가 발생하지는 않았는지 확인해야 한다. 만약 하드웨어 문제나 SQL Server의 버그가 발생하였다면 에러가 기록되어 있을 것이다.

 복구 작업이 CPU와 다스크를 사용하는지 알아보기 위해서 디스크의 작동상황을 알려주는 램프와 sysprocesses 활동 상황을 점검한다. 매우 드문 경우이긴 하지만 SQL Server가 데이터베이스를 정확하게 복구하지 못할 수도 있다.

 복구 과정을 확인하는 그 밖의 방법으로는 각 트랜잭션이 롤포워드(roll forward) 또는 롤백(rollback) 되었을 때 에러 로그에 기록하도록 트레이스 플래그(trace flag) 3412를 설정할 수도 있다.

 만약 데이터베이스가 복구되지 않고 백업한 것도 없다면 다음의 트레이스 플래그를 사용하여 복구 과정을 건너 뛸 수 있다. 단, 이렇게 하면 데이터베이스/데이터의 일관성이 깨질 수 있지만 다른 대안이 없는 경우에는 이 방법을 사용하되 반드시 필요한 객체를 BCP나 다른 툴을 사용하여 즉시 백업 받아야 한다.

3607 : 모든 데이터베이스의 자동 복구 생략
3608 : 마스터 데이터베이스를 제외한 모든 데이터베이스의 자동 복구 생략


그래도 데이터베이스를 사용할 수 없다면 – suspect라고 표시됨 – 다음의 명령을 실행하여 데이터베이스를 비상 모드(emergency mode)로 설정한다(먼저 수정작업을 허용해야 한다). 그 다음에 데이터베이스에 접속하여(SQL Server를 재기동할 필요 없음) 필요한 데이터를 백업 받으면 된다.

UPDATE master..sysdatabases SET status=-32768 WHERE name=’’

모든 방법이 실패하고 어떻게 해야 할 바를 모르겠다면 마이크로소프트 제품 지원 서비스(PSS)에 바로 전화하면 된다. 그들은 이와 같은 문제에 대비해서 1년 365일 24시간 내내 서비스를 제공하고 있으며 데이터 손실에 비하면 소요되는 비용은 무시 할 만 하다.

신고

'밥벌이' 카테고리의 다른 글

SQL Server I/O Basic  (0) 2010.10.11
read-ahead 미리읽기란?  (0) 2010.10.08
시스템이 복구중이라고 하고 시작되지 않을때  (0) 2010.10.07
sp_lock2  (4) 2010.10.06
주의대상 복구 suspect  (0) 2010.10.06
Statistics Strategy  (0) 2010.09.30
Posted by 보미아빠

sp_lock2

밥벌이 / 2010.10.06 16:26

 

 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

 

신고

'밥벌이' 카테고리의 다른 글

read-ahead 미리읽기란?  (0) 2010.10.08
시스템이 복구중이라고 하고 시작되지 않을때  (0) 2010.10.07
sp_lock2  (4) 2010.10.06
주의대상 복구 suspect  (0) 2010.10.06
Statistics Strategy  (0) 2010.09.30
CursorTypes, LockTypes, and CursorLocations  (2) 2010.09.30
Posted by 보미아빠
TAG lock

최근에 달린 댓글

최근에 받은 트랙백

글 보관함