데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

고스트 클린업

jevida(강성욱) 2016.10.13 06:29 Views : 2246

고스트 클린업

 

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

 

인덱스가 있는 행을 삭제 할 경우 인덱스의 효율성을 높이기 위해 인덱스의 리프-레벨에 삭제 하려는 행에 대해서는 우선 고스트 레코드(Ghost Record)로 마크한 다음 주기적인 삭제 작업이 실행 된다.

 

고스트 클린업 작업은 빈 데이터 할당 또는 인덱스 페이지 작업을 피하기 위해 기록(single record)을 남길 수 있다. 이러한 작업들은 백그라운드에서 실행된다.

 

고스트 클린업 작업은 삭제 트랜잭션이 완료 될 때까지 물리적으로 삭제 할 수 없다. 왜냐하면 트랜잭션 잠금이 트랜잭션 커밋이 될 때까지 잠금이 해제 되지 않기 때문이다. 고스트 레코드는 삭제된 것으로 표시되기 때문에 NOLOCK 또는 READUNCOMMITTED 에서는 원하는 값이 반환되지 않는다.

 

데이터 삭제가 실행되면 페이지 헤더에 기록하고 PFS 페이지에서 삭제할 레코드로 표시한다. 그리고 기록 정리가 필요하다는 것을 나타내기 위해 데이터베이스 상태를 변경한다. 고스트 정리 작업은 백그라운드에서 5초마다 실행된다. 고스트 정리 작업이 시작되면 페이지에 정리할 고스트 레코드가 있는지 확인하고 PFS 할당 맵 페이지를 통해 다음 데이터베이스를 선택 한다.

 

다음 스크립트는 DMV를 사용하여 고스트 정리 작업을 확인하는 예제이다.

SELECT * INTO myexecrequests FROM sys.dm_exec_requests WHERE 1 = 0;

GO

 

SET NOCOUNT ON;

GO

 

DECLARE @a INT

 

SELECT @a = 0;

 

WHILE (@a < 1)

BEGIN

    INSERT INTO myexecrequests SELECT * FROM sys.dm_exec_requests WHERE command LIKE '%ghost%'

    SELECT @a = COUNT (*) FROM myexecrequests

END

GO

 

SELECT * FROM myexecrequests;

GO

 

 

 

SQL Server 2000에서는 다음 스크립트를 실행 한다.

SELECT * INTO mysysprocesses FROM master.dbo.sysprocesses WHERE 1 = 0;

GO

 

SET NOCOUNT ON;

GO

 

DECLARE @a INT

 

SELECT @a = 0;

 

WHILE (@a < 1)

BEGIN

    INSERT INTO mysysprocesses SELECT * FROM master.dbo.sysprocesses WHERE cmd LIKE '%ghost%'

    SELECT @a = COUNT (*) FROM mysysprocesses

END

GO

 

SELECT * FROM mysysprocesses;

GO

 

 

페이지 정보를 통하여 레코드가 고스트 상태인 것은 확인해 보자.

CREATE TABLE t1 (c1 CHAR(10))

CREATE CLUSTERED INDEX t1c1 on t1 (c1)

GO

 

BEGIN TRAN

    INSERT INTO t1 VALUES ('PAUL')

    INSERT INTO t1 VALUES ('KIMBERLY')

    DELETE FROM t1 WHERE c1='KIMBERLY';

GO

 

DBCC IND ('SW_TEST', 't1', 1);

GO

 

DBCC TRACEON (3604);

GO

 

DBCC PAGE ('SW_TEST', 1, 118, 3);

GO

 

 

 

고스트 레코드가 기록되는 과정에서 트랜잭션은 어떤 변화가 발생하는지 알아 보자. (이 명령은 문서화 되지 않은 기능이다.)

 

결과를 보면 삽입 작업과 삭제 작업이 기록되어 있으며 고스트 레코드로 마크되어 있다. 그러나 PFS 페이지는 업데이트 되어 있을까? 고스트 비트 변경은 PFS 페이지의 트랜잭션 의 한 부분으로 되지 않는다. (이 부분은 덤프에서 수동으로 트랜잭션 로그를 확인해야 한다.)

