SQL Server 2016 대용량 데이터 로드시 최소 로깅(minimal logging) 과Batch Size
· Version : SQL Server 2016
SQL Server에서는 대용량 데이터를 로드(bulk load)할때 시스템의 오버헤드를 줄이기 위해 최소 로깅(minimal logging)을 사용한다. 최소 로깅에 대해서는 아래 링크를 참고한다.
· Bulk Import Optimizations (Minimal Logging) : https://blogs.msdn.microsoft.com/sqlserverstorageengine/2008/02/04/bulk-import-optimizations-minimal-logging/
SQL Server 2016에서는 최소 로깅의 기능 개선 일부로 인덱싱된 테이블에 대해 최소 로깅을 위해 더이상 추적플래그 T610을 활성화 할 필요가 없으며 일부 다른 추적 플래그(1118, 1117, 1236, 8048)와 결합하여 부분적으로 기록된다.
SQL Server 2016에서 대량 로드 작업시 새 페이지가 할당 될 때 앞에서 설명한 최소 로깅을 위한 필수 조건이 충족되면 해당 새 페이지를 순차적으로 채우는 모든 행이 최소한으로 기록된다. 기존 페이지에 데이터를 삽입할 경우 인덱스 순서를 유지하기 위한 페이지 분할 및 이동되는 행에 대한 내용은 완전히 기록된다. 또한 ALLOW_PAGE_LOCKS를 ON으로 설정(기본값 ON) 할 경우 페이지 할당 도중에 페이지 잠금이 획득되어 페이지 또는 익스텐트 할당만 기록되므로 최소한의 로깅이 가능하다.
아래 링크는 데이터로딩 성능 가이드이다.
· The Data Loading Performance Guide : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd425070(v=sql.100)
아래 표는 SQL Server 2016의 최소 로깅 조건을 위한 가이드 이다.
Table Index | Rows in table | Hints |
Heap | Any | TABLOCK |
Heap | Any | None |
Heap + Index | Any | TABLOCK |
Cluster | Empty | TABLOCK, ORDER(1) |
Cluster | Empty | None |
Cluster | Any | None |
Cluster | Any | TABLOCK |
Cluster + Index | Any | None |
Cluster + Index | Any | TABLOCK |
1. INSERT … SELECT를 사용하는 경우 ORDER 힌트는 지정하지 않아도 되지만 행은 클러스터형 인덱스와 동일한 순서여야 한다. BULK INSERT를 사용하는 경우 ORDER 힌트를 사용해야 한다. 2. 동시 적재는 특정 조건하에서만 가능하다. 또한 새로 할당된 페이지에 기록된 행만 최소로 기록된다. 3. 최적화 프로그램에서 선택한 계획에 따라 테이블의 비 클러스터형 인덱스가 완전 또는 최소로 기록될수 있다. |
대량 로드 작업의 최소 로깅 작업은 데이터가 미리 ORDER되거나 순차적으로 로드 될 때 인덱스에서 데이터 로드 작업의 성능을 향상시키는데 도움이 되지만 대량 로드로 할당 된 페이지 의 범위 및 효율적인 공간 활용 또한 빠른 성능을 달성하는데 중요한 역할을 한다. 대량 로드 일괄처리는 기존에 할당된 익스텐트의 여유 공간을 확인하지 않고 우회하여 새로운 익스텐트를 할당하여 사용하므로 삽입 성능을 최적화 한다. 이러한 방식 때문에 batch size에 따라 비효율적인 공간으로 오브젝트가 예약되기도 한다. 아래 표는 다양한 batch size로 1000개의 레코드를 대량 로드하고 sp_spaceused를 실행한 결과이다. batch size가 10인 대량 로드는 할당된 오브젝트에서 사용되지 않는 공간이 가장 많고 batch size 1000이 할당된 오브젝트에서 사용되지 않는 공간이 가장 작다. 또한 batch size 1000 일 경우 모든 레코드를 단일 일괄 처리로 로드 한다.
BatchSize | Data(KB) | Index_Size(KB) | Unused(KB) | Reserved(KB) |
10 | 808 | 8 | 5656 | 6472 |
100 | 168 | 8 | 1176 | 1352 |
1000 | 128 | 8 | 128 | 264 |
최소 로깅 모드에서 기억해야할 중요한것은 모든 레코드를 기록하지 않고 할당만 기록하기 때문에 일괄처리가 커밋되는 즉시 데이터 페이지가 플러시 된다. 따라서 큰 batch size를 선택하면 쓰기 I/O 버스트가 발생할 수 있다. I/O 서브시스템이 쓰기 I/O 버스트를 처리할 수 없는 경우 대량 로드 작업 및 SQL Server 인스턴스에서 실행되는 다른 모든 트랜잭션의 성능에 악영향을 미칠 수 있다. 따라서 batch size의 크기를 산정할 때 로드되는 데이터의ROW의 평균 사이즈를 익스텐트(64KB) 크기로 선택하여 쓰기 I/O 를 64K로 제한하여 효율적으로 범위 내의 공간을 채우고도록 하는 것이 중요하다. 대부분의 I/O 서브 시스템에서 기본 디스크 I/O 성능에 따라 효율적인 공간 활용과 최적의 대량 로드 성능 간의 균형을 맞추기 위한 쉬운 방법으로 1 extent (64KB)에서 64 extent (4MB) 크기 사이의 배치 크기를 선택 할 수 있다.
어떤 이유로든 배치 크기를 변경할 수 없거나 기본 최소 로깅 동작으로 향상된 데이터 로드 성능을 볼 수 없는 경우 추적 플래그 T692를 사용하여 SQL Server 2016에서 빠른 삽입 동작을 사용하지 않도록 설정할 수 있다. (일반적인 상황에서는T692는 거의 사용되지 않는다.) 대용량 데이터를 로드할 때 최소 로깅을 사용하지 않을수도 있지만 트랜잭션 로그 오버헤드가 증가할 수 있다.
[참고자료]
2018-07-24 / Sungwook Kang / http://sqlmvp.kr
SQL Server, bulk load, SQL 2016, minimal logging, 최소 로깅, 미니멀 로깅, T619, 인덱스, 페이지 할당, page allocation, batch size
출처: https://sqlmvp.tistory.com/1265?category=618825 [Database Lab]
