데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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 거의 사용되지 않는다.) 대용량 데이터를 로드할  최소 로깅을 사용하지 않을수도 있지만 트랜잭션 로그 오버헤드가 증가할  있다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in-bulk-load-operations/

 

 

 

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]
No. Subject Author Date Views
2150 SQL Server 2019 에서 업그레이드된sp_estimate_data_compression_savings 프로시저 (컬럼스토어 압축율 예상) jevida(강성욱) 2019.03.26 521
2149 SQL Server 2019 에서 추가된sys.dm_db_page_info, sys.fn_PageResCracker 기능으로 대기 관련 정보 확인 jevida(강성욱) 2019.03.26 751
2148 SQL Server 2019에서 향상된 Rowstore batch mode jevida(강성욱) 2019.03.26 415
2147 SQL Server 2016부터 도입된 USE HINT를 사용한 추적 플래그 활성화 jevida(강성욱) 2019.03.26 433
2146 In-memory optimized table에 사용되는 Hash Index jevida(강성욱) 2019.03.26 387
2145 VM환경에서 AG를 구성하였을때VSS 백업 동작 변경 jevida(강성욱) 2019.03.25 394
2144 SQL Server 2016 향상된 가용성 그룹 – 데이터베이스 수준의 상태 탐지 장애조치 jevida(강성욱) 2019.03.25 417
» SQL Server 2016 대용량 데이터 로드시 최소 로깅(minimal logging) 과Batch Size jevida(강성욱) 2019.03.25 307
2142 SQL Server 설치시 발생하는 1638 오류 jevida(강성욱) 2019.03.25 409
2141 SQL Server 666코드의 고유 식별자 오류 jevida(강성욱) 2019.03.25 293
2140 SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅 jevida(강성욱) 2019.03.25 348
2139 SQL Server Scheduling and Yielding 트러블슈팅 jevida(강성욱) 2019.03.25 349
2138 SQL Server 2016 Tempdb 경합(contention) 최적화 jevida(강성욱) 2019.03.25 431
2137 XEvent를 사용하여 Auto tuning 작업 모니터링 jevida(강성욱) 2019.03.25 442
2136 SQL Server In-Memory OLTP에 ASP.NET 세션 상태 저장하기 jevida(강성욱) 2019.03.25 349
2135 SQL Server에서 JSON 데이터 저장하기 jevida(강성욱) 2019.03.25 414
2134 Azure SQL에서 네트워크를 구성하는 방법 jevida(강성욱) 2019.03.25 231
2133 SQL Server 네이티브 컴파일된 저장 프로시저 성능 모니터링 jevida(강성욱) 2019.03.25 435
2132 SQL Server 2017 소규모 시스템에서 향상된 리소스 사용 jevida(강성욱) 2019.03.25 256
2131 클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭 jevida(강성욱) 2019.03.25 392





XE Login