Search results matching tags 'Performance' and 'Replication'

Transactional replication and massive data updates

Linchi Shea — Thu, 06 Oct 2011 04:19:00 GMT

SQL Server transactional replication has been a rather solid feature and works well for delivering data to reporting servers (among other things) in near real time. That said, it may not work too well when you need to perform massive updates on a published table, for instance, when you need to archive a large amount of data. It can take a long time for all the changes to be replicated to the subscribers.

For some requirements, a perfect solution for this scenario would be to temporarily disable the transactional replication, perform the large scale data archiving concurrently on both the publisher and all the subscribers, and then resume the replication. That is, this would work very well if you can keep the published table from being modified by any other process for the duration.

But wait! This won’t work because there is no built-in SQL Server feature to disable/enable transactional replication so that it becomes completely transparent to data updates while it’s disabled.

A potential workaround is to remove the replication completely before making massive data changes and then re-create it afterwards. Before I actually started using this approach, it sounded to me like a rather dicey way of getting better performance. A number of things could go horribly wrong to help ruin your otherwise peaceful weekend:

Replication you re-create afterwards may not be identical to what you have removed.
Your table(s) could get out of sync among the servers before you re-create replication.
Coordinating concurrent updates on multiple servers is inherently more complex than just updating the publisher.

But now that I have put this approach in practice and battle tested for many months, I can report that this can be a very effective approach to gaining performance in your archiving process. Here are some of the tips I’d like to share in order to achieve that effectiveness:

You must have a robust mechanism to quiesce all the processes that update your published data. If your data is updated (by a non-archiving process) on the publisher while replication is removed, your subscribers will be out of sync with the publisher and you must re-sync them with the publisher, which defeats the purpose of removing replication in the first place.
Once the data update processes are quiesced, you must verify that the tables are indeed in sync among the publisher and the subscribers. If this fails, there is no point of proceeding further.
You’ll need a program or a script to generate the T-SQL scripts for deleting and creating the replication. I do this with a C# program via RMO, but a Poweshell script will do just fine. The T-SQL scripts need to be generated immediately before you remove the replication so that any change to the replication is captured.

If you are archiving a single table or a few tables, you can just script out the replication setup on the table(s). In many cases, you are archiving a database and you should script out all the replication setup for that database.
Among other things, your replication script generator should take the name of the table or database as an input and automatically find out all the replication setup that needs to be scripted. This ensures that you don’t need to change the script generator as publications and/or subscriptions are being added, dropped, or modified.
You’ll also need a program to execute the generated replication delete scripts and another program to execute the generated replication create scripts. These programs need to be aware of where a given script must be executed. For instance, a script to create the pull subscript needs to be executed on the subscriber. My solution is to the replication script generator record this information. As it goes through each publication and pull subscription to generate scripts, the script generator records the generated T-SQL script locations and the target server names in a table. The programs that execute the generated scripts are driven by this table to find each script and what server on which that script needs to be executed.

Once the tables are verified to be in sync and the replication is removed (by executing the delete scripts), you can start to archive data concurrently on all the servers. This step is best driven by a common list of what need to be archived so that you are guaranteed that archiving will be done on all the server on exactly the same data, and at the end of archiving, you are guaranteed that the tables will still be in sync.
Since archiving is done on separate servers, things can obviously go wrong to crash it on one server but not on the others. Regardless what may go wrong, before you execute the generated T-SQL scripts to re-create the replication, you must verify that:

The archiving processes are indeed done on all the servers, and
The data is still in sync across all the servers

All the subscriptions need to be re-created with no-sync. Otherwise, you have just archived the subscriber for nothing. This means that the replication script generator needs to modify the generated T-SQL scripts so that the value of the @sync_type parameter for the sp_addsubscription procedure is set to ‘none’ regardless of how it is currently set.
To ensure that the re-created replication is the same as the original replication (before it’s removed), you can take a snapshot of the syspublications, sysarticles, and syssubscriptions tables before the replication is removed, and take another snapshot of the same three tables after the replication is re-created. By comparing the before and after replication configurations recorded in these three tables, you can determine whether you have changed any replication setup.
I would also suggest that you run a test change and see it get replicated from the publisher to the subscribers. Nothing gives you more comfy feeling than see it in action and see it works.
The distribution agents should run under the security context of the SQL Agent service account. Otherwise, because the generated scripts don’t include any password text, the script containing sp_addpushsubscription_agent or sp_addpullsubscription_agent will fail and no distribution agent would be created.

