SQL MYSTERIES: Tracing BCP Might Fool You

Moved from bobsql.com

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.