보미네

미리읽기란? 

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.

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

미리 읽기의 비 활성화 방법

관련 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.

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시간 내내 서비스를 제공하고 있으며 데이터 손실에 비하면 소요되는 비용은 무시 할 만 하다.

 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

Identifying statistics-related recompilations

Statistics-related recompilations can be identified by the "EventSubClass" column of the profiler trace (to be described later in this paper) containing the string "Statistics changed".

Closing remarks

An issue not directly related to the topic of this document is: given multiple statistics on the same set of columns in the same order, how does the query optimizer decide which ones to load during query optimization? The answer is not simple, but the query optimizer uses such guidelines as: Give preference to recent statistics over older statistics; Give preference to statistics computed using FULLSCAN option to those computed using sampling; and so on.

There is a potential of confusion regarding the "cause and effect" relationship between plan optimality-related compilations, recompilations, and statistics creation/updates. Recall that statistics can be created or updated manually or automatically. Only compilations and recompilations cause automatic creation or updates of statistics. On the other hand, when a statistic is created or updated (manually or automatically), there is an increased chance of recompilation of a query plan which might find that statistic "interesting."

Best practices

Four best practices for reducing plan optimality-related batch recompilations are given next:

Best Practice: Because a change in cardinality of a table variable does not cause recompilations, consider using a table variable instead of a temporary table. However, because the query optimizer does not keep track of a table variable's cardinality and because statistics are not created or maintained on table variables, non-optimal query plans might result. One has to experiment whether this is the case, and make an appropriate trade-off.

Best Practice: The KEEP PLAN query hint changes the recompilation thresholds for temporary tables, and makes them identical to those for permanent tables. Therefore, if changes to temporary tables are causing many recompilations, this query hint can be used. The hint can be specified using the following syntax:

SELECT B.col4, sum(A.col1) FROM dbo.PermTable A INNER JOIN #TempTable B ON A.col1 = B.col2 WHERE B.col3 < 100 GROUP BY B.col4 OPTION (KEEP PLAN)

Best Practice: To avoid recompilations due to plan optimality-related
(statistic update-related) reasons totally, KEEPFIXED PLAN query hint can be specified using the syntax:

SELECT c.TerritoryID, count(*) as Number, c.SalesPersonID FROM Sales.Store s INNER JOIN Sales.Customer c ON s.CustomerID = c.CustomerID WHERE s.Name LIKE '%Bike%' AND c.SalesPersonID > 285 GROUP BY c.TerritoryID, c.SalesPersonID ORDER BY Number DESC OPTION (KEEPFIXED PLAN)

With this option in effect, recompilations can only happen because of correctness-related reasons — for example, schema of a table referenced by a statement changes, or a table is marked with sp_recompile procedure.

In SQL Server 2005, there is a slight change in behavior as described below. Suppose that a query with OPTION(KEEPFIXED PLAN) hint is being compiled for the first time, and compilation causes auto-creation of a statistic. If SQL Server 2005 can get a special "stats lock," a recompilation happens and the statistic is auto-created. If the "stats lock" cannot be obtained, there is no recompilation, and the query is compiled without that statistic. In SQL Server 2000, a query with OPTION(KEEPFIXED PLAN) is never recompiled because of statistics-related reasons, and therefore, in this scenario, no attempt is made to get a "stats lock" or to auto-create the statistic.

Best Practice: Turning off automatic updates of statistics for indexes and statistics defined on a table or indexed view will ensure that plan optimality-related recompilations caused by those objects will stop. Note, however, that turning off the "auto-stats" feature using this method is usually not a good idea because the query optimizer is no longer sensitive to data changes in those objects, and sub-optimal query plans might result. Adopt this method only as a last resort after exhausting all of the other alternatives.

TEST 프로그램은 첨부파일로 담았으니 혹시 관심있는 분은 Test 해보시길 바랍니다.
VS2010 c# Console application 입니다.

CursorPerfTest.zip

마지막으로 당부 드리고 싶은말은 SQL Server 어플리케이션 성능이 좋지않아 원인을 분석해보니 ServerSide 커서를 사용하고 그 CacheSize 혹은 FetchSize 가 1로 되어 있다면, 이 CacheSize 만 늘이시길 바랍니다. 아니면, 어플리케이션 의도상 반드시 ServerSide 커서가 필요한 어플리케이션이 있을 수 있으니 주의해서 설정(튜닝) 하시길 바랍니다. 그냥 클라이언트 커서로 바꾸시구요, 에러나는건 잡으면서 반나절만 고생하시면 되요. 이럼 아니되옵니다.

