대량 가져오기 최적화 지침
이 항목에서는 여러 개의 대량 가져오기 시나리오의 성능 최적화 지침에 대해 설명합니다.
단일 클라이언트 또는 스트림에서 빈 테이블로 데이터 가져오기
단일 클라이언트 또는 스트림에서 일부가 채워져 비어 있지 않은 테이블로 데이터 가져오기
[!참고]
증분 대량 가져오기라는 비어 있지 않은 테이블로 데이터 가져오기. 증분 대량 가져오기의 주요점은 먼저 인덱스를 삭제해야 하는지 여부에 있습니다.
여러 클라이언트 또는 스트림에서 테이블 수준 잠금으로 데이터를 병렬로 가져오기
MicrosoftSQL Server 인스턴스 간 데이터 복사
또한 이 항목에서는 대량 가져오기 작업 동안의 테이블 잠금 및 로깅에 대해 간략하게 설명합니다.
단일 클라이언트 또는 스트림에서 빈 테이블로 데이터 가져오기
단일 클라이언트나 스트림에서 빈 테이블로 데이터를 가져올 경우 Microsoft는 다음을 수행할 것을 권장합니다.
TABLOCK 한정자(세 가지 대량 가져오기 방법에서 모두 힌트 또는 옵션으로 사용 가능)를 지정합니다. TABLOCK을 사용하면 대량 작업을 수행하는 동안 테이블 수준 잠금이 적용되고 개별 행 잠금 오버헤드가 제거됩니다. 자세한 내용은 대량 가져오기를 위한 잠금 동작 제어를 참조하십시오.
로깅을 최소화합니다. 자세한 내용은 대량 가져오기의 최소 로깅을 위한 선행 조건을 참조하십시오.
인덱스를 다음과 같이 처리합니다.
bcp, BULK INSERT 또는 INSERT ... SELECT * FROM OPENROWSET(BULK...)을 사용하는 경우 테이블이 비어 있고 데이터 파일의 클러스터형 인덱스와 데이터가 클러스터형 인덱스 키 열과 일치하도록 정렬되어 있으면 다음 작업을 추가로 수행합니다.
클러스터형 인덱스가 이미 있는 데이터를 대량으로 가져옵니다.
ORDER 힌트와 TABLOCK 힌트를 지정합니다. 자세한 내용은 데이터 대량 가져오기 중 정렬 순서 제어를 참조하십시오.
빈 테이블의 경우 이 방법은 정렬 단계가 없으므로 데이터를 가져온 후 클러스터형 인덱스를 만드는 것보다 훨씬 빠릅니다.
[!참고]
비어 있지 않은 테이블에 인덱스가 있으면 대량 가져오기 작업은 대량 로그 복구 모델에서도 모두 로그됩니다. 인덱스 제거 여부를 결정하려면 인덱스가 없는 테이블로 대량 가져오기를 수행할 때의 이점이 인덱스를 제거하고 다시 만드는 비용보다 더 큰지를 고려해 보십시오.
인덱스 있는 빈 테이블로 데이터를 대량으로 가져오고 일괄 처리 크기를 지정하면 첫 번째 일괄 처리 후에 해당 테이블은 비어 있지 않은 테이블이 됩니다. 데이터는 두 번째 일괄 처리부터 모두 로그됩니다. 인덱싱된 빈 테이블의 경우 단일 일괄 처리로 대량 가져오기를 수행하는 것을 고려하십시오.
[!참고]
일괄 처리 크기를 지정하지 않으면 기본적으로 SQL Server 쿼리 최적화 프로그램은 기본 크기를 데이터 파일 크기로 간주합니다. 성능을 향상시키려면 ROWS_PER_BATCH 또는 KILOBYTES_PER_BATCH 한정자를 최적화 프로그램에서 데이터 파일의 대략적인 행 수에 대한 힌트로 사용할 수 있습니다. 자세한 내용은 대량 복사 일괄 처리 크기 관리를 참조하십시오.
일반적으로 인덱스 있는 테이블보다 인덱스 없는 테이블로 대량 가져오기가 더 빨리 수행됩니다. 그러므로 빈 테이블에 인덱스가 있으면 테이블로 데이터를 가져오기 전에 해당 인덱스를 제거하고 나중에 다시 만들어야 합니다. 클러스터형 키 열을 기준으로 데이터를 정렬하지 않거나 테이블이 비어 있을 경우 모든 인덱스를 제거하고 데이터를 가져온 다음 새 인덱스를 만듭니다.
단일 클라이언트 또는 스트림에서 비어 있지 않은 테이블로 데이터 가져오기
이미 데이터가 있는 테이블(비어 있지 않은 테이블)로 데이터를 가져오는 작업을 증분 대량 가져오기라고 합니다. 증분 대량 가져오기의 주요점은 먼저 인덱스를 삭제해야 하는지 여부에 있습니다. 두 가지 옵션이 있습니다. 하나는 인덱스를 유지하는 것이고 다른 하나는 인덱스를 삭제하고 나중에 다시 만드는 것입니다.
단일 클라이언트 또는 스트림에서 비어 있지 않은 테이블로 데이터를 가져올 경우 인덱스 유지 여부는 테이블의 기존 데이터 양에 대한 가져온 새 데이터 양에 따라 결정됩니다.
가져온 새 데이터가 기존 데이터 양에 비해 적으면 인덱스를 제거하고 다시 작성하는 작업이 비효율적으로 수행됩니다. 인덱스를 다시 작성하는 데 필요한 시간이 대량 작업 중에 저장된 시간보다 길어질 수 있습니다.
이와 반대로 비교적 많은 양의 새 데이터를 가져오면 대량 작업을 수행하기 전에 테이블에서 인덱스를 제거하는 작업의 성능이 향상되고 실제로 인덱스 지정에 필요한 시간은 증가하지 않습니다.
다음 표에서는 인덱스를 제거하기 전에 테이블에 있어야 하는 최소한의 새 데이터 양 목록을 보여 줍니다. 최소량은 테이블의 전체 데이터에 비례합니다. 이 양은 인덱스 유형과 조합에 따라 달라집니다. 새 데이터가 지정된 인덱스 유형이나 인덱스 그룹의 권장 비율보다 큰 경우 대량 작업을 수행하기 전에 인덱스를 제거하고 나중에 다시 만드는 것을 고려하십시오. 이러한 숫자는 기존 데이터 및 로드될 데이터의 데이터 패턴에 영향을 받으므로 일반 지침으로만 사용해야 합니다.
인덱스 |
상대적 새 데이터 양 |
|---|---|
클러스터형 인덱스만 |
30% |
클러스터형 인덱스와 비클러스터형 인덱스 한 개 |
25% |
클러스터형 인덱스와 비클러스터형 인덱스 두 개 |
25% |
비클러스터형 단일 인덱스만 |
100% |
비클러스터형 인덱스 두 개 |
60% |
여러 클라이언트 또는 스트림에서 테이블 수준 잠금으로 데이터를 병렬로 가져오기
둘 이상의 프로세서가 있는 컴퓨터에서 SQL Server를 실행하고 있고 테이블로 대량으로 가져온 데이터를 개별 데이터 파일로 분할할 수 있는 경우 여러 클라이언트에서 테이블로 데이터를 병렬로 가져오면 성능을 향상시킬 수 있습니다. 여러 클라이언트에서 하나의 테이블로 대량 가져오기를 수행하는 경우 각 클라이언트에 해당 입력 데이터 파일이 있어야 합니다.
여러 클라이언트에서 데이터를 한 테이블로 가져올 경우에는 다음 사항을 고려하십시오.
여러 개의 대량 가져오기 스트림은 잠재적으로 서로를 차단할 수 있습니다.
이를 방지하기 위해 SQL Server는 BU(대량 업데이트) 잠금이라고 하는 특수 내부 잠금 기능을 제공합니다. BU 잠금을 적용하려면 다른 대량 가져오기 스트림을 차단하지 않고 각 가져오기 스트림에 TABLOCK 옵션을 지정해야 합니다. 이렇게 하면 여러 클라이언트가 테이블에 액세스할 때 충돌이 발생하지 않습니다. 그러나 BU 잠금은 인덱스 없는 테이블(빈 테이블 또는 비어 있지 않은 테이블)에서만 사용할 수 있습니다. 인덱스가 있는 테이블에 TABLOCK을 지정하면 병렬로 대량 가져오기를 수행할 수 없습니다. 자세한 내용은 대량 가져오기를 위한 잠금 동작 제어를 참조하십시오.
테이블에 인덱스가 있을 경우 데이터를 대량으로 가져오기 전에 모든 인덱스를 제거하면 BU 잠금을 사용할 수 있습니다. TABLOCK을 사용하여 병렬로 데이터를 대량으로 가져온 다음 인덱스를 다시 만들 수 있습니다. 비어 있지 않은 테이블에 인덱스가 있으면 대량 가져오기 작업은 대량 로그 복구 모델에서도 모두 로그됩니다. 인덱스 제거 여부를 결정하려면 인덱스가 없는 테이블로 대량 가져오기를 수행할 때의 이점이 인덱스를 제거하고 다시 만드는 비용보다 더 큰지를 고려해 보십시오.
[!참고]
보조 인덱스를 제거한 경우 별도의 클라이언트에서 각 보조 인덱스를 만들어서 보조 인덱스를 병렬로 다시 만드는 것을 고려하십시오.
인덱스를 삭제하고 다시 만들지 않으려면 TABLOCK 힌트를 지정하지 않고 병렬 가져오기를 수행하면 됩니다. 그러나 이 경우 여러 개의 대량 가져오기 스트림은 잠재적으로 서로를 차단할 수 있고 대량 로그 최적화도 사용할 수 없습니다. 차단을 최소화하려면 더 적은 일괄 처리 크기를 지정하고 대량 가져오기 작업 중에 ORDER 힌트를 사용하여 정렬 단계를 제거하면 됩니다.
데이터를 클라이언트마다 한 파일씩 여러 개의 입력 파일로 분할해야 합니다. CPU를 가장 효율적으로 사용하려면 데이터 파일의 크기가 비슷해야 합니다.
자세한 내용은 테이블 수준 잠금으로 데이터를 병렬로 가져오기를 참조하십시오.
대량 가져오기 작업 동안의 테이블 잠금 및 로깅
다음 표에서는 대량 가져오기 작업을 실행하는 동안 테이블 스키마에 따라 잠금 유형이 어떻게 결정되는지를 간략히 설명합니다. 또한 테이블이 비어 있는지 여부, 작업에 대해 TABLOCK이 설정되어 있는지 여부, 그리고 데이터베이스에서 대량 로그 복구 모델을 사용하는 경우 발생하는 로깅 유형을 보여 줍니다.
[!참고]
첫 번째 일괄 처리 성공 후 테이블은 더 이상 비어 있는 상태가 아닙니다.
대량 가져오기 대상 테이블 |
테이블이 비어 있는지 여부 |
TABLOCK 설정 여부 |
잠금 유형 |
대량 로그 및 단순 복구 모델의 로깅 유형 |
|---|---|---|---|---|
힙 |
예 |
예 |
BU-Tab |
대량 로깅 |
힙 |
예 |
아니요 |
IX-Tab |
전체 로깅 |
힙 |
아니요 |
예 |
BU-tab |
대량 로깅 |
힙 |
아니요 |
아니요 |
IX-Tab |
전체 로깅 |
비클러스터형 인덱스가 한 개 포함된 힙 |
예 |
예 |
SCH-M |
대량 로깅 |
비클러스터형 인덱스가 한 개 포함된 힙 |
예 |
아니요 |
IX-Tab |
전체 로깅 |
비클러스터형 인덱스가 한 개 포함된 힙 |
아니요 |
예 |
SCH-M |
|
비클러스터형 인덱스가 한 개 포함된 힙 |
아니요 |
아니요 |
IX-Tab |
전체 로깅 |
클러스터형 인덱스 |
예 |
예 |
SCH-M |
대량 로깅 |
클러스터형 인덱스 |
예 |
아니요 |
IX-Tab |
전체 로깅 |
클러스터형 인덱스 |
아니요 |
예 |
X-TAB |
전체 로깅 |
클러스터형 인덱스 |
아니요 |
아니요 |
IX-Tab |
전체 로깅 |
SQL Server 인스턴스 간에 데이터 복사
SQL Server의 한 인스턴스에서 다른 인스턴스로 데이터를 대량으로 복사하려면 bcp를 사용하여 테이블 데이터를 데이터 파일로 내보냅니다. 그런 다음 대량 가져오기 방법 중 하나를 사용하여 파일에서 테이블로 데이터를 가져옵니다. 네이티브 형식이나 유니코드 네이티브 형식을 사용하여 대량 내보내기 및 대량 가져오기 작업을 수행합니다.
[!참고]
이러한 형식에 대한 자세한 내용은 네이티브 형식을 사용하여 데이터 가져오기 및 내보내기 및 데이터를 가져오거나 내보내기 위해 유니코드 네이티브 형식 사용을 참조하십시오.
원본 테이블에 클러스터형 인덱스가 있거나 데이터를 클러스터형 인덱스가 있는 테이블로 대량으로 가져오려면 다음을 수행합니다.
bcp에 SELECT 문의 query 옵션을 사용하고 ORDER BY 절을 사용하여 정렬된 데이터 파일을 만들어서 원본 테이블에서 데이터를 대량으로 내보냅니다. 자세한 내용은 bcp 유틸리티를 참조하십시오.
데이터를 SQL Server로 대량으로 가져올 경우 ORDER 한정자를 사용합니다. 이 한정자는 bcp와 BULK INSERT에서만 지원됩니다. 자세한 내용은 데이터 대량 가져오기 중 정렬 순서 제어를 참조하십시오.
자세한 내용은 서버 간의 데이터 복사를 참조하십시오.