Here are twenty little known tips that you can use to ensure your replication operations are performing in the most efficient manner possible.
1. Avoid publishing unnecessary data.
Try to restrict the amount of published data. This can result in significant
performance benefits as SQL Server will publish only the amount of
data required. At the same time, this can reduce network traffic and boost the overall
replication performance.
2. Place the published database log and distribution database log
on separate disk drives.
Because logging is more write-intensive, it is important that the disk
arrays containing the SQL Server log files have sufficient disk I/O
performance. Separating the logs onto two drives ensures high disk I/O performance.
3. Do not configure the distribution database to expand or shrink automatically.
Microsoft recommends to set a fixed size for the distribution database.
Setting a database to automatically grow results in some performance
degradation; thus, you should set a reasonable initial size of the
distribution database.
4. Place the distribution component of replication on its own dedicated server.
This topology is used for performance reasons when the level of replication
activity increases or the server resources become constrained.
It reduces Publisher loading, but it increases overall network traffic.
This topology requires separate Microsoft SQL Server installations --
one for the Publisher and one for the Distributor.
5. Run the Snapshot Agent as infrequently as possible.
The Snapshot Agent bulk copies data from the Publisher to the Distributor,
which results in some performance degradation. Try to schedule the agent
during CPU idle time and slow production periods to minimize performance loss.
6. Avoid using continuous replication.
If possible, schedule replication to occur at regular intervals instead of
using continuous replication.
7. Avoid replicating text, ntext and image columns.
These data types require more storage space and processing than other
column data types.
8. Replicate the execution of stored procedures when a large number of
rows are affected.
For example, instead of replicating a very large number of insert, update
and delete statements, you can create a stored procedure which contains
all of these statements. Replicate to subscriber only the execution of this
stored procedure. This can reduce network traffic and boost overall
replication performance.
9. Set the "Maximize Throughput for Network Applications" option.
This can increase SQL Server performance as Windows NT will
allocate more RAM to SQL Server than to its file cache.
To set this option, do the following:
- Double-click the Network icon in Control Panel.
- Click the Services tab.
- Click Server to select it, and then click the Properties button.
- Click Maximize Throughput for Network Applications, and then click OK.
- Restart the computer.
10. Specify the 'min server memory' option.
This option is used to set a minimum amount of memory allocated to
SQL Server. If the server is a remote Distributor or a combined Publisher and Distributor, Microsoft recommends that the 'min server memory' option
be set to at least 16 MB of memory to avoid low memory availability during replication activities, .
You can also change these options when SQL Server works on the same
computer with other applications. In this case, the 'min server memory'
option is used to allow SQL Server to work when other applications
attempt to use all available memory.
11. If you work with SQL Server 2000 in a central publisher with remote
distributor topology (when the distribution component of replication
resides on its own dedicated server) and Publisher is connected with
the Distributor over a slow LAN or WAN, consider compressing the
snapshot files.
This is a new SQL Server 2000 replication feature that allows you
to decrease network traffic by compressing snapshot files.
12. Try to enable pull or anonymous subscriptions to increase the
Distributor performance.
This can increase the Distributor performance, because the Distribution Agent
processing will be moved from the Distributor to Subscribers.
13. Increase the MaxBcpThreads property of the Snapshot Agent.
This property specifies the number of bulk copy operations that can be
performed in parallel. By increasing this value, bulk copy operations
can run faster, because they will be performed in parallel.
To increase the MaxBcpThreads value in the Snapshot Agent profile,
you can do the following:
- Run SQL Server Enterprise Manager.
- Expand a server group, then expand a server.
- Expand Replication Monitor, then expand the Agents
and click the Snapshot Agents folder.
- Right-click appropriate publication and select Agent Profiles...
- Click the New Profile button to create the new profile
with the appropriate MaxBcpThreads value.
- Choose the newly created profile.
Note. Do not set this property too high -- it can result in some
performance degradation, because SQL Server will have to spend
extra time managing the extra threads. First increase this property
to 2 and continue monitoring performance.
14. Set the OutputVerboseLevel property of the Distribution Agent, the
Log Reader Agent, the Merge Agent, and the Snapshot Agent to 0.
This property specifies whether the output should be verbose. There are
three available values:
0 - only error messages are printed
1 - all of the progress report messages are printed
2 - all error messages and progress report messages are printed
The default value is 2. You can increase performance by printed only error
messages.
To set the OutputVerboseLevel value to 0, you can do the following:
- Run SQL Server Enterprise Manager.
- Expand a server group, then expand a server.
- Expand Replication Monitor, then expand the Agents
and click the appropriate agent folder.
- Right-click appropriate publication and select Agent Properties...
- On the Steps tab, double-click the Run agent step, and then add
the -OutputVerboseLevel 0 in the Command text box.
15. You can minimize the performance effect of history logging by selecting 1
for the HistoryVerboseLevel property of the Distribution Agent, the Log
Reader Agent, the Merge Agent, and the Snapshot Agent.
This property specifies the amount of history logged during distribution
operation (for a Distribution Agent), during a log reader operation (for
a Log Reader Agent), during a merge operation (for a Merge Agent), or
during a snapshot operation (for a Snapshot Agent).
To set the HistoryVerboseLevel value to 1, you can do the following:
- Run SQL Server Enterprise Manager.
- Expand a server group, then expand a server.
- Expand Replication Monitor, then expand the Agents
and click the appropriate agent folder.
- Right-click appropriate publication and select Agent Properties...
- On the Steps tab, double-click the Run agent step, and then add
the -HistoryVerboseLevel 1 in the Command text box.
16. If you work with SQL Server 2000, consider using the -UseInprocLoader
agent property.
If this option was set, the in-process BULK INSERT command will be used
when applying snapshot files to the Subscriber. You cannot use this
property with character mode bcp, this property cannot be used by OLE DB
or ODBC Subscribers.
To set the UseInprocLoader property, you can do the following:
- Run SQL Server Enterprise Manager.
- Expand a server group, then expand a server.
- Expand Replication Monitor, then expand the Agents
and click the Distribution Agents or Merge Agents folder.
- Right-click appropriate publication and select Agent Properties...
- On the Steps tab, double-click the subscription agent step, and then
add the -UseInprocLoader property in the Command text box.
17. Increase the Log Reader Agent ReadBatchSize parameter.
This parameter specifies the maximum number of transactions read out of
the transaction log of the publishing database. The default value is 500.
This option should be used when a large number of transactions are written
to a publishing database but only a small subset of those are marked for
replication.
18. If you work with transactional replication, increase the Distribution
Agent CommitBatchSize parameter.
This parameter specifies the number of transactions to be issued to the
Subscriber before a COMMIT statement is issued. The default value is 100.
19. Create an index on each of the columns used in the filter's WHERE clause.
If you do not use indexes on the columns used in filters, then SQL Server must
perform a table scan.
20. If you work with merge replication, use static instead of dynamic filters.
Because SQL Server requires more overhead to process the dynamic filters
than static filters, for best performance you should use static filters whenever possible.
»
See All Articles by Columnist Alexander Chigrik
» 
Comment and Contribute
