대용량 작업 시 주의 사항

대용량 데이터베이스에서 작업을 하려면 사전에 상당한 주의를 필요로 합니다. 대량의 배치를 실행하는 경우나 대량의 수정이나 삭제 작업 등은 충분한 저장공간을 필요로 할 뿐만 아니라 대량의 트랜잭션 로그 파일 공간을 필요로 합니다. 따라서, 작업 전에 데이터 파일과 로그 파일의 크기를 적절하게 구성해야 합니다. 이와 같은 사전 준비가 적절하지 않 으면 어려움을 겪을 수 있습니다. 다음에서 대용량 작업 시 주의 사항을 살펴 봅니다.

1) 대용량의 BCP

데이터 파일과 로그 파일은 미리 충분한 공간을 할당합니다. 로그 파일의 경우 로그가 백업된 뒤 TRUNCATE 를 수행해서 파일을 줄일 수 있는 공간은 CHECKPOINT의 LSN(로 그 시퀀스 번호) 과 최근에 발생한 COMMIT 의 LSN, 가장 오래된 활성 트랜잭션의 LSN 가운데 가장 오래된 LSN 가운데에서 가장 적은 값의 LSN이 기준이 됩니다. 따라서, 대형 의 트랜잭션이 발생 중에는 해당 트랜잭션 이후의 작업은 설사 COMMIT 되고 백업되었다 고 할지라도 로그를 잘라낼 수 없어서 로그 파일 크기를 줄일 수 없습니다. 해당 디스크에 빈 공간이 부족한 경우에는 임시로 로그 파일을 추가합니다. 작업이 종료되면 로그를 백업하고 임시로 만든 로그 파일을 삭제합니다. 또는, 데이터베이스의 복구 모델을 대량 로그(Bulk_logged)로 변경하여 작업하는 것도 해결책이 될 수 있습니다. 대량 로그 모드에 서는 로그 파일에는 BCP 작업이 시작되었다는 정보와 익스텐트 할당 등의 정보만 기록 되고 BCP 작업의 세부 변경 사항은 기록되지 않습니다. 그렇지만 BCP 작업으로 변경되거나 할당된 익스텐트의 정보가 각 데이터 파일의 BCM 이라는 페이지에 기록되고 로그 백업 시 해당 BCM의 정보를 가지고 변경된 데이터도 백업하게 됩니다.

또는 -b 옵션을 사용해서 적절한 값을 입력하는 것을 고려합니다. -b 옵션은 COMMIT 되는 행수를 지정합니다. 다음과 같이 지정하면 1000 행마다 COMMIT을 수행합니다.

그러나 이런 경우는 1000행마다 COMMIT되므로 인덱스의 단편화가 하나의 트랜잭션으 로 작업한 경우보다 훨씬 심하게 발생하게 됩니다.

2) 대용량의 삭제 / 수정

대량의 삭제 시 로그 파일의 사용을 줄이고 여러 번에 나누어 다음과 같이 삭제할 수 있습니다. SQL Server 2005는 DELETE 와 UPDATE 구문에서도 TOP 키워드를 사용할 수 있어서 편리합니다.

3) 대용량 인덱스 생성 작업

A. 클러스터드 인덱스를 생성하는 작업은 테이블 크기의 약 120% 공간을 추가로 필요로 합니다.

다음은 1,429MB 크기의 테이블에서 클러스터드 인덱스를 생성하는 경우입니다. 인덱스를 생성하기 전 데이터 파일 크기는 약 3,200MB인 것이 인덱스를 생성하는 동안 약 1,400MB 증가했음을 확인할 수 있습니다.

B. 데이터 파일에 여유 공간이 충분하지 않은 경우나 I/O의 경합을 감소시키고자 하는 경우에는 SORT_IN_TEMPDB 옵션을 사용합니다. 테이블이 저장되어 있는 디스크 에서는 읽기작업이 수행되고 tempdb가 있는 디스크에서는 정렬을 위한 쓰기 작업 이 진행되어 I/O가 분산됩니다.

C. 인덱스 생성 작업은 MAXDOP 쿼리 힌트를 지원하지 않으므로 병렬 작업의 범위를 지정하기 위해서는“Max Degree Of Parallelism”서버 옵션의 값을 수정하여야 합니다. 이 값을 적절하게 지정하여 인덱스 생성 작업이 전체 CPU를 점유하지 않도록 조치합니다. SQL Server 2005는 인덱스 생성과 재구성 작업 시에 MAXDOP 쿼리 힌트를 지원합니다.

D. 클러스터드 인덱스를 생성할 때는 테이블에 배타적 잠금이 걸립니다. 그러므로 작업이 종료될 때까지 해당 테이블에 대한 모든 작업이 블로킹됩니다. 그러므로 서비스 중에 작업하는 것은 피해야 합니다.

