0

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!

Improve this question
2
  • Why are you copying the snapshot into the subscriber machine? Can't use a shared folder?
    – McNets
    Oct 2, 2020 at 9:59
  • i use shared folder - just copied to be sure that this is not some network limitation then deleted file ;)
    – Dorian
    Oct 2, 2020 at 10:03

1 Answer 1

Reset to default
2

actually opening this snapshot that he generates before and doing this bulk insert like thru linked server in that case?

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.

Why it is going so slow ?

Look at the Wait Stats and CPU utilization on the Subscriber.

Can it be the case that the publisher machine is distributor also ?

Yes. Called Local Distribution. Most common configuration.

Improve this answer
4
  • it is push subscription because we need this data almost online so yes agent is on distributor so on publication server. it is not expres . cpu utilization on subscriber is between 0 and 2% when loading...
    – Dorian
    Oct 5, 2020 at 9:29
  • i guess no more ideas ?:)
    – Dorian
    Oct 7, 2020 at 13:08
  • And the wait stats on the subscriber?
    – David Browne - Microsoft
    Oct 7, 2020 at 13:09
  • it is new VM specially for this replication so this server is doing nothing else while this slow bulk insert is happening... also publisher is 2008r2 and the subscriber is 2012 but i don't believe this may be the case of this 4MBps limit also ?
    – Dorian
    Oct 7, 2020 at 20:38

Your Answer

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

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