February 20, 2019 at 11:28 pm
Trying to copy a table over linked server using query below. The problem is it takes about the same time copying this table at 10 MB/sec disk throughput. Upgraded to faster disks (ssd) but still the time to transfer remained same as it was writing at 10 MB/sec.
Insert into call_usage_new with (tablock) select * from linkedserver.db.dbo.call_usage
What setting needs to be changed or should I use any additional options/different driver with the linked server to increase throughput.
However, if I parition the table and split it into two, each thread writes at 10 MB/sec and I am able to load twice fast, so overall the disks can keep up with this load.
Nothing else runs on either boxes during the loads and no indexes on target.
Thanks.
February 21, 2019 at 3:02 am
Could it be the network, rather than the disc(s)? Considering this is a linked server, I assume that the location of the data is from a different host, so perhaps the problem is the speed the data is transferring over the LAN/WAN? Just a wild guess though.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 21, 2019 at 3:23 am
Both the servers support data transfers upto 2 gbps on the NICs and there is an 8 gig pipe between them. If something like QOS is enabled I dont know but can confirm. Other than asking someone from network team, is there any specific way I can confirm this?
When I do a disk IO test on target it exceeds 1024 MB/sec write speeds by using a test file that simulates a typical sql workload.
IS sequential throughput a factor of IOPS times the bytes per transfer (network packet size)? Although the second disks have 4 times as much IOPs capacity so throughput should still have quadrupled (in theory)
February 22, 2019 at 12:12 am
You need to review wait stats and run performance counters on your server to identify where the bottleneck is. 10MB/s disk thoughput doesn't mean SQL is only capable of that, it could be that it is only receiving 10MB/s from the network to write to disk, or you could be CPU constrained so only 10MB/s of data is ready to be written to disk.
Use something like sp_whoisactive to check the waits while the insert is executing and start troubleshooting from there.
February 22, 2019 at 12:55 am
I used to have similar situation with linked server. Later we implement SSIS solution and observed significant performance gain.
I suggest go for SSIS.
ThanksSaurabh.D
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply