Try to restrict the amount of published data. This can results in good performance benefits, because SQL Server will publish only the amount of data required. This can reduce network traffic and boost the overall replication performance.
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.
Microsoft recommends to set a fixed size for the distribution database. Setting a database to automatically grow results in some performance degradation, therefore you should set a reasonable initial size of the distribution database.
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.
The Snapshot Agent bulk copies data from the Publisher to the Distributor, which results in some performance degradation. So, try to schedule it during CPU idle time and slow production periods.
Try to schedule replication to occur at regular intervals instead of using continuous replication.
These data types require more storage space and processing than other column data types.
For example, instead of replicating a very large number of insert, update and delete statements, you can create stored procedure, which will contain all these statements, and replicate to subscriber only the execution of this stored procedure. This can reduce network traffic and boost the overall replication performance.
This can increase SQL Server performance, because Windows NT will allocate more RAM to SQL Server than to its file cache. To set this option, you can do the following:
1. Double-click the Network icon in Control Panel.
2. Click the Services tab.
3. Click Server to select it, and then click the Properties button.
4. Click Maximize Throughput for Network Applications, and then click OK.
5. Restart the computer.
This option is used to set a minimum amount of memory allocated to SQL Server. Microsoft recommends that the 'min server memory' options be set to at least 16 MB of memory to avoid low memory availability during replication activities, if the server is a remote Distributor or a combined Publisher and Distributor. 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 works when other applications pretend to use all available memory.
This is a new SQL Server 2000 replication feature, which allows you to decrease network traffic by compressing snapshot files.
This can increase the Distributor performance, because Distribution Agent processing will be moved from the Distributor to Subscribers.
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 perform in parallel. To increase the MaxBcpThreads value in the Snapshot Agent profile, you can do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Replication Monitor; then expand the Agents and click the Snapshot Agents folder.
4. Right-click appropriate publication and select Agent Profiles...
5. Click the New Profile button to create the new profile with the appropriate MaxBcpThreads value.
6. Choose the newly created profile.
Note. Do not set this property too high, it can results in some performance degradation, because SQL Server will have to spend extra time managing the extra threads. Increase this property to 2 and continue monitoring.
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:
1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Replication Monitor; then expand the Agents and click the appropriate agent folder.
4. Right-click appropriate publication and select Agent Properties...
5. On the Steps tab, double-click the Run agent step, and then add the -OutputVerboseLevel 0 in the Command text box.
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:
1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Replication Monitor; then expand the Agents and click the appropriate agent folder.
4. Right-click appropriate publication and select Agent Properties...
5. On the Steps tab, double-click the Run agent step, and then add the -HistoryVerboseLevel 1 in the Command text box.
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:
1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Replication Monitor; then expand the Agents and click the Distribution Agents or Merge Agents folder.
4. Right-click appropriate publication and select Agent Properties...
5. On the Steps tab, double-click the subscription agent step, and then add the -UseInprocLoader property in the Command text box.
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.
This parameter specifies the number of transactions to be issued to the Subscriber before a COMMIT statement is issued. The default value is 100.
If you do not use indexes on columns used in filters, then SQL Server must perform a table scan.
Because SQL Server requires more overhead to process the dynamic filters than static filters, for best performance you should use static filters, whenever possible.
No comments:
Post a Comment