Sql-server – Can BCP Out cause resource contention

bcpsql serversql server 2014stored-procedures

I had these two things running in parallel in the same database – 

BCP "SELECT * FROM [ServerName].[DatabaseName].[dbo].[TableName] WITH (NOLOCK)" queryout "\\LocationToMyOutputFile\TableName.csv" -c -S "ServerName" -T
  1. A BCP out command on a PowerShell prompt which looked like above.
  2. A stored procedure which was using the same database. The stored procedure was executing in the same database as the BCP out command (but was not using the same table). The SP drops a table and inserts records into it from a CTE (the CTE is made from tables of same database in question).

BCP out has 1.16 Billion records to output and it took 3565 seconds (~1 hour) to finish.
When the BCP out command was executing the SP kicked in and it took roughly 7 hours to finish. In general, the particular SP finishes in 15-20 minutes.

Here are a few things which I noticed while both were running in parallel –

  1. The wait type for the BCP out command as seen from SQL Monitor was PAGEIOLATCH_SH.

  2. The wait type for the SP were PAGEIOLATCH_SH and PAGEIOLATCH_EX.

  3. While both of them were running in parallel the SP went into SUSPENDED state as seen when executing sp_who2.

  4. After the BCP out command finished executing in about 1 hour, the SP continued to be in SUSPENDED state with very high DiskIO(which was continuously increasing) for another few hours.

After observing the above, these are the questions which come to my mind –

  1. AFAIK, BCP out command doesn't lock the source table and should not affect anything, but seeing the wait types I can infer that BCP Out first loaded the entire 1.16 Billion records from the disk to memory and then output(read outputted) it to flat-file. This caused the SP to go into SUSPENDED state since memory was not available for it to load records from the disk. Is my understanding correct?
  2. Secondly, when the BCP out finished executing, why didn't the SP change its state from SUSPENDED, since all resources would have been free. There was nothing else executing in the database other than the culprit SP. Is it because even after the BCP out finished it still had records in the memory which didn't get flush automatically?

Please let me know your thoughts as I am confused here whether my understanding is correct.

SQL Server version is Microsoft SQL Server 2014 (SP2-CU17-GDR) (KB4505419) - 12.0.5659.1 (X64).

Best Answer

I would guess these symptoms (PAGEIOLATCH_SH waits and longer execution times) are simply due to increased physical IO. SQL Server has to scan the entire source table during the BCP which may evict pages from buffer cache that the stored proc later references. When run by itself, the pages needed by the proc probably remain in memory.

Your assumption about BCP needing to read the entire table into memory before writing the result is incorrect. BCP streams results from the source query to the output file.

Related Question