i recently set up sql transnational replication and i have some performance issue
when i'm initializing/re-initializing it from snapshot.
The biggest table have is about 8gb as snapshot file
network connection is fine – when im copying (just to check network speed is not the case) this snapshot file into desktop of this subscriber machine then it is around 200 MBps.
When this source machine witch is distributor also is bulk inserting this table into subscription database then CPU on this subscriber machine almosts sleeps , batch requests is between 0 and 1 and DATABASE IO is 4 MBps max…
Why it is going so slow ? is there any way to speed this up ? 8gb table will be +- 30min what if i will have 50gb table some day?
Can it be the case that the publisher machine is distributor also ? and because of that there are some network bulk insert commands limitations ?
It is how i think that in that case that the distributor is actually opening this snapshot that he generates before and doing this bulk insert like thru linked server in that case?
On my destination recovery models is simple so it is not this also.
thanks and regards!
Best Answer
Is this a push subscription or a pull subscription? For Push the Snapshot Agent runs at the distributor; for Pull it runs at the subscriber. The Snapshot Agent reads the snapshot files and connects to the subscriber to create the tables and bulk load them.
You can also copy the snapshot folder to an Alternate Snapshot Location and have the Snapshot Agent read from there. If you're using Push Subscriptions because you have SQL Express on the subscriber and so don't have SQL Agent, you can syncronize your Pull Subscription through PowerShell.
Look at the Wait Stats and CPU utilization on the Subscriber.
Yes. Called Local Distribution. Most common configuration.