Sample Header Ad - 728x90

BCP out significant throughput drop while using read committed snapshot isolation

1 vote
0 answers
130 views
I am running bcp to output data to a file. I cannot run bcp out in read committed mode because of the reasons mentioned in https://dba.stackexchange.com/questions/315798/snapshot-replication-corrupted-bcp-files . I tried using read committed snapshot isolation but found that there was a significant throughput drop. With RC, I am getting throughput of 52 MB/s. However, with RCSI, I am getting throughput of only 35 MB/s. I tried a normal select * with RC and RCSI and I got throughput of 25 and 26 MB/s respectively, which indicates that there is not much impact while reading snapshots from *tempdb*. I wanted to understand what internal architecture in bcp makes the throughput drop so high. The question is more about the relative numbers than the absolutes, but if it helps, I am using SSD persistent disk for both read and write. Write throughput of the disk to which I am writing is rated at 400 MB/s. **DB Version**: SQL Server 2019 Standard CU16-GDR \ **Data table**: ORDER_LINE from standard TPCC database\ **BCP version**: 17.10.0001.1\ **BCP command used**: bcp ORDER_LINE OUT out/order_line.dat -S tcp:127.0.0.1,1434 -U {username} -P {password} -d TPCC -t "," -r "\n" -F 1 -f format/order_line-n.fmt\ **Format file generated using**: bcp ORDER_LINE format nul -S tcp:127.0.0.1,1434 -U {username} -P {password} -d TPCC -n -f format/order_line-n.fmt\ **Metrics measured**: network sent_bytes_count There is a write load on the database generated by HammerDB . I ran bcp once using Read committed isolation level, then set READ_COMMITTED_SNAPSHOT to ON and then ran bcp again.
Asked by toros (23 rep)
Nov 3, 2022, 11:28 AM
Last activity: Nov 4, 2022, 04:55 AM