원본글 링크 http://dev.mysql.com/tech-resources/articles/vb-cursors-and-locks.html

One of the most common sources of trouble for the beginning VB / MySQL developer seems to revolve around which cursor location to use. Related to the cursor location problem is the choice of cursor type and lock type. The purpose of this article is to assist the Visual Basic developer in choosing the proper cursor location, cursor type, and lock type to use when programming Visual Basic (or VB) applications that use MySQL as a back-end database through ADO and Connector/ODBC (MyODBC).

This article will assume that the reader is using the MySQL RDBMS, but should apply to developers using other database management systems. For an overview of why MySQL is a good choice for Visual Basic developers, see the Why VB/MySQL article on this site. This sample requires the latest version of Connector/ODBC (MyODBC), available for download here (currently 3.51.06).

On the Visual Basic side, I recommend you have service pack 5 installed for VB, which you can download here. Additionally, the latest version of MDAC (Microsoft Data Access Components) should be installed, and can be found here (currently 2.7 SP1). Finally, this article applies to ADO 2.7 and VB6. It is not applicable to ADO.NET under VB.NET (Unless you reference ADODB in your project and use it for database access).

What Are Cursors?

In ADO, when we talk about cursors, we are essentially talking about a set of rows. When you execute a query that returns rows of data, such as SELECT * FROM mytable, the resulting data is handled using a cursor. A cursor can be located either on the client with the adUseClient argument, or on the server with the adUseServer argument. In addition, the are 4 types of cursor: adOpenForwardOnly, adOpenStatic, adOpenDynamic, and adOpenKeyset.

The different types and locations will be discussed in further detail below. Your choice of cursor type and cursor location will affect what you are able to do with the data you retrieve, and how changes made to the data by other users are reflected in your copy of the data.

Cursor Location

The ADODB.Connection object (the ADO object used to broker all data exchanges between the VB application and the MySQL server) has a property known as CursorLocation which is used to set/retrieve the cursor location that will be used by any recordset objects that access their data through the connection object.

The CursorLocation property can only be set while the connection is closed, and the property will be inherited by any recordset objects that access their data through the given connection object. Recordset objects can also explicitly set a cursorlocation different than the connection objects cursorlocation as long as it is set before the recordset is open. The two options available for this property are adUseClient and adUseServer, with adUseServer being the default property.

adUseServer

When using the adUseServer server-side cursorlocation, responsibility for handling the data generated by a query lies with the database server. MySQL itself does not support server-side cursors, so the data handling is actually done by the Connector / ODBC driver. The benefit of server-side cursors is that we gain access to the dynamic cursor type. This allows us to see any changes to the data that are made by other users in the data our application is accessing.

For example: let's say we are selling tickets to a concert with our application, we need to know that a given seat is available for sale in real-time to ensure we do not double-book the seat. With a server-side cursor, we can be sure that the data we are manipulating is the most current possible. In addition, we have the ability to lock the data we are working on as we edit it, to make sure our changes are going to be posted to the database successfully.

With a server-side cursor (adUseServer), we have access to the adOpenDynamic and adOpenForwardOnly cursor types, and all four of the recordset lock types, which will be discussed below.

It should be noted that using a server-side cursor, and the adOpenDynamic cursor in particular, will result in a significant performance loss, and should be avoided if at all possible. In addition, certain functionality, such as the RecordCount property of a Recordset and the GetChunk and Appendchunk function for handling BLOB data, will fail or return abnormal results when used with a server-side cursor.

adUseClient

Client-side cursors, specified with the adUseClient keyword, are handled internally by ADO. These cursors offer more functionality than their server-side counterparts, and also result in less load being placed on the server. Most advanced ADO functionality is designed for use with client-side cursors, and I personally use client-side cursors for all my applications (with one exception).

When using a client-side adUseClient cursor, only the adOpenStatic cursor is available, and we cannot use the adLockPessimistic lock type (see below).

