Snapshot Agent Performance –MaxBCPThreads
We noticed a large published database was taking a long time for the Snapshot to be applied to the subscriber. Looking further in sp_who and sys.sysprocesses we noticed only one Distribution Agent thread executing the bulk insert statements. It took a while but we discovered the publication was created using “concurrent snapshot” option.
-MaxBcpThreads number_of_threads
Specifies the number of bulk copy operations that can be performed in parallel. The maximum number of threads and ODBC connections that exist simultaneously is the lesser of MaxBcpThreads or the number of bulk copy requests that appear in the synchronization transaction in the distribution database. MaxBcpThreads must have a value greater than 0 and has no hard-coded upper limit. The default is 2 times the number of processors, up to a maximum value of 8.
NOTE: For Transactional Replication, when applying a snapshot that was generated at the Publisher using the concurrent snapshot option, one thread is used, regardless of the number you specify for MaxBcpThreads.
Hi Chris
How can you confirm if the snapshot that was generated at the Publisher using the concurrent snapshot option? Our Primary DB is 250GB and the snapshot thus far has taken 50gb in 12 hours. Seems slow to me (we have 16 cores). The MaxBcpThreads was set to 1, we have changed this to 8 but have not restarted the agent as we don’t want to lose what we have snapshotted thus far. Is there anything else that we could do to speed up the snapshot?
regards
Sunil