SELECT Description, * FROM fn_dblog (null, null) WHERE Context like '%PFS%' AND AllocUnitName like '%t1%';

 

 

 

첫 번째 페이지는 그냥 할당하지만 두 번째 페이지는 우리가 찾고 잇는 하나이다. 이전의 모든 트랜잭션을 커밋하고 어떻게 되는지 확인한다.

SELECT MAX ([Current LSN]) FROM fn_dblog (null, null);

GO

 

-- 00000021:000001c0:000a

COMMIT TRAN

GO

 

SELECT [Page ID], * FROM fn_dblog (null, null) WHERE [Current LSN] > '00000021:000001c0:000a';

GO

 

 

 

트랜잭션이 커밋되자 몇 초이내로 고스트 정리 작업에 들어간 것을 확인 할 수 있다. 페이지 덤프를 확인하여 어떻게 기록되었는지 살펴보자.

DBCC PAGE ('SW_TEST', 1, 118, 3);

 

 

DBCC PAGE ('SW_TEST', 1, 118, 2);

 

 

기록이 더 이상 존재하지 않더라도 공간이 재사용 될 때까지 기록이 페이지에 남아 있다.

 

이처럼 데이터를 삭제하여도 물리적으로 즉시 삭제되는 것은 아니며 삭제 된 것으로 표시하였다가 백그라운드에서 주기적으로 삭제 작업을 진행 한다. 또한 데이터 페이지에는 실제 데이터가 남아 있으므로 만약의 불미스러운 상황에서도 같은 공간이 재사용 되지 않았으면 데이터 복구도 가능하다는 것을 알 수 있다.

 

데이터 복구에 관해서는 다음 링크를 참고 한다.

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/

 




강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1930 트랜잭션 백업 실패와 전체 백업 성공 그리고 대처 방안 jevida(강성욱) 2016.10.13 1540
1929 Fast recovery 와 로그 잠금 jevida(강성욱) 2016.10.13 2021
» 고스트 클린업 jevida(강성욱) 2016.10.13 2246
1927 페이지 분할이 발생 하였을 때 롤백을 하면 어떻게 될까? jevida(강성욱) 2016.10.13 1640
1926 DBCC WRITEPAGE - DBCC 명령을 사용한 데이터 파괴하기 jevida(강성욱) 2016.10.13 1641
1925 SQL Server Backup Error 3023 jevida(강성욱) 2016.10.13 2248
1924 Delete 작업과 페이지 offset 변화 jevida(강성욱) 2016.10.13 1477
1923 트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구 jevida(강성욱) 2016.10.13 5052
1922 PFX 형식의 인증서를 SQL Server에서 사용하기 jevida(강성욱) 2016.10.13 1149
1921 SQL Server NUMA 메모리 노드와 Operating System 접근 jevida(강성욱) 2016.10.13 1290
1920 SQL Server 에러 핸들링 비용 비교 jevida(강성욱) 2016.10.13 1302
1919 압축 백업 시 Checksum 옵션으로 손상 확인하기 jevida(강성욱) 2016.10.13 1633
1918 SQL Server 2012 Memory Manager 구성 jevida(강성욱) 2016.10.13 1263
1917 SQL Server Memory Manager 변화 jevida(강성욱) 2016.10.13 1480
1916 Ring_Buffer_Resource_Monitor jevida(강성욱) 2016.10.13 994
1915 SQL Server 프로파일러 템플릿 만들기 jevida(강성욱) 2016.10.13 1335
1914 SQL Server 메모리 병목 현상 식별 jevida(강성욱) 2016.10.13 1770
1913 SQL Server 시작 옵션 사용 jevida(강성욱) 2016.10.13 1787
1912 Ring buffer를 활용한 External Memory Pressure 확인 jevida(강성욱) 2016.10.12 1272
1911 Memory Pressure jevida(강성욱) 2016.10.12 1122





XE Login