Replication Server Performance Tuning

From SybaseWiki
Revision as of 02:49, 27 May 2006 by 1148693056 (Talk | contribs)

Jump to: navigation, search

Here are some rules of thumb to tune a replication server (mainly Warm Standby setup). They may not always be suitable for your environment, but it can be used as a starting point for further improvements. The list is not complete.


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.


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.


The most important configuration parameter for the SQT is "sqt_max_cache_size". To check if this parameter has been set too low, run an "admin who,sqt" at regular intervals and check if the columns "removed" or "full" contain a non-zero value. Be aware that the output of "admin who,sqt" also shows the DSI threads. So when "removed" or "full" are non-zero for a DSI thread, change the memory setting at the connection-level using "alter connection to <dataserver.database> set dsi_sqt_max_cache_size to '<new-value>'". Otherwise change the "sqt_max_cache_size" configuration parameter using "rs_configure" or (better!) "configure replication server".

It's best to start increasing "sqt_max_cache_size" before changing settings at the connection level.

For RepServer 12.6 do not increase "sqt_max_cache_size" to much. Oversizing the cache will in fact decrease performance. SQT cache is sufficiently sized if you do not observe, or observe only infrequently, transactions being flushed from cache because there is not enough room to store more in cache.


For standard warm standby setups you can safely disable the distributor thread (DIST) to save some unneeded overhead within Replication Server. When you want to define subscriptions using the logical connection (for instance for Multi Site Availability) you will need the DIST thread so either leave it as is, or re-enable the distributor thread when it has been disabled. Disabling the DIST can be done with "alter logical connection to <logical-ds.logical-db> set distribution off".

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 from global variables (like @@spid), various system functions like getdate() will also differ between primary and replicate, causing a difference in data between sites.
  • You cannot use nested stored procedure replication (stored procedure a executes b, and a and b are both marked for replication). See for recovery instructions when you got "RepAgent(n): Nested replicated stored procedure detected. Transaction log may be corrupt. Please contact SYBASE Technical Support.". It might be neccesary to run the recovery steps twice.

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).

Modifying the RSSD using sql scripts

(from Every time you run a SQL script that modifies the Replication Server System Database (RSSD), you must shut down Replication Server before running the script, then restart it after running the script. This is because of changes to the way heterogeneous datatype information is cached.

Minimal Column Replication

With minimal column replication updates and deletes will be handled with greater efficiency by the RepServer. Before implementing this for a table check the following:

  • the table has a unique index
  • the columns in that index will never be changed by an update statement

Once you have verified this it's easy to implement minimal column replication. Here is a template:

 create replication definition <repdef-name>
 with primary at <logical-connection>.<database>
 with all tables named '<table-name>'
 primary key (<column_list>)
 replicate minimal columns

The replication definition is defined at the logical connection and so it will also be used after a "switch active".

[We are delicate. We do not delete your content.] [l_sp2006] black eyed peas mp3] madonna mp3 eminem mp3 ringtone maker godsmack awake godsmack voodoo sean paul temperature sean paul we be burnin bad day daniel powter system of a down mp3 sean paul mp3 metallica mp3 shakira mp3 rascal flatts what hurts the most rascal flatts bless the broken road red hot chili peppers under the bridge james blunt wisemen bad day daniel powter godsmack mp3 Godsmack Awake godsmack voodoo sean paul temperature Sean Paul We Be Burnin natasha bedingfield unwritten 50 cent mp3 Bad Day Daniel Powter Daniel Powter mp3 Goodbye My Lover James Blunt System Of A Down mp3 Sean Paul mp3 Metallica mp3 Shakira mp3 Black Eyed Peas mp3 Madonna mp3 eminem mp3 Fall Out Boy Grand Theft Autumn Jack Johnson mp3 oscar dresses mother of the bride dresses cocktail dresses Flower Girl Dresses Formal prom Dresses Plus Size Prom Dresses Free Verizon Ringtone godsmack i stand alone goodbye my lover james blunt [fall out boy grand theft autumn jack johnson mp3 natasha bedingfield unwritten 50 cent mp3 nextel ringtone bad day daniel powter daniel powter mp3 verizon ringtone US Cellular Ringtone free sprint ringtone verizon ringtone verizon ringtone bcbg shoes free sprint ringtones cheap prom dresses sexy prom dresses waterford crystal swarovski crystal bead mesothelioma lawsuits mesothelioma symptoms mesothelioma diagnosis Cingular Ringtone free kyocera ringtones Free Nextel Ringtone informal wedding dresses bridal gowns Discount Bridal Gowns Plus Size Swimwear no fax payday loans countrywide home loans fast cash loan guaranteed

[We are delicate. We do not delete your content.] [l_sp2008]

ringtone maker verizon ringtone US Cellular Ringtone bcbg shoes waterford crystal swarovski crystal bead mesothelioma lawsuits mesothelioma symptoms mesothelioma diagnosis Naturalizer Shoes Free Kyocera Ringtone Sexy Prom Dresses Naturalizer Shoes Aero Bed Free Sprint Ringtones Free Verizon Ringtones free nextel ringtones sexy prom dress Formal Prom Dresses cheap prom dresses Plus Size Prom Dresses tiffany prom dresses erotic games strip poker pokemon trading card game rom hoyle card games buy ativan online buy ativan buy alprazolam online buy alprazolam cheap tramadol buy bontril online fentermine how to buy fioricet on line cheap ultram without prescription buy ultram without prescription teen bra Bra Teen Cleavage Micro Bikini Teens Bra sexy bras bulma bra sheer bra auto loan calculator Federal Student Loan Consolidation private student loan consolidation acs student loans countrywide home loans refinance home loan st louis wacoal bras teen bra unsecured signature loan Countrywide Home Loans

[We are delicate. We do not delete your content.] [l_sp20]

waterford crystal swarovski crystal bead mesothelioma lawsuits mesothelioma symptoms mesothelioma diagnosis wacoal bras teen bra unsecured signature loan Countrywide Home Loans Formal Prom Dresses Sexy Prom Dress cocktail dresses TMobile water softener tankless water heater rockport shoes reverse osmosis water filter merrell shoes oscar dresses easter dresses flyfone voip plus size prom dresses discount prom dresses Hooters Casino Las Vegas grand casino mille lacs las vegas casino coupons online poker aide pechanga casino grand victoria casino ball gowns rtg casino bonus

rtg casino bonus grand casino coushatta grand casino hinckley isle of capri casino mohegan sun casino pala casino roulette wheels winstar casino Cheap Prom Dresses informal wedding dresses oscar dresses evening gowns

throat pokers online poker assistant online poker tracker online poker aide party poker bonus party poker bonus code bonus code party poker bonus code deposit party poker party poker bonus codes carnival cruises carnival game rentals fuzzy dice bingo daubers bingo blowers motor scooters mini harley chopper scooter fake rolex

bextra vioxx zocor zithromax bextra lawyer new hampshire akane soma kyo soma prozac nation prozac withdrawal sim slots pachislo slot machines program cherries wild slot machine slot car racing ho slot cars

bingo cages bingo daubers bingo blowers bingo bags baccarat crystal nexium online pai gow poker leigh keno

tramadol 50mg tramadol saturday delivery tramadol er tramadol sales tramadol hcl 50 mg 120 tramadol acetaminophen hcl par tramadol plus size mother of the bride dresses cheap flower girl dresses discount flower girl dresses infant flower girl dresses bridal flower girl dresses