Difference between revisions of "Replication Server Performance Tuning"

From SybaseWiki
Jump to: navigation, search
m (Replication Stored Procedures)
(Replication Aspects)
Line 11: Line 11:
  
 
How can you tell if a RepAgent cannot keep up with sending the transaction log to the Replication Server?
 
How can you tell if a RepAgent cannot keep up with sending the transaction log to the Replication Server?
 
===Replication Aspects===
 
  
 
====Replicating Stored Procedures====
 
====Replicating Stored Procedures====
 
Using stored procedure replication can greatly improve the performance of the 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.
 
Using stored procedure replication can greatly improve the performance of the 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.
 
Since data at the replicate site is now modified through a stored proc, some side effects can occur.
* Tables using identity columns  
+
* 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 some system functions, will also differ between primary and replicate.
Before you mark stored procedures for replication, check and beware of the following:
+
Usually a stored procedure is marked for replication like this: sp_setrepproc "<procedure name>","function"

Revision as of 18:18, 4 March 2006

Things about Replication Server

Performance Tuning

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.

How can you tell if a RepAgent cannot keep up with sending the transaction log to the Replication Server?

Replicating Stored Procedures

Using stored procedure replication can greatly improve the performance of the 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 some system functions, will also differ between primary and replicate.

Usually a stored procedure is marked for replication like this: sp_setrepproc "<procedure name>","function"