Improve Snapshot Performance by changing MaxBcpThreads

content provided by: Akbar Farishta, Technical Lead, Microsoft SQL Server Support

I wanted to post an update to Snapshot Agent Performance –MaxBCPThreads blog posting.  We recently encounter a performance problem trying to push a 120gig snapshot down to a subscriber.  The Distribution Agent was taking a long time doing a Bulk Insert on an 80gig table.  Customer was not using “concurrent snapshot” as described in the blog posting above, which is good, but still not getting fast throughput.  Checking the connections we could see 8 SPIDs connected to the subscriber doing BULK INSERT, however this was a beefy server with lots of unused CPU and IO capacity.

Question: How can we take advantage of the extra CPU/IO to push Bulk Insert faster?

Answer: Increase the number of parallel Bulk Insert threads by increasing MaxBcpThreads settings.

Technical Details: When the Snapshot Agent runs it generates multiple BPC out files depending on the number of rows in the table and the Snapshot Agent MaxBcpThreads setting.  For a 80gig table and MaxBcpThreads 8 we got 16 bcp OUT files, each about 5gb.  Once the Snapshot Agent completed, setting the Distribution Agent MaxBcpThreads to 16 would Bulk Insert all 16 in parallel, almost twice the performance.

We still had excess capacity in the Subscriber so we reran with MaxBcpThreads 100.  This generated 143 BCP OUT files, each under 1 gig.  Increasing the MaxBcpThreads setting for the Distribution Agent would Bulk Insert these in parallel.  While MaxBcpThreads 100 may be to high a setting for some servers, experiment to see if you can find a balance that increases Snapshot performance while not over taxing the servers.

Note: Another solution would be to setup the Subscriber using Backup/Restore of the Publisher or to move the data to the subscriber using another technique such as an SSIS package.  See BOL: Initializing a Transactional Subscription Without a Snapshot