| Product: | Syniti Replicate, Syniti Data Replication (SDR) |
| Version: | All |
| ID: | 1616 |
| Summary: | Options for improving performance with replications involving MS SQL Server |
Using FastLoad/BulkInsert for Refresh Replications
If you are replicating to Microsoft SQL Server using Refresh mode, you can improve performance by setting the BulkInsert option for your target connection. The .NET Data Provider makes a FastLoad interface available, which makes it possible for Syniti Replicate/SDR to perform bulk inserts during replication.
- In the Target Connection wizard, when creating a target connection, select the Microsoft SQL Server .NET Data Provider.
- In the Replication wizard, when configuring the replication, choose Refresh as the Replication mode.
- Select the replication in the Management Center Metadata Explorer (Enterprise Manager tree).
- From the right mouse button menu, choose Replication Properties.
- In the Preferences tab, set the Insert Mode property to Bulk.
- Adjust the Block Size property (default is 10,000) to a value between 25,000 to 100,000 for best performance.
If the replication is already running, for this change to take effect, you will need to disable, then re-enable the replication.
Allocating Space in SQL Server
In addition to using bulk insert to increase performance, you can allocate the necessary space to store the replicated tables in SQL Server. By setting the allocated space for the database, the continuous DBMS requests for more space for the tables during replication (especially for refresh mode) will be eliminated, thereby saving execution time. In SQL Server Management Studio, open the Object Explorer and connect to the SQL Server Database Engine. View Databases, and right-click on the database to view Properties. Manage the data files to ensure enough space to contain the entire replicated database
Thread Settings
Here are some hints on how to manage performance using settings found in the Syniti Replicate/SDR Management Center Data Replicator Options dialog and Replication Properties dialog.
Data Replicator Options Dialog: Max number of concurrent threads
Increasing this number will boost the performance but the CPU and memory usage will also increase and other applications running on the same PC might be affected.
Data Replicator Options Dialog: Thread Delay
When this value is greater than 0, it represents the number of time slices the thread executes before going into a sleep state. The purpose of this parameter is to release some CPU resources if the CPU usage is too high. Setting this value to 1 instructs Syniti Replicate/SDR to suspend each thread for a fraction of time (Sleep(0)) at every time slice. 1 is the value that releases the maximum of the CPU usage but it's also the value that penalizes performance the most. Setting it to n indicates that a thread will sleep every n slices of time. If the thread delay is 0, the thread is never suspended if there are replications running.
Data Replicator Options Dialog: Thread execution factor
This value is a factor indicating how much time a thread spends processing a task (time slice) before switching to the following task in the execution list. It's basically used to increase the time slice Syniti Replicate/SDR assigns to each thread.
Replication Properties Dialog: Thread Priority
This value affects the time slice which Syniti Replicate/SDR assigns to a thread when the thread is running the current replication. It's similar to the "Thread execution factor", but relative to a specific replication. Increasing the thread priority of the replication might increase the performance because it increases the slice of time which Syniti Replicate/SDR dedicates for processing the replication. However the performance is also affected by the total number of replications running and the "Max number of concurrent threads" value set in the Options dialog.