Client-side cursors also help decrease load on our MySQL server, since with a static cursor data is sent to the client and then the server has no further communications with the client. This allows your server to scale a lot better than with server-side cursors.

Cursor Types

In addition to the two cursor locations, there are four cursor types, three of which are supported under Connector/ODBC:

• adOpenStatic (Client-Side)
• adOpenForwardOnly (Server-Side)
• adOpenDynamic (Server-Side)

The different cursor types support different functionality and features, and I will now discuss each one in detail. The fourth cursor type, adOpenKeySet, is not currently supported by MySQL / MyODBC.

adOpenStatic

The static cursor is the only cursor type that is currently available when using adUseClient as your cursor location. With a static cursor, the server will send the result set to the client, after which there will be no further communication from the server to the client. The client may communicate with the server to send changes back to the server. This makes the static cursor more resource-intensive for the client and less resource-intensive for the server, as the result set is stored in the client's memory instead of the server's.

If a different client makes changes to the underlying data after the query results are sent, the original client will receive no notification of the change. A static cursor is bi-directional, meaning that your application can move forwards and backwards through the recordset. The following methods are available to a recordset using a static cursor and the adLockOptimistic lock type (more on lock types later):

The static cursor will also show an accurate value for the RecordCount property of your recordset, and supports the getchunk and appendchunk methods for dealing with BLOB data. If you are having trouble with either of these problems, explicitly setting your connection's cursorlocation to adUseClient should solve them.

One handy feature of the static cursor is the ability to fetch data asynchronously. When data is fetched asynchronously., a separate thread is started to handle row retrieval, and your VB application can begin processing returned rows immediately. An in depth article on asynchronous data fetching is pending, but to activate this feature, simple use the adFetchAsync option during your recordset.open method call.

If you specify any cursor type other than adOpenStatic when opening a recordset with an adUseClient cursor location, it will be automatically converted to a static cursor.

adOpenForwardOnly

The adForwardOnly cursor type is the fastest performing cursortype, and also the most limited. The forward-only cursor does not support the RecordCount property, and does not support the MovePrevious methods of the recordset object.

The most efficient way to access data for display to the screen out output to a file is to use a adOpenForwardOnly cursor with a adLockReadOnly lock type when opening a recordset. This combination is often referred to as a Firehose Cursor. A firehose cursor bypasses a lot of handling code between the client and server and allows for very fast data access when moving sequentially through the resulting rows.

The following recordset methods are supported when using a forward-only cursor with an optimistic lock:

• AddNew
• Delete
• Find
• Update
• UpdateBatch

In addition, the forward-only cursor type supports non-caching queries. While an asynchronous query allows data to be worked on immediately, it offers no memory benefits when accessing large resultsets, as all rows eventually wind up in memory, taxing system resources when accessing a large number of rows, or a medium number of rows when BLOB data is involved.

With MySQL and Connector/ODBC, we can specify option 1048576 in our connection string or check off the option "Don't Cache Results" in the ODBC manager in order to specify to the ODBC driver that it should only retrieve one row at a time from the server. With this option set, memory usage on the client is limited as only one row at a time is stored in memory. With every call to the recordset's MoveNext method, the previous row is discarded and the next row is queried from the server.

adOpenDynamic

While the forward-only cursor is the most efficient of the cursor types, the dynamic cursor, specified but adOpenDynamic, is the least efficient. Because of it's inefficiency, dynamic cursor support must be manually activated by using option 32 in your connection string, or by checking "Enable Dynamic Cursor" in the ODBC manager. Without this option enabled, any cursortype other than forward-only with be automatically converted to a static cursor, with it enabled, all cursor types other than forward-only will be converted to dynamic.

Why is a dynamic cursor so slow? As there is no native support for dynamic, server-side cursors in MySQL, every call to a row-moving method(MoveNext, MovePrevious, etc.) results in the Connector/ODBC driver converting your method call to a SQL query, posting the query, and returning the resulting row. This also means that for a dynamic cursor to work properly, your underlying table needs a primary key column to determine the next/previous row with. As such, dynamic cursors are not recommended unless absolutely necessary.

The dynamic cursor supports the following recordset methods when opened with a optimistic lock:

• AddNew
• Delete
• Find
• MoveFirst
• MovePrevious
• Update
• UpdateBatch

