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.
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.
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.