Database Shrink를 하지 말아야 하는 이유

---

---

 

Database Shrink

 

Shrink Database를 가급적 하지 말아야 하는 이유.

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

 

Shrink 데이터베이스

- 데이터베이스를 축소하는 작업

- 데이터 파일(.mdf)를 축소하는 것과 트랜잭션 로그(.ldf)를 축소하는 것은 다르게 생각해야 한다.

- 트랜잭션 로그(.ldf)를 축소하는 것은 로그 파일의 사이즈가 제어하지 못할 정도로 커지거나 과도한 VLF Fragmentation을 제거하가 위한 목적에서는 유용하다.

그렇지만 로그 파일을 축소하는 것은 빈번하게 일어나는 작업이 아니며 시스템 유지보수를 위해 정기적으로 수행해야 하는 작업은 아니다.

- 데이터 파일을 축소하는 것은 트랜잭션 로그를 축소하는 것보다 더 자주 일어나는 일은 아니다.

그리고 데이터 파일을 축소하는 것은 매우 큰 인덱스 단편화(Index Fragmentation)를 발생시킨다.

 

Example)

DB_TEST 라는 데이터베이스를 만들고 10MB 크기의 FilerTable 테이블을 만들어 데이터를 채워넣는다. 그리고 10MB 크기의 ProdTable 에 클러스터 인덱스

production 을 만들고 새로 만든 production 클러스터의 Fragmentation 을 확인해 본다.

 

 

USE [master] ;

GO

 

IF DATABASEPROPERTYEX (N'DB_TEST' , N'Version' ) IS NOT NULL

DROP DATABASE DB_TEST;

GO

 

CREATE DATABASE DB_TEST;

GO

USE DB_TEST ;

GO

 

SET NOCOUNT ON;

GO

 

-- Create the 10MB filler table at the 'front' of the data file

CREATE TABLE [FillerTable] (

[c1] INT IDENTITY,

[c2] CHAR (8000) DEFAULT 'filler');

GO

 

-- Fill up the filler table

INSERT INTO [FillerTable] DEFAULT VALUES;

GO 1280

 

-- Create the production table, which will be 'after' the filler table in the data file

CREATE TABLE [ProdTable] (

[c1] INT IDENTITY,

[c2] CHAR (8000) DEFAULT 'production');

CREATE CLUSTERED INDEX [prod_cl] ON [ProdTable] ( [c1]);

GO

 

INSERT INTO [ProdTable] DEFAULT VALUES;

GO 1280

 

-- Check the fragmentation of the production table

SELECT

[avg_fragmentation_in_percent]

FROM sys .dm_db_index_physical_stats (

DB_ID ( N'DB_TEST'), OBJECT_ID ( N'ProdTable'), 1 , NULL, 'LIMITED');

GO

 

 

데이터베이스를 Shrink 하기 전의 논리적 단편화는 0.5% 밖에 되지 않는다.

 

이제 FillerTable을 Drop시키고 데이터베이스를 Shrink하고 난 다음에 클러스터드 인덱스의 단편화를 확인해보자.

 

-- Drop the filler table, creating 10MB of free space at the 'front' of the data file

DROP TABLE [FillerTable];

GO

 

-- Shrink the database

DBCC SHRINKDATABASE ([DB_TEST] );

GO

 

-- Check the index fragmentation again

SELECT

[avg_fragmentation_in_percent]

FROM sys .dm_db_index_physical_stats (

DB_ID ( N'DB_TEST'), OBJECT_ID ( N'ProdTable'), 1 , NULL, 'LIMITED');

GO

 

 

 

논리적 단편화가 거의 100% 가까이 발생되었다. 데이터베이스 Shrink 작업은 100% 인덱스 단편화를 발생한다고 봐야될 것이다.

인덱스를 사용하여 효율적으로 범위 스캔(Range Scan)을 하는 이점은 모두 없어질 것이다.

 

데이터 파일을 Shrink하는 작업은 한 번에 하나의 파일에서 GAM bitmap을 사용하여 파일에 가장 높게 할당된 페이지를 찾아낸다.

그리고 계속 해당 페이지를 앞으로 이동시킬 수 있을 때까지 이동시키게 된다. 이렇게 되면 미리 예약된 클러스터드 인덱스의 순서들이 대부분 조각나게 되는 것이다.

 

DBCC SHRINCKFILE, DBCC SHRINKDATABASE, auto-shrink 모두 다 동일하게 안 좋다.

데이터 파일을 shrink 하는 작업은 많은 I/O를 발생시키고, 많은 CPU를 사용하며 많은 트랜잭션 로그를 발생시킨다.

 

데이터 파일 Shrink는 규칙적인 데이터베이스 점검 사항에 포함될 항목은 아니며, auto-shrink 의 경우 절대로 하지 말아야한다.

위의 코드는 SQL 2005, 2008에서 동일하게 적용된다.

 

그러면 어떤 경우에 Shrink 를 해야되는 것인가? 매우 큰 데이터베이스에서 더 이상 저장 공간이 부족하여 대량의 데이터를 삭제해야 하거나, 또는 삭제하기 전에 빈 파일 공간이

필요할 경우에는 가능하다.

 

추천 방법은 다음과 같다.

- 새로운 파일 그룹을 만든다.

- 영향받는 모든 테이블과 인덱스를 새로운 파일 그룹에 CREATE INDEX... WITH (DROP_EXISTING = ON) 구문으로 이동한다.

테이블을 이동하며 단편화를 제거하는 작업을 동시에 하는 것이다.

- 오래된 파일 그룹을 shrink 한다.

 

만일 어쩔수 없이 데이터 파일을 shrink 해야되는 경우에느 인덱스 단편화를 야기할 수 있다는 점과 축소한 다음 발생하는 퍼포먼스 문제를 고려해야 한다.

데이터 파일이 증가하지 않고 인덱스 단편화를 제거하는 방법으로는 DBCC INDEXDEFRAG 나 ALTER INDEX .. REORGANIZE를 사용하는 것이 유일하다.

이 명령어는 오직 8KB 만의 추가 페이지를 필요로 하며, 새롭게 전체 인덱스를 생성하거나 인덱스를 리빌드하는 작업 대신에 사용할 수 있다.

 

가급적 데이터 파일을 Shrink 하는 것은 피하도록 하자.