UPDATE를 통한 SQL서버 엔진 내부 들여다 보기

목적 : SQL서버의 메모리 아키텍처와 업데이트시 변경되는 메모리 현황에 대해 이론과 실습을 통하여 분석해보자 

 

 
Update의 Where절(SEEK)이 수행되는 단계에서 업데이트 잠금이 걸리며, Filter된 결과에 대해 실제로 Update를 시도할 때

 업데이트 잠금은 배타적(X) 잠금으로 전환.

또한 변경 시 Intent LOCK 이 해당 키 값 상위의 Page와 그 Page가 위치한 Table에 걸림. 
(다른 LOCK과의 충돌 방지를 위하여 현재 업데이트 하고 있는 해당 페이지와 해당 테이블을 알리는 SQL 내부 메커니즘)
수정 되는 행들을 메모리(버퍼 Cache)로 올라간 후 수정은 버퍼캐시에서 수행
(NOLOCK 옵션 시 버퍼 Cache에서 읽음)

프로시저 캐시에 실행계획저장

로그 또한 LOG Cache에 저장됩니다.

 

 

트랜잭션이 Commit되면 로그캐시에 저장된 로그 페이지가 syslogs에 저장

일정한 시간 후  Checkpoint가 발생하면 수정된 버퍼캐시가와  디스크동기화 실행

ROLLBACK의 경우에는 로그캐시에 저장된 변경 전 데이터를 이용하여 원상복구

 

실습

 

Table 구조

SP_HELP [dbo.test]

 

인덱스 구조

SP_HELPINDEX [dbo.test]

업데이트 할 행 내역

SELECT * FROM dbo.test

where SEQ=65490

TEST할 DB의 ID 쿼리 

SELECT DB_ID('TESTSQL')  9번

Update 테스트를 위하여 하기의 쿼리를 실행 

checkpoint                -- 더티페이지 디스크와 동기화

dbcc FREEPROCCACHE()    -- 프로시저캐시 삭제

dbcc dropcleanbuffers()     -- 버퍼캐시  삭제

dbcc traceON(3604)  –-DBCC 결과 화면에 출력    

dbcc traceON(3505)  --자동체크포인트 중지

dbcc traceON(661)   --고스트 레코드 제거 프로세서 중지

업데이트 실행

Begin tran

update dbo.test

set DUMY1='M'

WHERE SEQ =65490

SP_LOCK 실행

업데이트 락이 걸린 후 X락이 걸려야 하지만 넌클러스터드 인덱스가 걸린 SEQ 로

포인트 쿼리를 하다 보니 순식간에 X락 걸림  현재 Page 57359의 95번쨰 Slot에

X락이 걸리며 상위 개체에 대하여 INTENT X락이 걸린 상황

버퍼캐시의 해당 페이지 상세 내역 조회 

DBCC PAGE(9,1,57359,3)

 (x락 걸린 페이지와 슬롯번호로 조회결과 dumy1이 M으로 변경되어있음)

 

 

 

 

 

프로시저 캐시 조회

SELECT cache_plan.objtype, cache_plan.size_in_bytes,

        cache_plan.cacheobjtype,cache_plan.usecounts,

        sql_text.text

FROM sys.dm_exec_cached_plans as cache_plan 

outer apply sys.dm_exec_sql_text (cache_plan.plan_handle) as sql_text 

UPDATE문의 실행계획이 재 컴파일 방지를 위하여 프로시저 캐시로 올라옴

  

 

DVM 를 통한 버퍼캐시 내역 조회

Select * from sys.dm_os_buffer_descriptors

Where database_id =9

And  is_modified = 1   디스크에서 읽은 후 페이지가 수정된 것 

57359페이지가 현재 변경된 것으로 확인 (is_nodied = 1)

로그 캐시 내역 확인

dbcc log (9, 3)

 

 

 

 

sys.dm_os_buffer_descriptors View에서 조회한 AllocunitID와 동일 한 내역의 Page ID 헥사값 10진수로 변환 시 page 정확히 일치 

ROwLogContents0 컬럼  변경 이전값(FB)

ROwLogContents1  변경된 값(M)

COMMIT 실행

Commit 후 LOG캐시 현황(Commit 내역 등록됨)

 

 

 

Checkpoint 실행

CheckkPoint 후 Log캐시

CheckPoint후 버퍼캐쉬 DMV에 변경된 값 없음(디스크과 동기화됨)