고스트 클린업
- Version : SQL Server 2005, 2008, 2008R2, 2012
인덱스가 있는 행을 삭제 할 경우 인덱스의 효율성을 높이기 위해 인덱스의 리프-레벨에 삭제 하려는 행에 대해서는 우선 고스트 레코드(Ghost Record)로 마크한 다음 주기적인 삭제 작업이 실행 된다.
- GhostRecord(인덱스에서 행 삭제시발생) : http://sqlmvp.kr/140164866348
고스트 클린업 작업은 빈 데이터 할당 또는 인덱스 페이지 작업을 피하기 위해 기록(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); |
기록이 더 이상 존재하지 않더라도 공간이 재사용 될 때까지 기록이 페이지에 남아 있다.
이처럼 데이터를 삭제하여도 물리적으로 즉시 삭제되는 것은 아니며 삭제 된 것으로 표시하였다가 백그라운드에서 주기적으로 삭제 작업을 진행 한다. 또한 데이터 페이지에는 실제 데이터가 남아 있으므로 만약의 불미스러운 상황에서도 같은 공간이 재사용 되지 않았으면 데이터 복구도 가능하다는 것을 알 수 있다.
데이터 복구에 관해서는 다음 링크를 참고 한다.
- Delete 작업과 Page offset 변화 : http://sqlmvp.kr/140206569293
[참고자료]
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
