출처 : 블로그
원문 : 그는 무슨생각을 하는가?
SQL 서버 데이터 대량 복사 최적화 방안 10가지
이번 기사에서는 수억건에 달하는 대량의 데이터를 가능한 빠르게 복사하는 방법에 대해서 알아보자.
가능한 BULK INSERT 문을 활용한다.
가능하면 bcp 유틸리티보다는 BULK INSERT 문을 사용하여 데이터를 SQL Server로 대량 복사한다. BULK INSERT 문이 bcp 유틸리티보다 빠르다. 이유는 로깅을 하지 않기 때문이다.
비교되는 기존데이터의 양과 인덱스 유형에 영향을 받는다.
대량 복사 작업의 성능을 높이기 위해 이러한 옵션들 중 어느 것을 사용해야 하며 사용할 수 있는지를 결정하는 요인에는 다음 두 가지가 있다.
- 테이블에 복사될 데이터 양에 비교되는 테이블의 기존 데이터 양
- 테이블의 인덱스 개수 및 유형
또한 이러한 요인은 데이터가 단일 클라이언트로부터 테이블에 대량 복사되는지 또는 여러 클라이언트로부터 병렬로 대량 복사되는지에 따라 달라지기도 한다.
단일 클라이언트에서 데이터를 빈 테이블로 로드할 때는 다음 항목을 지정하는 것이 좋다.
- TABLOCK : 대량 복사 작업 중 테이블 수준의 잠금이 이루어진다.
- ROWS_PER_BATCH : 전체 파일 크기를 나타내는 단일 일괄 처리를 지정하는 것이 좋다.
- 로깅되지 않은 대량 복사 작업 : 로깅되지 않은 작업 수행 후에는 트랜잭션 로그 백업을 만들어서는 안 된다. 최소로 로깅을 사용하는 대량 복사 작업을 진행해야 한다.
인덱스를 지운후 복사하고 이후 생성시킨다.
테이블에 클러스터된 인덱스가 있으며 데이터 파일의 데이터가 클러스터된 인덱스 키 열에 일치하도록 정렬된 경우에는 클러스터된 인덱스가 이미 있는 테이블로 데이터를 대량 복사하고 ORDER 참고를 지정한다. 데이터가 테이블로 복사된 후에 클러스터된 인덱스를 만드는 것보다 이것이 훨씬 빠르다.
테이블에 클러스터되지 않은 인덱스도 있는 경우에는 데이터를 테이블로 복사하기 전에 이 인덱스를 삭제한다. 일반적으로 데이터를 클러스터되지 않은 인덱스가 있는 테이블에 대량 복사하는 것보다는 클러스터되지 않은 인덱스가 없는 테이블로 대량 복사한 다음, 클러스터되지 않은 인덱스를 다시 만드는 것이 더 빠르다.
데이터가 로드 된 후 인덱스를 다시 만든다.
데이터를 기존 데이터가 있는 테이블로 복사할 때 기존 인덱스를 통한 대량 복사 작업 수행을 위한 권장 사항은 테이블에 이미 있는 데이터 양과 비교되는, 테이블에 복사될 데이터 양에 따라 달라진다. 테이블에 복사할 데이터 비율(테이블의 기존 데이터 양을 기준으로 함)이 커짐에 따라 테이블에서 모든 인덱스를 삭제하고, 대량 복사 작업을 수행한 다음, 데이터가 로드된 후 인덱스를 다시 만드는 것이 더 빠르다.
다음 기준에 부합되는 경우 가능한 인덱스를 삭제한 후 복사하고 다시 만든다.
다양한 유형의 인덱스에 대해 테이블에 추가될 데이터 양의 대략적인 기준 수치를 다음과 같이 제안할 수 있다. 이 비율을 초과하는 경우에는 인덱스를 삭제했다가 다시 만드는 것이 더 빠르다는 것을 알 수 있을 것이다.
- 클러스터된 인덱스, 추가되는 데이터 양이 30%
- 클러스터된 인덱스와 클러스터되지 않은 인덱스 한 개, 추가되는 데이터 양이 25%
- 클러스터된 인덱스와 클러스터되지 않은 인덱스 두 개, 추가되는 데이터 양이 25%
- 클러스터되지 않은 단일 인덱스, 추가되는 데이터 양이 100%
- 클러스터되지 않은 인덱스 두 개, 추가되는 데이터 양이 60%
여러 클라이언트에서 데이터를 한 테이블로 복사할 때는 다음 사항을 고려한다.
- 테이블의 모든 인덱스를 먼저 삭제한 다음, 그 테이블에서 다시 만든다. 별도의 클라이언트로부터 보조 인덱스를 동시에 각각 만드는 방식으로 보조 인덱스를 병렬로 다시 만드는 것을 고려한다.
- 정렬된 데이터와 ORDER 참고를 사용하면 로드 중에 클러스터된 인덱스가 나타나지 않으므로 성능에 영향을 주지 않는다.
- 데이터를 클라이언트마다 한 파일씩 여러 개의 입력 파일로 분할해야 한다.
단일 클라이언트로부터의 대량 복사 작업과 마찬가지로 다음 사항을 지정한다.
- TABLOCK : 대량 복사 작업 중 테이블 수준의 잠금이 이루어진다.
- ROWS_PER_BATCH : 각 클라이언트에 대해 전체 클라이언트 파일 크기를 나타내는 단일 일괄 처리를 지정하는 것이 좋다.
- 데이터베이스 옵션 : select into/bulkcopy를 true로 설정하여 로깅되지 않은 작업을 사용한다.
SQL Server를 실행하는 컴퓨터에서 다른 컴퓨터로 데이터를 복사하는 경우에는 원시 형식 또는 유니코드 원시 형식을 사용하여 모든 대량 복사 작업을 수행한다.
원본 테이블에 클러스터된 인덱스가 있거나 데이터를 클러스터된 인덱스가 있는 테이블로 대량 복사하려면
- SELECT 문과 해당 ORDER BY 절을 지정하여 정렬된 데이터 파일을 만드는 원본 테이블에서 데이터를 대량 복사한다.
- 데이터를 SQL Server로 대량 복사할 때는 ORDER 참고를 사용한다