Difference between revisions of "Replication Server Performance Tuning"
m (→RepAgent) |
m (→Performance Tuning) |
||
| Line 2: | Line 2: | ||
===Performance Tuning=== | ===Performance Tuning=== | ||
| + | |||
| + | Here are some rules of thumb to tune a replication server. They may not always be suitable for your environment, but it can be used as a starting point for further improvements. | ||
====RepAgent==== | ====RepAgent==== | ||
| Line 8: | Line 10: | ||
* "scan timeout", set to "5" (seconds) (not really a performance booster) | * "scan timeout", set to "5" (seconds) (not really a performance booster) | ||
* "send buffer size", set to "16k" | * "send buffer size", set to "16k" | ||
| − | * Check if "batch ltl" is set to "true" | + | * Check if "batch ltl" is set to "true". |
With the sp_help_rep_agent stored procedure you can see if the RepAgent can keep up with the activity in the transaction log. Output of this procedure (with the "scan" option") shows the current and the end marker. The end marker is the last page/row number of the transaction log, and the current marker is the page/row number currently being scanned by the RepAgent. | With the sp_help_rep_agent stored procedure you can see if the RepAgent can keep up with the activity in the transaction log. Output of this procedure (with the "scan" option") shows the current and the end marker. The end marker is the last page/row number of the transaction log, and the current marker is the page/row number currently being scanned by the RepAgent. | ||
In the output of sp_help_rep_agent the markers are shown like this (example): (2954283,4). The first number is the page number and second one the record number on the page. | In the output of sp_help_rep_agent the markers are shown like this (example): (2954283,4). The first number is the page number and second one the record number on the page. | ||
| + | |||
| + | ====SQM==== | ||
| + | Do not spend a lot of time on this. Just make sure that the stable devices are on raw disk when your operating system supports it. | ||
====Replicating Stored Procedures==== | ====Replicating Stored Procedures==== | ||
Revision as of 22:28, 8 March 2006
Contents
Things about Replication Server
Performance Tuning
Here are some rules of thumb to tune a replication server. They may not always be suitable for your environment, but it can be used as a starting point for further improvements.
RepAgent
Some basic settings need to be changed to get a better througput from the RepAgent to the RepServer. Change these setting with the sp_config_rep_agent stored procedure and do a restart of the RepAgent afterwards.
- "scan batch size", set to "10000"
- "scan timeout", set to "5" (seconds) (not really a performance booster)
- "send buffer size", set to "16k"
- Check if "batch ltl" is set to "true".
With the sp_help_rep_agent stored procedure you can see if the RepAgent can keep up with the activity in the transaction log. Output of this procedure (with the "scan" option") shows the current and the end marker. The end marker is the last page/row number of the transaction log, and the current marker is the page/row number currently being scanned by the RepAgent. In the output of sp_help_rep_agent the markers are shown like this (example): (2954283,4). The first number is the page number and second one the record number on the page.
SQM
Do not spend a lot of time on this. Just make sure that the stable devices are on raw disk when your operating system supports it.
Replicating Stored Procedures
Using stored procedure replication can greatly improve the performance of a replicated environment. Before using this feature check if the tables that are modified through the stored procedure are also marked for replication. For warm standby environments this is usually done with the sp_reptostandby stored procedure. Since data at the replicate site is now modified through a stored proc, some side effects can occur.
- Tables using identity columns will have a different value in their identity column on the primary and replicate site.
- Inserted/Updated values like getdate(), global variables (like @@spid) and the result of some system functions, will also differ between primary and replicate, causing a difference in data between sites.
Check if this applies to your situation before using stored procedure replication.
Usually a stored procedure is marked for replication like this: sp_setrepproc "<procedure name>","function" There is no need to create anything else (like subscribtions or other stuff).
