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
Last activity: Nov 4, 2022, 04:55 AM