Ghost Record(인덱스에서 행 삭제시 발생)

Ghost Record(인덱스에서 행 삭제시 발생)

 

인덱스가 있는 행을 삭제 할 경우 인덱스의 효율성을 높이기 위해서 인덱스의 리프-레벨에 삭제할려는 행에 대해서는 우선 고스트 레코드(Ghost Record )로 마크해 놓고 주기적인 삭제 작업이 실행 됩니다. 즉 삭제 행에 대해서 즉시 삭제가 이루어지지 않습니다.

 

인덱스 리프-레벨 페이지의 레코드가 삭제될 때 고스트 레코드로 변경되고 레코드가 포함된 페이지게 고스트 레코드 개수가 설정 됩니다. 이 고스트 레코드를 삭제하는 프로세스를 Ghost CleanUp또는 House Keeping 이라고 합니다.

 

[Ghost Record 살펴 보기]

• 전체 코드

BEGIN TRY     DROP TABLE TBL_X END TRY BEGIN CATCH END CATCH GO   create table tbl_x (num int identity, name NVARCHAR(50)) go   INSERT INTO TBL_X VALUES ('HTTP://SQLMVP.KR') GO 1000   SELECT * FROM TBL_X GO   CREATE CLUSTERED INDEX IX_TBL_X_NUM ON TBL_X (NUM) GO   SP_HELPINDEX TBL_X   DBCC IND (SW_TEST, TBL_X, 1)   DBCC CHECKTABLE(TBL_X) DBCC PAGE(SW_TEST, 1, 1344, 1) WITH TABLERESULTS   DBCC TRACEON(3604) DBCC TRACEON(2514)   BEGIN TRAN     DELETE TBL_X WHERE NUM = 1     --DBCC CHECKTABLE(TBL_X)     --DBCC PAGE(SW_TEST, 1, 1344, 1) WITH TABLERESULTS COMMIT TRAN   DBCC CHECKTABLE(TBL_X) DBCC PAGE(SW_TEST, 1, 2561, 1) WITH TABLERESULTS

 

데이터를 입력하고 클러스터 인덱스를 생성합니다. 그리고 DBCC IND 명령어를 이용하여 페이지 정보를 확인 합니다.

 

행을 삭제 하기 전 DBCC CHECKTABLE로 확인하여 보면 Ghost Record Count = 0인 것을 확인 할 수 있습니다.

 

DBCC PAGE를 이용하여 데이터 페이지를 확인하여 보면 정상적으로 행이 존재하고 데이터가 있는 것을 확인 할 수 있습니다.

 

 

행을 삭제하고 CHECK TABLE과 DBCC PAGE 명령문을 이용하여 Ghost Record와 데이터 페이지를 확인 합니다.

Ghost Record Count = 1로 바뀐 것을 확인 할 수 있습니다.

(일정 시간 지나면 고스트 레코드가 삭제되어 확인 되지 않습니다.)

 

 

이 때의 데이터 페이지를 확인 해 봅니다. 아래 그림처럼 m_GhostRecCnt의 값이 1로 나타나며 Record Type에 GHOST_DATA_RECORD라고 표시된 것을 확인 할 수 있습니다.

 

[SQL 2005 이상 Ghost Record 확인]

SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), OBJECT_ID('PERSON.ADDRESS'), NULL, NULL, 'DETAILED')

 

 

[Ghost Record로 인한 문제]

대량 삭제 작업시 While문 등으로 삭제하다보면 Ghost record 삭제시 Ghost CleanUp 프로세스에 의하여 블록킹이 발생 합니다.

 

[해결 방법]

추척 플래그 (661, -1) 이용하여 고스트 레코드 제거 프로세스를 사용 안 함으로 설정 합니다. 이 플래그를 사용하면 고스트 레코드로가 제거 되지 않습니다. 또한 고스트 레코드가 삭제되지 않음으로써 공간이 해제 되지 않습니다. 이 작업은 인덱스 공간 및 검색에 영향이 있음으로 반드시 숙지하신 후 사용하길 바랍니다.

 

참고자료 : http://support.microsoft.com/kb/920093

참고자료 : http://support.microsoft.com/kb/2622823/ko

 

2012-08-03 / 강성욱 / http://SQLMVP.KR