While Dynamic cursors can be beneficial for multi-user applications, it is best to avoid them when possible, and work around multi-user issues when possible by calling the resync and requery methods when possible, and executing UPDATE queries that increment and decrement count values instead of using the recordset to do updates (i.e. rather than getting an inventory count in a recordset, incrementing it in VB, and doing a recordset.update, use the connection object to execute a query similar to UPDATE inventory SET count = count - 1 WHERE itemcode = 5)

Lock Types

While cursor locations and cursor types specify how our data is going to be handled, the lock type property specifies how we are going to lock the underlying data to protect any changes we make and ensure they are processed. There are four different lock types, and the locktype is set in the recordset object as part of the open method (it can also be set using the LockType property of the recordset object). The four locktypes are: adLockReadOnly (default), adLockOptimistic, adLockPessimistic, and adLockBatchOptimistic. All four locktypes are available to a server-side cursor, the adLockPessimistic locktype is unavailable to a client-side cursor.

adLockReadOnly

The default lock type is adLockReadOnly. A read-only lock is the most efficient when accessing data, as there is no checking for data changes and therefore no extra traffic between the client and server while loading records.

As the name implies, using a read-only lock will block you from making any changes to the table. If you find yourself with an error message like "Current recordset does not support updating", then you need to change away from the default adLockReadOnly lock type.

adLockOptimistic

An optimistic lock is used for modifications that either happen in a low-concurrency environment, or where having multiple users making changes to the same records is not a major concern. With an optimistic lock, the table or row locks will occur when the update method of the recordset object is called. This will ensure the change is successfully made, but will not prevent other users from changing the underlying data while you are modifying it in VB.

The adLockOptimistic lock type is typically your best choice when deciding on a table lock for a non-read-only situation. In almost all my applications, the only two lock types I use are adLockReadOnly and adLockOptimistic.

adLockBatchOptimistic

When using the adBatchOptimistic lock type, your changes will be cached locally until the recordset's UpdateBatch method is called. When UpdateBatch is called, all changes will be pushed to the server in a group. This can make the bulk insert of a large number of records more efficient. (Note: Calling ALTER TABLE mytable DISABLE KEYS before a large batch of inserts, followed by ALTER TABLE mytable ENABLE KEYS after the batch completes, can dramatically speed up the batch insert process, as MySQL can rebuild an index faster than it can add one entry at a time).

adLockPessimistic

In a situation of high concurrency, with multiple users modifying the same data, you may need a pessimistic lock type. With asLockPessimistic, the underlying rows (or table) will be locked as soon as you begin making changes to the current record, and will not be unlocked until the Update method is called.

While this will ensure that you do not have overlapping changed with other users, it could cause performance issues, especially with a MyISAM table, with features table-level locking only. Make sure that the changes are immediately followed by the recordset's update method, and that there is no break for user input between a change and the update in order to ensure no long breaks (and potentially canceled locks by the database) in the operation of the database.

While adLockPessimistic has it's place, the same advice I gave regarding dynamic cursors applies: avoid it when possible, as it is very resource intensive and involves a lot more work both on the client and server side.

Conclusion

While there are a large number of potential CursorType/CursorLocation combinations, the ones that are currently available to the MySQL/VB developer are: adUseClient/adOpenStatic, adUseServer/adOpenForwardOnly, and adUseServer/adOpenDynamic.

For most uses, adUseClient/adOpenStatic is your best choice, with adLockReadOnly as your lock type for any read-only operations (export to a file, load rows to a listview, combobox, etc.) and adLockOptimistic as your lock type for any read/write operations.

adOpenDynamic and adLockPessimistic are best suited for high-concurrency situations where you need to ensure that multiple users do not corrupt each other's data. While these offer the most current views of data and the most restrictive locking, they do so at a severe price as far as performance is concerned.

The combination of adUseServer/adOpenForwardOnly/adLockReadonly offers the best performance overall for operations like populating controls and exporting to files. When combined with option 1048576 (Don't cache query results), adOpenForwardOnly also provides excellent memory efficiency, as only one record at a time is loaded into memory. Be awate that if a locktype other than adLockReadOnly is used, memory usage will slowly build as rows are loaded into memory and kept in case they are needed for an update or cancel statement. The one row at a time operation is only present with an adLockReadOnly/adOpenForwardOnly combination.