2

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).

1 Answer 1

1

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.

2
  • Alright got you. But what about the second part of the question Dan. That's the part which is more confusing. Given that no other query was running, the culprit SP should have picked up pace and executed shortly after the completion of the BCP command. But that didn't happen. Commented Jul 19, 2019 at 13:11
  • My thought is that the preceeding BCP had evicted pages needed by the proc, resulting in physical IO that would not have been needed otherwise. You could monitor page reads/sec with the proc running alone and compare that with the rate after the BCP.
    – Dan Guzman
    Commented Jul 19, 2019 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.