SQL MYSTERIES: Tracing BCP Might Fool You
Today I was tracking a large BCP.exe ‘IN’ operation and monitoring the sys.dm_exec_requests entries. The ‘BULK INSERT’ command entry appeared, showing the CPU, reads, writes, … but these values where getting reset from time to time. Here are 3 snapshots from my system.
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
Instead of a single BULK INSERT batch I saw multiple BULK INSERT batches taking place on the same session. When you specify the BCP, batch size (-b) the rows are streamed until the batch size is reached, a commit is issued and a new batch is started for subsequent rows. Thus, you see a series of BULK INSERT operations and not the BCP in it entirety.
Bob Dorr - Principal Software Engineer SQL Server