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

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

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


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



No. Subject Author Date Views
1613 extended events 를 이용한 wait 분석 minsouk 2014.07.25 6264
1612 동일기준에대해 복수컬럼 PIVOT 사용하기 열이 2014.06.27 21319
1611 SELECT / UPDATE / INSERT 쿼리를 쉽게 만들자... [1] Hisory 2014.05.12 10870
1610 ssms 자동 언어 변경이 되면, 이렇게 설정하세요 minsouk 2014.04.27 9566
1609 [QUERY] 그룹 번호 만들기 이스트럭(강동운) 2014.04.11 8682
1608 [QUERY]연승을 구해보자! [2] 이스트럭(강동운) 2014.04.11 9466
1607 VLF 이스트럭(강동운) 2014.04.11 8076
1606 extended events 용 excel view 공개 버전 minsouk 2014.04.07 6400
1605 [TIP] 한 라인에서 여러 테이블 삭제 하기. [3] 엘리엘1 2014.01.03 8616
» GhostRecord(인덱스에서 행 삭제시발생) jevida(강성욱) 2013.11.25 13560
1603 프로시저, 함수, 트리거 생성 정보 보기. jevida(강성욱) 2013.11.25 10632
1602 힙테이블에서 행을 삭제하면어떻게 처리 될까? [1] jevida(강성욱) 2013.11.25 9379
1601 SSMS의 디자이너에서 테이블 수정 시 발생하는 영향 [1] jevida(강성욱) 2013.11.25 10526
1600 SQL Server를 활용한 Perfmon 로그 저장 [1] jevida(강성욱) 2013.11.21 14751
1599 VLF 환경과 성능 jevida(강성욱) 2013.11.21 10060
1598 Block 모니터링 jevida(강성욱) 2013.11.21 8073
1597 추적파일을 테이블로 로드하기. jevida(강성욱) 2013.11.21 8895
1596 LOGON 트리거 jevida(강성욱) 2013.11.18 6562
1595 DeadLock(교착상태) 모니터 하기 [2] jevida(강성욱) 2013.11.18 11352
1594 Tempdb 경합(동시성 강화) jevida(강성욱) 2013.11.18 7566





XE Login