Tune Replication Performance using PacketSize
Contributed by: Karol Korasadowicz
Depending on type of data being replicated and network throughput adjusting the PacketSize parameter for the Distribution Agent may increase your overall Transactional Replication performance.
- -PacketSize packet_size
Is the packet size, in bytes. The default is 4096 (bytes).
To see which setting has most impact, create test scenario which inserts/updates records then distributes those to the subscriber using PacketSize 4KB (default), 8KB, 12KB, 16KB, and 32KB. Use Distribution Agent OUTPUT Parameter to track detailed performance statistics with each setting. Compare overall timestamps for each step along with overall Agent performance. Note: A higher packet size does requires more memory for the Distribution Agent.
For SQL 2008 see also Navigating Subscription Streams