As mentioned, this approach has been proven to be effective in practice, and it is also quite maintainable. Still this amounts to regular schema changes with a lot of moving parts. It would be much better that replication can be disabled with a single DDL statement, just like you can disable an index or a constraint.

Performance impact: implicit type conversions

Linchi Shea — Wed, 20 Jul 2011 04:26:00 GMT

Often you see an implicit type conversion or a type mismatch causing a performance issue when it trips up the SQL Server query optimizer. A while back though, I ran into a case where a harmless looking type mismatch caused significant performance degradation when there was no bad plan involved.

So we had a rather straightforward transactional replication setup for a table that included about 30 varchar columns. And when there was a burst of inserts into the table on the publisher, the distribution agent could not keep up with the traffic and a significant latency ensued. Upon investigation, we found that the inserts were distributed to the subscriber via a stored procedure, which was not unusual in itself because that is the default setting when you configure transactional replication using the SQL Server Management Studio (SSMS) GUI interface. The stored procedure could not be simpler:

CREATE PROC sp_MSins_MyTable

@c1 nvarchar(40),

@c2 nvarchar(30),

...

@c30 nvarchar(30)

INSERT MyTable(c1, c2, ..., c30)

SELECT @c1, @c, ..., @c30

This procedure was generated by SSMS when the published article was configured to call a procedure instead of using the INSERT statement for distributing inserts.

What’s odd was that even though the table columns were varchar types, the generated stored procedure above defined the input parameters as nvarchar, thus implicit type conversions must be performed during the proc execution. Not sure about why, but that is how SSMS generates these replication wrapper procs.

Since the type conversion was not involved in any where clause or join condition, there was no issue with a terribly inefficient query plan.

Still, the insert latency at the subscriber was huge and there was no other resource issue that might have helped explaining the poor insert throughput on the subscriber.

Thinking that all these type conversion efforts in each insert might have contributed to the slowdown, we replaced all the nvarchar’s with varchar’s in stored procedure. Voila! The insert performance increased dramatically and the distribution latency disappeared quickly.

All was happy and we moved on.

Yesterday, however, we ran into the same problem because upon recreating the replication over the weekend, we had inadvertently replaced our custom modified stored procedure with an SSMS-generated default proc as shown above, and it was using nvachar’s instead of varchar’s. Upon hearing the distribution latency, we promptly put in the proc with the varchar types and the problem went away almost immediately.

That got me wondering about the exact performance impact of these nvarchar/varchar type conversions. So I ran some tests outside the replication setup with two stored procedures, one with nvarchar for all the input parameters and the other with varchar for all the input parameters (note that the table columns are all varchar’s).

CREATE PROC p_insert_nvarchar

@c1 nvarchar(40),

@c2 nvarchar(30),

...

@c30 nvarchar(30)

INSERT MyTable(c1, c2, ..., c30)

SELECT @c1, @c, ..., @c30

CREATE PROC p_insert_varchar

@c1 varchar(40),

@c2 varchar(30),

...

@c30 varchar(30)

INSERT MyTable(c1, c2, ..., c30)

SELECT @c1, @c, ..., @c30

I put them through a loop to insert 100,000 rows into a truncated MyTable. To my surprise, I did not see much of a difference between the two procs.

At this point, I’m at a loss as to why changing from nvarchar to varchar had such a huge performance impact for the distribution agent, but had almost no impact in my controlled tests.

Thus the search for the explanation continues, and I’ll certainly report back when it’s found. By the way, if you know the reason, please post a comment here.