E. 넌클러스터드 인덱스를 생성할 때는 테이블에 공유 잠금이 걸립니다. 따라서,해당 인덱스가 만들어지는 테이블에서는 읽기 작업만 가능합니다. 그러므로 이 작업 역시 서비스 중에는 하지 않는 것이 좋습니다.

F. 인덱스를 재구성할 때 삭제 후 다시 생성하지 않도록 합니다. 그렇게 하지 않으면 삭제와 재생성으로 이중의 시간이 소요됩니다. CREATE INDEX ~WITH DROP_EXISTING 이나 DBCC DBREINDEX를 사용하면 작업시간을 단축하고 저장 공간도 적게 사용할 수 있습니다.

4) 파일 사이즈를 줄이는 작업

데이터베이스의 데이터 파일이 적절한 크기를 초과하여 지나치게 커졌을 경우 DBA는 해당 파일을 DBCC SHRINKFILE 명령어를 통해서 줄이게 됩니다. 이런 경우에 작업중인 테이블이나 페이지 등에 배타적 잠금을 걸게 되어 업무 동시성을 저해합니다. 따라서, 업무 시간 중의 작업을 삼가해야 합니다. 커다란 데이터 파일 하나를 적은 크기의 데이터 파일 여러 개로 분리해서 관리하는 것이 효율적입니다.

5) 대용량의 테이블인 경우

대용량의 테이블인 경우 인덱스도 대용량으로 생성되어 생성이나 재구성 작업이 효율적이지 못합니다. 또한 현재 운영에 필요하지 않는 오래된 데이터를 다른 데이터베이스나 테이블로 이동하는 작업을 진행할 경우에도 오랜 시간을 필요로 합니다. 따라서 대용량 테이블이나 인덱스의 경우 파티션 적용을 고려할 수 있습니다. SQL Server 2005는 테이블과 인덱스의 파티션을 지원합니다. SQL Server 2000은 파티션은 지원하지 않고 유사한 효과를 얻을 수 있는 분할된 뷰를 제공합니다. 온다.

6) 대용량 작업 중 서버가 장애를 입은 경우

SQL Server가 수정이 발생한 메모리의 데이터 페이지들을 하드 디스크의 데이터 페이지로 저장하는 방법은 크게 두 가지가 있는데 한 가지는 데이터 버퍼 캐시 공간의 사용률에 따라서 Lazy Writer 라는 프로세스가 동작?? 수행되는 인스턴스 복구 프로세스의 효율성을 위해서 CHECKPOINT 라는 프로세스가 동작합니다. 따라서, 대량으로 로드되던 데이터들은 서버가 다운되기 전에 이미 상당한 량의 데이터를 하드 디스크의 데이터 페이지에 저장했거나 저장공간을 할당받은 상태입니다. 그러나 트랜잭션은 COMMIT 하지 못하였으므로 데이터의 무결성을 유지하기 위해 SQL 서버는 서비스 시작 시점에 인스턴스 복구 프로세스를 진행하여, COMMIT 되었으나 데이터 파일에 기록되지 않은 트랜잭션을 롤포워드(ROLL FORWARD)하여 기록하고 COMMIT되지 않았으나 데이터 파일에 저장된 데이터나 할당 정보에 대해서 롤백 작업을 진행합니다.

이런 경우 롤백이 완료될 때까지 데이터베이스는 오랜 시간 동안 사용할 수 없습니다. SQL Server 2005는 인스턴스 복구 프로세스가 진행될 때 롤포워드 작업이 종료되면 즉시 데이터베이스를 사용할 수 있도록 하여 데이터베이스를 효율적으로 운영할 수 있습니다. 반면에 SQL Server 2000에서는 인스턴스 복구 프로세스가 종료될 때까지 기다리거 나 대용량의 배치 외에 다른 작업이 진행되지 않았다면 본 가이드의 [로그 파일 손상 시 복구] 부분을 참조하여 로그 파일을 삭제하고 새로 생성하는 방법으로 해당 데이터베이스 를 빠르게 사용할 수도 있습니다. 그러나, 해당 로그 파일에 롤백하고자 하는 배치 작업 외 에 다른 트랜잭션이 기록된 경우 트랜잭션 로그의 유실로 데이터의 무결성이 손상을 입게 될 수 있으므로 주의하여야 합니다. 해당 배치 작업만 실행된 경우라 할지라도 DBCC CHECKDB를 실행하고 sysindexes 시스템 테이블을 검사하여 일관성 오류가 발생한 테 이블이 존재하는지 여부와 논리적인 데이터의 무결성 여부를 반드시 확인해야 합니다.