BCP 실행시 동일 세션에서 여러개의  BULK INSERT 문으로 표시되는 이유

 

·       Version : SQL Server

 

SQL Server에서 BCP 명령을 사용하여 대량의 데이터를 로드할때, sys.dm_exec_requests 항목을 모니터링 해보면 command 항목에 BCP 대신 BULK INSERT 라고 표시되어 있다아래 표를 보면 동일한 세션에 BULK INSERT라고 여러개의 작업이 표시된 것을 확인할  있다.

cpu_time

total_elapsed_time

writes

session_id

start_time

command

1387

1396

32

51

2018-08-07 00:45:42.670

BULK INSERT

1930

1941

66

51

2018-08-07 00:46:02.087

BULK INSERT

632

638

32

51

2018-08-07 00:46:23.313

BULK INSERT

 

단일 BULK INSERT 대신 동일한 세션에 BULK INSERT 배치가 여러번 발생하는 이유는 무엇일까? BCP 명령을 사용할때옵션 -b (Batch Size)  지정하면서 배치크기에 도달할때까지 행이 스트링되고 커밋이 발생한다그리고 후속 행에 대해서 새로운 배치가 실행된다따라서 BCP 아닌 일련의 BULK INSERT 작업이 표시되는 것이다.

 

[참고자료]

·       https://blogs.msdn.microsoft.com/bobsql/2018/08/07/sql-mysteries-tracing-bcp-might-fool-you/

·       https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017

 

2019-09-12 / Sungwook Kang / http://sungwookkang.com

 

 

SQL Server BCP, Bulk Load, BULK INSERT



출처: https://sqlmvp.tistory.com/1337 [Database Lab]




profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.