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

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

Delete 작업과 페이지 offset 변화

 

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

 

SQL Server에서 테이블의 레코드를 삭제하는 경우 어떻게 처리되는지 알아본다.

 

실습을 위해 4개의 레코드가 8K의 페이지에 있는 테이블을 만든다.

-- Create a simple table where 4 records fit onto 1 page

CREATE TABLE TestTable

(

    Col1 INT IDENTITY(1, 1),

    Col2 CHAR(2000)

)

GO

 

데이터가 한 페이지에 들어가도록 입력한다.

-- Insert 4 records

INSERT INTO TestTable VALUES

(

    REPLICATE('1', 2000)

),

(

    REPLICATE('2', 2000)

),

(

    REPLICATE('3', 2000)

),

(

    REPLICATE('4', 2000)

)

GO

 

 

heap 테이블의 내용을 보기 위해서 DBCC PAGE 명령을 사용하여 할당된 페이지의 정보를 확인한다. DBCC PAGE의 내용을 확인하기 위해서는 먼저 추적 플래그 3604를 활성화 해야 한다.

-- Enable the Trace Flag 3604

DBCC TRACEON(3604)

GO

 

페이지 확인을 위해 특정 테이블의 인덱스에 할당된 페이지를 반환하는 DBCC IND 명령을 사용한다.

-- Retrieve all pages of the table

DBCC IND(SQLMVP, TestTable, -1)

GO

 

 

DBCC IND를 사용하여 페이지 ID 169를 확인하였다. (사용자마다 다름). 그리고 DBCC PAGE 명령을 사용하여 168페이지의 내용을 확인 한다. 다음과 같이 오프셋을 확인 할 수 있다.

-- Dump out one specific page

DBCC PAGE (SQLMVP, 1, 168, 2)

GO

 

 

행 오프셋 배열은 모든 레코드가 저장되는 물리적 위치를 나타내며 첫 번째 기록은 항상 페이지 헤더 (오프셋 0 (96바이트))다음에 기록된다. 행 오프셋 배열 방향은 거꾸로 올라간다.

 

행 오프셋의 2번째 배열 데이터를 삭제하여보자. 오프셋의 2번째 배열 값이 다음과 같이 0으로 변경되었다.

-- Delete a record from the table

DELETE FROM TestTable

WHERE Col1 = 2

GO

 

-- Dump out one specific page

DBCC PAGE (SQLMVP, 1, 168, 2)

GO

 

 

 

전체 데이터를 삭제하면 오프셋의 값은 모두 0으로 변경된다. 오프셋의 값 0은 삭제된 것을 의미하지만 실제 모든 레코드는 물리적으로 존재한다.

-- Delete all the remaining records from the table

DELETE FROM TestTable

GO

 

-- Dump out one specific page

DBCC PAGE (SQLMVP, 1, 168, 2)

GO

 

 

실제로 데이터가 삭제되지는 않았으며 삭제된 것처럼 표시한다. 다른 데이터로 덮어쓰기 이전까지는 그대로 값이 유지된다. 아래 그림을 보면 데이터를 삭제 하였지만 덮어쓰기 이전에 데이터가 그대로 존재하는 것을 확인 할 수 있다.

 

 

[참고자료]

 



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

No. Subject Author Date Views
1943 성능분석 9탄 – 쿼리 실행 분석 jevida(강성욱) 2016.10.15 4136
1942 성능분석 8탄 – IO 통계 (DISK 활동 분석) jevida(강성욱) 2016.10.15 1485
1941 성능분석 7탄 – 프로파일러 대기 유형 및 PREEMPTIVE_OS_WRITEFILEGATHER jevida(강성욱) 2016.10.15 1656
1940 성능분석 6탄 – CPU 경합 및 동시성 관련 대기 유형 jevida(강성욱) 2016.10.15 1920
1939 성능분석 5탄 – 메모리 및 네트워크 관련 대기 유형 jevida(강성욱) 2016.10.15 2054
1938 성능분석 4탄 – 디스크 및 IO 관련 대기 유형 jevida(강성욱) 2016.10.15 2181
1937 성능분석 3탄 – 집계 대기 통계 jevida(강성욱) 2016.10.15 1952
1936 성능분석 2탄 – 실행 요청을 기다리는 작업 확인 및 분석 (병렬 처리 대기 확인) jevida(강성욱) 2016.10.15 1395
1935 성능분석 1탄 – 실행 요청을 기다리는 작업 확인 및 분석 jevida(강성욱) 2016.10.15 2036
1934 확장이벤트를 사용하여 데드락 정보 확인 jevida(강성욱) 2016.10.15 1582
1933 확장 이벤트를 사용한 CPU 고부하 쿼리 추적 [1] jevida(강성욱) 2016.10.15 2159
1932 데이터에 대한 이해와 spill in tempdb jevida(강성욱) 2016.10.13 1780
1931 로그 파일이 많으면 왜 안 좋은가 jevida(강성욱) 2016.10.13 2084
1930 트랜잭션 백업 실패와 전체 백업 성공 그리고 대처 방안 jevida(강성욱) 2016.10.13 1568
1929 Fast recovery 와 로그 잠금 jevida(강성욱) 2016.10.13 2037
1928 고스트 클린업 jevida(강성욱) 2016.10.13 2283
1927 페이지 분할이 발생 하였을 때 롤백을 하면 어떻게 될까? jevida(강성욱) 2016.10.13 1670
1926 DBCC WRITEPAGE - DBCC 명령을 사용한 데이터 파괴하기 jevida(강성욱) 2016.10.13 1656
1925 SQL Server Backup Error 3023 jevida(강성욱) 2016.10.13 2339
» Delete 작업과 페이지 offset 변화 jevida(강성욱) 2016.10.13 1495





XE Login