However, it is clear from this experience that a simple nvarchar/varchar type mismatch can have a huge performance impact even when it does not involve any foul query plan. And if you are experiencing latency on the subscriber side (not on the distributor), this is one more thing you may want to check.

Distribution latency in transactional replication: Is a volume surge the culprit?

Linchi Shea — Fri, 05 Jun 2009 15:25:00 GMT

If you use transactional replication, I have no doubt that from time to time you are asked to explain why there is an increased latency between the publisher and the subscriber. More often than not, this end-to-end latency is caused by latency in distributing the commands from the distribution database to the subscriber. So I’ll focus on the distribution latency in this post.

If you are monitoring the latency of your transaction replication using SQL Server native alert system, you may get an alert such as the following:

SQL Server Alert System: 'Replication Warning: Transactional replication latency (PUBLISHER-publication-SUBSCRIBER-780)' occurred on \\DISTRIBUTOR

DATE/TIME: 6/4/2009 5:03:30 PM

DESCRIPTION: The SQL Server performance counter 'Dist:Delivery Latency' (instance 'PUBLISHER-publication-SUBSCRIBER-780') of object 'SQLServer:Replication Dist.' is now above the threshold of 10000.00 (the current value is 40160.00).

This tells you that the distribution agent, named PUBLISHER-publication-SUBSCRIBER-780, has crossed the latency alert threshold.

Now what?

Well, there can be more root causes for an increase in the distribution latency. A transaction volume increase is one of the most common causes. Hey, it justs longer to pump more commands through the system. So you want to quickly determine if that’s the cause or to rule it out quickly so that you can focus your attention on some other potential causes.

More specifically, you want to determine how many transactions, and most usefully, how many commands are being pumped into the distribution database for the distribution agent to apply to the subscriber. Note that a single update that modifies 100,000 rows—that is, one transaction with 100,000 commands—can easily send the distribution latency through the roof, at least, temporarily.

In order for you to determine if the transaction volume is the culprit and if the problem still persists, you need to be able to answer the following questions concerning the current state of replication and the state of replication just prior to the reported latency:

· What are the replication perfmon counter values related to the distribution agent?

· What are the undistributed vs. distributed command counts?

· What is the pending command count?

· What is the recent traffic pattern of the incoming transactions (e.g. transaction count by minute and transaction count to command count ratio over the past 20 minutes)?

· What is the recent traffic pattern of the incoming commands from the log reader (e.g. command count by minute by article over the past 20 minutes)?

· What are the to-be-distributed command counts by article?

· Is the distribution agent being blocked on the subscriber? Okay, this is not a volume issue, but nevertheless is an important piece of information to review.

You need a script to collect the stats if you want to get to the answers quickly, for instance, while your client is pestering you on the phone for an explanation.

The attached script accepts a distribution agent id—which you can get from the alert—as one of the parameters, and gives you the stats to determine if the latency is caused by a transaction/command volume surge.

A number of things to note about the script.

First, it is a Perl script. But that’s just because I wanted to streamline things a bit, and to be able to have better control on the output and the parameter passing. You can easily extract the SQL statements and put them into a T-SQL script with not much additional work.

Secondly, the script does accept parameters in addition to a distribution agent id. For instance, you can specify how far back in minutes you want the script to collect the stats on the incoming transaction and command traffic patterns. But other than a distribution agent id, all the parameters have default values that you can set in the script to match your environment.

Thirdly, it can be expensive to run some queries against the MSrepl_transactions and MSrepl_commands tables. So for instance, by default the script does not print the sample commands. You can control which query to run or not to run through the script parameters. Look up the comments in the script for details.

Finally, the script currently gets the stats primarily from the distributor. Although I have found this script to be very handy in many occasions, it can be significantly improved. One area I want to improve is to collect more stats from the publisher and the subscriber. For that, I’ll probably convert the script into a C# program because Perl is not good for multi threading and I do want to collect stats from the publisher, the distributor, and the subscriber in parallel from different threads.

If you use the script, I appreciate any feedback you may have.