Replication Performance and the @status parameter in sp_addarticle

When replicating data over a WAN using a Push Subscription in Transactional Replication there is one parameter related to the article’s properties that has a significant impact on the transactions delivery rate, this is the @status parameter defined with sp_addarticle.

The tests I have done (see bellow table) show that if your replication is configured with the @status parameter set to 8 you should gain a noticeable improvement in performance by setting a value of 16 or 24 instead.

To modify the article’s property use sp_changearticle and to to view the article’s properties use sp_helparticle

This is a copy of the @status in sp_addarticle from BOL (Books on Line)

[ @status=] status

Specifies if the article is active and additional options for how changes are propagated. status is tinyint, and can be the | (Bitwise OR) product of one or more of these values.

Value Description

1 Article is active.

8 Includes the column name in INSERT statements.

16 (default) Uses parameterized statements.

24 Includes the column name in INSERT statements and uses parameterized statements.

64  Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

For example, an active article using parameterized statements would have a value of 17 in this column. A value of 0 means that the article is inactive and no additional properties are defined.

The bellow table shows the results of a test I have done over a 160 ms WAN with one server located at NY and the other at TLV.
Both servers are running SQL Server 2005 x64 Enterprise Edition sp2 build 3282 (CU9) on top of VMware virtual machines running Windows Server 2008 Build 6001 Service Pack 1.

Publisher	Subscriber	Type	Status	Transactions	Commands	Sec/Delivered	Trans/Sec
2005	2005	Push	8	20000	20000	Too slow - stopped after 20 minutes	NA
2005	2005	Push	16	20000	20000	215	93
2005	2005	Push	25	20000	20000	210	95

 

To leave a comment: http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/03/30/replication-performance-and-the-status-parameter-in-sp-addarticle.aspx

-Yaniv
===================================

Boost up replication performance with SQL Server 2008

A couple of months a go I came a cross a Microsoft white paper article http://msdn.microsoft.com/en-us/library/dd263442.aspx that talks about a performance improvement that can be gained by using SQL Server 2008 on top of Windows Server 2008 when replicating data over a WAN (Wide Area Network).

The article talks about an improvement done in Windows Server 2008 to the TCP stack. SQL Server 2008 as an application benefits from these changes in the communication package exposed by the operating system.

The boost in performance is mainly derived from the fact that the TCP Receive Window is now defined dynamically as apposed to a fixed value in earlier versions.

To take advantage of this improvement you got to have SQL Server 2008 on top of Windows Server 2008. No other SQL/OS combination will yield the desired results.

I say it is about time and better latter than never. Think about it, a communication channel established on a WAN is necessarily different in nature and characteristic than a communication channel established on a LAN (Local Area Network). So having the TCP Window Size being the same one value across a given server in operating systems prior to srv 2008 does not make sense. Further more, in my experience no one ever dears touch or tune it as the basic assumption always was that touching this value you win at some connections and loose at others. So there is a real trade off here that has to be dealt with extra care.

Being in a position where I manage 5 data centers around the globe I found that article interesting and relevant enough to set up my own environment and perform some tests.

The bellow table shows the results of a test I have done over a 160 ms WAN with one server located at NY and the other at TLV.
Both servers are running SQL Server 2008 x64 Enterprise Edition (2008 RTM 10.0.1600.22) and SQL Server 2005 sp2 build 3282 (CU6) x64 with similar HW on top of VMware virtual machines running Windows Server 2008 Build 6001 Service Pack 1.

The test results point out some important facts:

Push Replication in sql 2008 becomes as fast as Pull in sql 2005

Pull Replication in sql 2008 performs significantly faster than Pull in sql 2005

Version	Type	Transactions	Commands	Sec Delivered	Trans/Sec
2005	Push	10000	10000	110	91
2005	Pull	10000	10000	54	185
2008	Push	10000	10000	57	175
2008	Pull	10000	10000	19	526

If you like to leave a comment to this post pls do so at:

http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/03/09/boost-up-replication-performance-with-sql-server-2008.aspx

-Yaniv

============================================

SQLScripter - new version available for download

SQLScripter Version 1.0.07 is now available for download.

This is a small version that mostly fixes minor issues.

New features:
SQL Server 2008 compatibleAdded the passwords hashed to the Logins script (SQL 2005 only)
Add the possibility to supply a list of database names separated by a semi column to the Database parameter
Add the UG switch

Fixes:Fixed a problem that the zip file was corrupted


Download:
http://www.valinor.co.il/tools-sqlscripter.asp


To leave a comment please follow this link:
http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/03/05/sqlscripter-new-version-available-for-download.aspx


-Yaniv
============================================

Pull Replication vs. Push Replication Part-II

Here is what I learned while working on an MSDN case with Microsoft in order to troubleshoot a serious degrade in performance that we faced following an upgrade from SQL Server 2000 to SQL Server 2005.

The distribution agent goes through the following steps to "move" a piece of data change from the distribution DB to a subscriber.

(1) querying the data change from the distribution DB
(2) moving the data change from distributor side to the subscriber side
(3) applying the data change to the subscriber DB

Step (1) is quite fast. Step (2) is slow in WAN. Step (3) is not fast. The distribution agent has two buffers.

In case of a push subscription, the distribution agent has two parallel tasks: (I) doing (1); (II) doing (2) and (3). At step (1), the data change is stored into a buffer of the distribution agent located at the distributor side.

In case of a pull subscription, the distribution agent has two parallel tasks: (I) doing (1) and (2); (II) doing (3). At step (2), the data change is stored into a buffer of the distribution agent located at the subscriber side.

We know that (2) and (3) are relatively slow. They are parallelized in a pull subscription, but serialized in a push subscription. So generally speaking, a pull subscription can perform better than a push subscription when it comes to WAN (Wide Area Network).


To leave a comment pls follow this link:
http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/03/03/pull-replication-vs-push-replication-part-ii.aspx

-Yaniv
============================================