Chris Skorlinski Microsoft SQL Server Escalation Services
Working with a customer to setup a large subscriber we noticed the Merge Agent was NOT doing a BULK INSERT, but instead was doing row-by-row insert. This had significant overhead for the Merge Agent resulting in longer then expected initial/reinit synchronization times.
Downloaded 33 change(s) in 'Customer' (33 inserts): 33 total Applied the snapshot and merged 33 data change(s) (33 insert(s), 0 update(s), 0 delete(s), 0 conflict(s)).
SQL Server Profiler trace shows Merge Agent inserting rows in a batch using MSmerge_ins_sp_{guid}_BATCH or inserting rows individually using MSmerge_ins_sp_{guid}. Merge Agent was not calling the Bulk Insert command as expected.
Bulk copied data into table 'Customer' (33 rows)
We discovered the customer was using a Dynamic filter based on Host_Name(). To take advantage of BULK INSERT, the Dynamic Snapshot must be:
1) pre-generated by executing the Dynamic Snapshot job. The Dynamic snapshot job appear under SQL Server Agent, Jobs, and start with name “dyn_”.
or
2) Merge Agent allowed to dynamically generate a new snapshot (see “Data Partitions” page on the Publication Properties.
If the Data Partition was pre-created and the dynamic snapshot generated before Merge synchronization the Merge Agent inserted data using Bulk Insert.