SQL Server IO and Latch 설명

SQL Server IO and Latch 설명

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server의 latch에 관한 설명과 왜 latch가 발생하는지 알아보자.

 

[래치는 무엇인가?]

래치는 목적으로 보면 잠금과 유사하지만 많은 차이점이 있다. 래치는 SQL Server 메모리에서 다른 개체로 부터 페이지 데이터 무결성을 보장하는 객체로 정의 할 수 있다. 이는 페이지를 사용할 때 자원을 고립시켜 제어된 액세스를 보장하는 논리적 구조이다. 잠금과의 차이점은 SQLOS 외부로 노출되지 않는 내부 SQL 서버 메커니즘이다. 래치는 많은 유형이 있지만 대략 버퍼 래치(Buffer Latch), 비버퍼래치(Non Buffer Latch) 2종류로 나눌 수 있다.

 

[버퍼 캐시(Buffer Catch)]

버퍼 래치를 이해하기 위해서는 먼저 버퍼 캐시의 목적을 이해해야 한다. 버퍼 캐시(또는 버퍼풀(Buffer Pool)이라고도 함)는 데이터 페이지를 보유하는데 사용되는 메모리 영역이다. 버퍼 캐시는 많은 여러 버퍼를 포함하고 있으며 버퍼 캐시의 크기는 8192바이트(8킬로바이트) 이다. 이 영역은 디스크에서 읽을 수 있는 영역이며 만약 수정이 발생하였을 경우 디스크에 다시 기록 한다.

 

버퍼풀의 데이터 로딩은 FIFO를 기초로 하며 캐시에서 오래된 데이터 또는 CHECKPOINT, DBCC DROPCLEANBUFFERS로 비울 수 있다. (캐시를 비우기 전에 버퍼에 있는 모든 더티 페이지를 기록한다). 버퍼 캐시의 각 버퍼는 디스크에서 읽은 데이터 페이지의 복사본을 포함 할 수 있다.

 

버퍼 관리자는 해시 테이블(특정 메모리 위치에 각 버퍼에 대한 포인터의 버킷 컬렉션 포함), 버퍼 배열(버퍼 포함) 그리고 버퍼에 대한 쓰기 등 버퍼캐시의 구성요소를 관리할 책임이 있다. 이것은 메모리 영역에서 데이터를 저장하고 검색하기 위한 메커니즘으로 데이터베이스 엔진과 메모리 페이지의 인터페이스 이다.

 

버퍼래치는 버퍼캐시의 페이지에 접근할 때 발생 한다. 이들은 잠금과 달리 트랜잭션이 지속되는 동안만 수행하고 더 이상 필요하지 않을 때 해제된다. 하나의 트랜잭션에 많은 래치가 있으며 래치는 다양한 이유로 발생한다.

 

버퍼래치중 가장 많이 알려진 종류 중 하나는 PAGE_IO_LATCH wait 이며 이것은 엔진이 I/O 동기화 작업을 기다리고 있는 것으로 완료 되기 전까지 래치에 반영된다. 이것은 I/O 경합을 나타내는 것으로 DMV sys.dm_os_wait_stats로 모든 버퍼 래치를 볼 수 있다. 대기 유형은 sys.dm_exec_requests에서 last_wait_type 컬럼에서 볼 수 있다.

 

select * from sys.dm_os_wait_stats

 

 

select * from sys.dm_exec_requests

 

 

뷰의 캐시에 있는 버퍼에 대한 정보는 DMV sys.dm_os_buffer_descriptors에서 확인 할 수 있다. 이는 버퍼캐시에 있는 각 데이터 페이지에 대해 하나의 행을 포함한다. Page_ID, Page_Type, Row_Count, free space 등을 보여주며 더티페이지 관해서는 나타내지 않는다.

 

select * from sys.dm_os_buffer_descriptors

 

 

 

[래치 클래스(Latch Classes) 및 래치 모드(Latch Mode)]

버퍼 클래스는 기능에 따라 버퍼 래치와 비버퍼 래치의 여러 종류로 분류 할 수 있다. 다음 스크립트는 이들의 매우 포괄적인 목록을 확인할 수 있다.

SELECT latch_class FROM sys.dm_os_latch_stats

 

 

버퍼 클래스는 일반적으로 사용된다. DMV에서 waiting_requests_count 열을 쿼리하는 경우 대부분의 클래스는 순간 유휴 상태(0)이다. 다음은 래치 모드에 대해서 몇 가지 알아 본다.

• NULL latch (NL) : 사용하지 않음
• Keep latch (KP) : 래치가 위치하는 동안 버퍼캐시에서 페이지를 유지하기 위한 목적과 참조 카운트를 유지하기 위한 목적이 있다.
• SHARED latch (SH) ; 데이터 페이지를 읽은 요청이 있을 때
• UPDATE latch (UP) : EX 래치보다는 가벼움. 업데이트 동안 페이지에 읽기는 허용하나 쓰기 안됨.
• EXCLUSIVE latch (EX) : 기록되는 동안 페이지에 대한 액세스 허용하지 않음.
• DESTORY latch (DT) : 버퍼를 파괴하고 캐시를 축출하는데 사용(?)

 

 

[Latch Waits]

래치대기는 성능모니터에서도 확인 할 수 있다. 래치를 피킹하는 것은 여러 가지 이유로 발생 할 수 있다. 예를 들어 작은 범위의 페이지에 업데이트가 많을 때 발생 한다. 다음 스크립트를 통하여 간단히 확인하여 보자.

 

(원문의 테스트 환경은 2012 RTM, Dual Core 2.4GHz, 6GB RAM, Windows 7 pro)

SET NOCOUNT ON   CREATE TABLE dbo.BigWrites ( DateTimeNow DATETIME ) INSERT INTO dbo.BigWrites (DateTimeNow) SELECT GETDATE() WHILE 1=1 BEGIN UPDATE dbo.BigWrites SET DateTimeNow = GETDATE() END

 

 

동일한 페이지에 액세스하여 업데이트하려고 하는 동안 래치 대기를 확인 할 수 있다.

 

[래치 경합 다루기 – SuperLatch를 사용한 병렬처리]

SQL Server는 Superlatches(또는 sublatches라고도 함)라는 버퍼래치의 경합을 처리 할 수 있는 메커니즘을 가지고 있다. 페이지의 병렬 처리를 가능하기 위해 다른 CPU 스케줄러(NUMA 아키텍처)에서 액세스 할 수 있다.

(자세한 내용은 Professional SQL Server 2008 Internals 참고하세요 ^^)

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3088/explanation-of-sql-server-io-and-latches/

 

 

2013-10-31 / 강성욱 / http://sqlmvp.kr