안녕하세요 김민석 입니다.

LOB (TEXT)  데이터 삭제 후 SQL Server 2000의 경우 CTAS 외 특별한 방안이 없었던 것이 사실 입니다.

그러나, SQL Server 2005 이상 부터 alter index 구문을 이용해 실시간 축소가 가능 합니다. 
 

간략한  TEST

1. lob_compaction  many times  (하나의 테이블만 적용가능 한번 실행시 마다 unused 공간의 절반씩 반환)

ALTER INDEX CL_TBLX ON TBLX
REORGANIZE
WITH ( LOB_COMPACTION = ON )
GO

-- 75%보다 작은 LOW-DENSITY UNIFORM EXTENTS 를 찾아 빈 공간이 있는 LOB UNIFORM EXTENTS 에 넣는다.

-- HEAPSORT 나 SMOOTHSORT 알고리즘과 같이 단 2개의 PAGE 만 LOCK 보유

-- lob_compaction 은 default 로 on 입니다.

2. 파일내 조각모음 진행  (전체 데이터베이스에 적용)

다음 명령으로 파일 축소를 진행 한다.

DBCC SHRINKFILE (testdb_data, 1, notruncate)
2번 진행 후, 물리적 위치와 논리적 위치의 순서가 맞지 않게 되므로 클러스터 인덱스 리빌드가 필요함  

-- 이하는  varchar(max) 일 경우만

3. 다음 인덱스 물리 fragmentation 을 제거 하기 위해 online rebuild 수행

ALTER INDEX CL_TBLX ON TBLX
rebuild with (online = on ) 

온라인 reindex 는 text 컬럼이 있으면 진행 되지 않는다. 주의 !

본인은 unused 로 남은 공간을 그대로 두어도 나중에 해당 object 에서는 재사용 가능 하므로, 위 방법을 실행 하라고 강력하게 권하고 싶지는 않습니다. 그러나, 서버의 예상 수명과 스토리지 공간 관리 플랜을 만들기 위해서는 필요할 수 있습니다. 보다 예측 가능한 운영을 위해서는 필요 합니다.

감사합니다.

* CTAS

   ORACLE 의 CREATE TABLE AS

   혹은 MSSQL 의 SELECT * INTO NEW_TABLE FROM  를 지칭함  

* LOB_COMPACTION

   SQL SERVER 2005 이상에서 생긴 LOB  영역의 최적화 옵션

* 온라인 중 실행가능

   Page 단위 LOCK 설정으로 실제 운영중인 서버에서 실행 할 수 있음을 의미 allow_page_locks 이 off 이면 불가능

* 물리 FRAGMENTATION

   인덱스는 논리 순서로는 항상 정렬되어 있지만, 디스크상에서 물리 순서는 정렬되어 있지 않을 수 있음

   특히 SHRINKFILE 이나 ,  DEFRAG 등을 수행하면, 이러한 문제가 심각해짐

* sys.dm_exec_requests percent_complete 컬럼을 통해 진행정도 모니터링 가능

* 한번 LOB_COMPATION 할 때 마다 UNUSED 공간이 반으로 준다.

---------------- 

TEST SCRIPT

---------------- 

USE TESTDB
GO

IF OBJECT_ID ('TBLX') IS NOT NULL
DROP TABLE TBLX
GO

CREATE TABLE TBLX
(IDX INT IDENTITY(1,1)
,COLA TEXT
)
GO

SET NOCOUNT ON

DECLARE @IDX INT
SET @IDX = 0

DECLARE @LOB_DATA VARCHAR(MAX) -- CLOB 데이터 넣기 편법
SET @LOB_DATA = REPLICATE('A', 8000)
SET @LOB_DATA = @LOB_DATA + REPLICATE('A', 8000)
SET @LOB_DATA = @LOB_DATA + REPLICATE('A', 8000)
SET @LOB_DATA = @LOB_DATA + REPLICATE('A', 8000)

WHILE (1=1)
BEGIN
 INSERT INTO TBLX (COLA) VALUES(@LOB_DATA)
 SET @IDX = @IDX + 1
 IF @IDX > 1000 BREAK
END
GO

SELECT TOP 1 DATALENGTH(CAST(COLA AS VARCHAR(MAX))) FROM TBLX
GO

CREATE UNIQUE CLUSTERED INDEX CL_TBLX ON TBLX (IDX)
GO

-- 지우기 전 사이즈
DBCC EXTENTINFO ('TESTDB','TBLX')
GO

-- 인터리브하게 지우고 난 후 사이즈
DELETE FROM TBLX WHERE IDX % 2 = 1

-- LOB_COMPACTION 후 사이즈
ALTER INDEX CL_TBLX ON TBLX REORGANIZE
DBCC EXTENTINFO ('TESTDB','TBLX')
ALTER INDEX CL_TBLX ON TBLX REORGANIZE
DBCC EXTENTINFO ('TESTDB','TBLX')
ALTER INDEX CL_TBLX ON TBLX REORGANIZE
DBCC EXTENTINFO ('TESTDB','TBLX')
ALTER INDEX CL_TBLX ON TBLX REORGANIZE
DBCC EXTENTINFO ('TESTDB','TBLX')
ALTER INDEX CL_TBLX ON TBLX REORGANIZE
DBCC EXTENTINFO ('TESTDB','TBLX')
ALTER INDEX CL_TBLX ON TBLX REORGANIZE
DBCC EXTENTINFO ('TESTDB','TBLX')
ALTER INDEX CL_TBLX ON TBLX REORGANIZE
DBCC EXTENTINFO ('TESTDB','TBLX')
ALTER INDEX CL_TBLX ON TBLX REORGANIZE
DBCC EXTENTINFO ('TESTDB','TBLX')
ALTER INDEX CL_TBLX ON TBLX REORGANIZE
DBCC EXTENTINFO ('TESTDB','TBLX')
ALTER INDEX CL_TBLX ON TBLX REORGANIZE
DBCC EXTENTINFO ('TESTDB','TBLX')

http://cafe.naver.com/sqlmvp
sqlserver mvp 2006 ~ 2009
msn minsouk@hotmail.com

Twitter minsouk_sqlmvp
Tel 010-9967-0955