Bagikan melalui

SQL MYSTERIES: Tracing BCP Might Fool You

  • Artikel

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