http://msdn.microsoft.com/en-us/library/aa237436%28v=sql.80%29.aspx
http://msdn.microsoft.com/en-us/library/ms151762.aspx
1. Most importance is Pull subscription.
Consider Pull or Anonymous Subscriptions
The Distribution or Merge Agent runs on the Distributor for push subscriptions, and on Subscribers for pull or anonymous subscriptions. Using pull or anonymous subscriptions can increase performance by moving Distribution or Merge Agent processing from the Distributor to Subscribers.
You can also offload agent processing by using Remote Agent Activation. Agent processing can be moved to the Subscriber for push subscriptions and to the Distributor for pull subscriptions. Administration of the agent still takes place at the Distributor for push subscriptions and at the Subscriber for pull subscriptions. For more information, see Remote Agent Activation.
Anonymous subscriptions, which are especially useful for Internet applications, do not require that information about the Subscriber be stored in the distribution database at the Distributor for transactional replication and reduces the storage of information about the Subscriber in the publishing database for merge replication. This reduces the resource demands on the Publisher and Distributor because they do not have to maintain information about anonymous Subscribers.
Anonymous subscriptions are a special category of pull subscriptions. In regular pull subscriptions, the Distribution or Merge Agent runs at the Subscriber (thereby reducing the resource demands on the Distributor), but still stores information at the Publisher. When a publication supports anonymous subscriptions, the publication is configured to always have a snapshot ready for new Subscribers.
For transactional replication, this means that every time the Snapshot Agent runs, a new snapshot will be generated. Typically, a snapshot is not generated if there are no new Subscribers waiting for a snapshot or no Subscriber needs to be reinitialized at the time the Snapshot Agent is run. So while anonymous Subscribers can reduce the resource demands at the Distributor, the tradeoff is that a snapshot is generated more often. With merge replication, a new snapshot is always generated when the Snapshot Agent runs regardless of the type of subscriptions supported by the publication.
2. Change default distribution agent setup.
http://msdn.microsoft.com/en-us/library/ms147328.aspx
3. Performance test
http://msdn.microsoft.com/en-us/library/dd263442.aspx
Performance Indicators | Test Scenarios | SQL Server 2005 on Windows Server 2003 (A) | SQL Server 2008 on Windows Server 2008 (B) | Performance Gains or Losses |
[(A-B)/B]*100 | ||||
CPU Utilization (%) | All |
15% |
15% |
0% |
Memory | All |
99% |
99% |
0% |
Push Replication | 1-GB | 226.12 (minutes) | 110.42 (minutes) |
104.78% |
1,000,000 1k character records | ||||
Pull Replication | 1-GB | 174.87 (minutes) | 12.5 (minutes) |
1298.96% |
1,000,000 1k character records | ||||
Linked Server | 10-MB | 107.6 (minutes) | 113.6 (minutes) | –5.28% |
10,000 1k character records | ||||
Push Replication | 112-MB | 247.07 (minutes) | 59.13 (minutes) |
317.84% |
100,000 varbinary (max) records | ||||
Pull Replication Records | 112-MB | 223.18 (minutes) | 1.95 (minutes) |
11345.13% |
100,000 varbinary (max) records | ||||
Snapshot Replication | 11.3-GB 10,100,000 1k records | Not tested | 22.75 (minutes) | Comparison not available |