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 |
실제로 데이터가 삭제되지는 않았으며 삭제된 것처럼 표시한다. 다른 데이터로 덮어쓰기 이전까지는 그대로 값이 유지된다. 아래 그림을 보면 데이터를 삭제 하였지만 덮어쓰기 이전에 데이터가 그대로 존재하는 것을 확인 할 수 있다.
[참고자료]
- DELETE operations on tables : http://www.sqlpassion.at/archive/2014/02/11/delete-operations-on-tables/
- 힙테이블에서 행을 삭제하면 어떻게 처리 될까? : http://sqlmvp.kr/140164714473
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
