November 18, 2009 at 12:22 pm
We have been seeing this problem off and on with one of our servers.
Our replication infrastructure is rather involved.
We have 16 merge replication subscribers (SQL 2000 - SP4) with one of those subscribers also a subscriber to many transactional replications from a combination of SQL 2000 and SQL 2005 servers.
I did some CPU utilization investigations a few months back and noticed many of our newest (SQL 2005) transactional publications (at the subscriber) were using more CPU cycles then our most busy merge replication subscriptions.
We ran some tracing and discovered something rather odd. The statements coming from SQL 2005 were using nvarchar (N'XX') characters which resulted in the SQL 2000 subscriber not using either the clustered or non-clustered indexes.
After rebuilding the publication, our CPU issues seemed to be much better, but our update/insert/delete options on the sp_addarticle calls were using stored procedures instead of the native SQL setting.
We again rebuilt our SQL 2005 publications using the settings: @ins_cmd='SQL', @upd_cmd='SQL', @del_cmd='SQL' and now our CPU usage is again 26 times higher then our most busy merge job.
From this, I concluded that the use of the stored procedures for the @ins_cmd, @upd_cmd, @del_cmd must have caused implicit conversions from nvarchar to varchar which allowed the statements to use both the clustered and non-clustered indexes.
I plan on rebuilding one of these publications later tonight using the stored procedure update rather then the native SQL.
From the collective knowledge on this site, is this a reasonable plan or did I miss something.
Thanks!
November 19, 2009 at 10:30 am
I rebuilt our replication and got my answer.
Our source tables for these replications have compound keys using character data with only a few indexes. The underlying data is getting updated constantly which compounds the problem.
When I removed the @ins_cmd, @upd_cmd and @del_cmd = 'SQL' option and had the stored procedures generated for me, one of my performance issues was resolved.
This is a potential performance pitfall for anyone using SQL 2005 to SQL 2000 transactional replication.
For an idea of scope, we had one publication that was created over the weekend that had used almost 200 million CPU ticks in SQL. That one is gone, but it has been passed by another that is over 205 million and has been synching for about six days with no end in site.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply