Problems with replicating large batch inserts

I have transactional replication set up between 2 SQL Server 2005 databases on 2 different boxes. Both the log reader and distribution agent run in continuous mode. The distributor is residing on a third SQL Server box. We are having performance issues with replication when there are large batch deletes/inserts happening on the publisher. There is a batch job that runs for about 8-10 hours everyday on the publisher and deletes/inserts thousands of records as part of transactions. The amount of time to replicate all this data on the subscriber is around 13-15 hours which is not acceptable to our user community. While monitoring I found that the distribution database (MSRepl_commands table) at times has millions of records in it which would explain why the latency is so high. Add to it the fact that there are large transactions occuring on the publisher.

I was wondering if anyone has faced similar problem before. Are there any conifguration changes I can make to the replication infrastructure to reduce latency?

Would appreciate your help.

Thanks.

I wasn't sure if the large MSrepl_commands table is the root cause of your performance problem, but there are some parameters you can change to potentially reduce the size of this table. The parameters are @.max_disretention and @.history_retention of sp_adddistributiondb (you can change it use sp_chagnedistributiondb or through SQL Server Management Studio UI Distributor Property page). The default value is 72 and 48 hours each. Be careful when changing those parameters, your subscription may be deactivated if they haven't synced with publisher beyond the retention period. See SQL Server 2005 Books Online topic for sp_adddistributiondb for details.

If disk I/O is your problem, then you could probably change the location of distribution db to a different physical drive than publication db, that may give you some performance boost as well.

There are some parameters of logreader agent and distribution agent job may help you with replication performance, you can experiment with those parameters to see if they help. (see http://msdn2.microsoft.com/en-us/library/ms151223.aspx for details)

Thanks,

Zhiqiang Feng

|||

Do you know if the latency is higher for the logreader agent or the distribution agent? You can experimenet with tracer tokens to figure this out. But for now, let's assume distribution agent is the bottleneck. If this is the case, performance can be any one of these:

- if you have large transaction size, distribution agent will not start replication the transaction until it has completed. FOr example, if your transaction has an update statement that affects 1 million rows, until the transaction is completed, and until the logreader agent has completed replicating the last update, the distribution will not pick up those commands.

- With such high activity, could disk be a bottleneck at the distributor?

- WIth such large transaction size, distribution agent will apply the same large transaction to the subscriber. Could there be blocking at the subscriber? Could subscriber's tempdb be growing?

- Could you benefit from Distribution Agent's -SubscriptionStreams parameter?

- As Zhiqiang mentioned above, do you have too much history laying around?

- In the UI, the tab that shows the latency for logreader and distribution agent - it runs an expensive query to determine latency. Please use it carefully.

- Your batch processing, is it or can it be done inside of a stored procedure? If so, you could definitely benefit from replicating the execution of a stored procedure, have you considered this? THis saves space in the distribution database, and should improve performance.

- check distribution database to see if filegrowth is occurring.

problems with GETDATE()

Hello

I have an application with visualBasic that conects to SQL server 2000 through ODBC.

In sql server 2000 I have a trigger that inserts into an historic database de transaction made in the active database, like this:

CREATE TRIGGER [TRG_UPD_GTECON] ON [dbo].[GTECON]
FOR UPDATE
AS

INSERT INTO HISTORICO.dbo.HISTO_GTECON (GTECONCOD,GTECONNIV,GTECONORD,GTECONPAD,GTECONDES ,GTECONTIP,GTETIPNOD,CODUSUA,FECMODIF,ACCION, FECHAHIST)
SELECT GTECONCOD,GTECONNIV,GTECONORD,GTECONPAD,GTECONDES, GTECONTIP,GTETIPNOD,CODUSUA,FECMODIF , 'M',GETDATE() FROM INSERTED

FECMODIF field is a datetime field and it gives the problems.

I make two updates from my application: one to change one field and another one to change another one. It is made "one after the other", I mean: there is no user time between both but there are two different updates that should have different datetime at FECMODIF field as I use GETDATE() in both UPDATEs. the update id like this:

UPDATE GTECON
SET GTETIPNOD = 'H',
CODUSUA = 'coco',
FECMODIF = GETDATE()
WHERE GTECONCOD = 'A01'
AND GTECONTIP= 'H'

My problem is that when I see the historic database there are two registers of modification ('M') but BOTH HAVE THE SAME FECMODIF DATE!!.

It looks that GETDATE() is not indeterminist. If I debug the program, as there are user time between both updates, there is a difference between dates but when it works quickly It looks that theres no difference for getdate(). My historic is like this:

A01 ESTOMATOLOGICOS 2002-12-05 10:46:58.843 M
A01 ESTOMATOLOGICOS 2002-12-05 10:46:58.843 M

Please, some help or Ideas. It looks that nobody have this problem all over Internet.

Note1: I tried to put at the second update something like this FECMODIF = dateadd(ss,3,getdate()) in order to force the date to be different, but It doesn't work. It gives me the SAME DATETIME.

Note2: Everything is under the same transaction (maybe it helps)

RaulI don't know if you have solved your problem, however I ran the following test on SQL 2000 and 7.0
--create table abc (id int identity(1,1) not null, msg varchar(20), txnTime datetime)
--create table trigabc (id int not null, txnTime datetime)
/*
CREATE TRIGGER trig_test
ON abc
FOR UPDATE
AS
BEGIN
insert trigabc (id,txnTime) select id, getdate() from inserted
END
*/
truncate table abc
truncate table trigabc
go
insert abc (msg,txnTime) select 'First', getdate()
insert abc (msg,txnTime) select 'Second', getdate()
go
begin tran
update abc set msg='First Update', txnTime=getdate() where id = 1
waitfor delay '000:00:03'
update abc set msg='Second Update', txnTime=getdate() where id = 2
commit tran

select * from abc
select * from trigabc


My output on both systems showed a 3 second delay:
id msg txnTime
---- ------- ----------------
1 First Update 2002-12-06 09:55:15.720
2 Second Update 2002-12-06 09:55:18.773

(2 row(s) affected)

id txnTime
---- ----------------
1 2002-12-06 09:55:15.770
2 2002-12-06 09:55:18.773

Does this simulate your transaction process?|||Thank you very much

Your idea is good but I couldn't place it into the trigguers. Instead of this I have place it in program code and it looks to work.

Thank you very much

Raul

Originally posted by achorozy
I don't know if you have solved your problem, however I ran the following test on SQL 2000 and 7.0
--create table abc (id int identity(1,1) not null, msg varchar(20), txnTime datetime)
--create table trigabc (id int not null, txnTime datetime)
/*
CREATE TRIGGER trig_test
ON abc
FOR UPDATE
AS
BEGIN
insert trigabc (id,txnTime) select id, getdate() from inserted
END
*/
truncate table abc
truncate table trigabc
go
insert abc (msg,txnTime) select 'First', getdate()
insert abc (msg,txnTime) select 'Second', getdate()
go
begin tran
update abc set msg='First Update', txnTime=getdate() where id = 1
waitfor delay '000:00:03'
update abc set msg='Second Update', txnTime=getdate() where id = 2
commit tran

select * from abc
select * from trigabc


My output on both systems showed a 3 second delay:
id msg txnTime
---- ------- ----------------
1 First Update 2002-12-06 09:55:15.720
2 Second Update 2002-12-06 09:55:18.773

(2 row(s) affected)

id txnTime
---- ----------------
1 2002-12-06 09:55:15.770
2 2002-12-06 09:55:18.773

Does this simulate your transaction process?

Problems with an INSERT SP

Hi folks
I have a SP that inserts a record into a table. The text is as follows:
''''
'
CREATE PROCEDURE [dbo].[procClientAdd]
@.lastname varchar (50),
@.firstname varchar (50) = NULL,
@.othernames varchar (50) = NULL,
@.gender varchar (6),
@.dob smalldatetime,
@.comment varchar (50) = NULL,
@.note varchar (500) = NULL,
@.addr1 varchar (50)=NULL,
@.addr2 varchar (50) = NULL,
@.suburb int = NULL,
@.state varchar (3) = NULL
AS
INSERT INTO Clients (LastName, FirstName, OtherNames, Gender, DateOfBirth,
Comment, Note, AddressLine1, AddressLine2, SuburbID, StateCode)
VALUES (@.lastname, @.firstname, @.othernames, @.gender, @.dob, @.comment,
@.note, @.addr1, @.addr2, @.suburb, @.state)
'''
When I attempt to execute it within SQL Query Analyser, I get the following
message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.
The EXEC statement is as follows:
EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross',,@.gender =
'Male', @.dob = 'Novermber 1 1952', @.comment = '?',, @.addr1 = '7Bay Road',,
@.suburb = 1, @.state = 'TAS'
I hope somebody can help - I am using Win XP SP2
Kind regards
Ross PetersenEXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross',,@.gender =
'Male', @.dob = 'Novermber 1 1952', @.comment = '?', @.addr1 = '7Bay Road',
@.suburb = 1, @.state = 'TAS'
This is the correct way. You had too many commas after comment and addr1
Yosh
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:706EF6DF-5FD3-45E6-9477-E2AEE35F32F2@.microsoft.com...
> Hi folks
> I have a SP that inserts a record into a table. The text is as follows:
> ''''
'
> CREATE PROCEDURE [dbo].[procClientAdd]
> @.lastname varchar (50),
> @.firstname varchar (50) = NULL,
> @.othernames varchar (50) = NULL,
> @.gender varchar (6),
> @.dob smalldatetime,
> @.comment varchar (50) = NULL,
> @.note varchar (500) = NULL,
> @.addr1 varchar (50)=NULL,
> @.addr2 varchar (50) = NULL,
> @.suburb int = NULL,
> @.state varchar (3) = NULL
> AS
> INSERT INTO Clients (LastName, FirstName, OtherNames, Gender, DateOfBirth,
> Comment, Note, AddressLine1, AddressLine2, SuburbID, StateCode)
> VALUES (@.lastname, @.firstname, @.othernames, @.gender, @.dob, @.comment,
> @.note, @.addr1, @.addr2, @.suburb, @.state)
> '''
> When I attempt to execute it within SQL Query Analyser, I get the
> following
> message:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ','.
> The EXEC statement is as follows:
> EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross',,@.gender =
> 'Male', @.dob = 'Novermber 1 1952', @.comment = '?',, @.addr1 = '7Bay Road',,
> @.suburb = 1, @.state = 'TAS'
> I hope somebody can help - I am using Win XP SP2
> Kind regards
> Ross Petersen|||Hi Yosh
Thanks for the quick reply.
I tried your suggestion & it has helped, but there is a problem still.
the EXEC statement looks like this now:
''
EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross', @.gender =
'Male', @.dob = 'Novermber 1 1952', @.comment = '?', @.addr1 = '7Bay Road',
@.suburb = 1, @.state = 'TAS'
''
I am now getting the following error msg:
'''
Server: Msg 8114, Level 16, State 4, Procedure procClientAdd, Line 0
Error converting data type varchar to smalldatetime.
''''
I have tried a few permutations of the date section, but the same error
occurs. Is there something wrong with how I am specifying the date?
Kind regards
Ross Petersen
"Yosh" wrote:

> EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross',,@.gender =
> 'Male', @.dob = 'Novermber 1 1952', @.comment = '?', @.addr1 = '7Bay Road',
> @.suburb = 1, @.state = 'TAS'
> This is the correct way. You had too many commas after comment and addr1
> Yosh
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:706EF6DF-5FD3-45E6-9477-E2AEE35F32F2@.microsoft.com...
>
>|||Hi,
"November" is misspelled. Try either
@.dob = 'November 1, 1952'
or
@.dob = '1952-11-01'
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab web site - http://www.rlmueller.net
--
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:1F6942C3-B180-4627-BC6A-BAB556F67626@.microsoft.com...
> Hi Yosh
> Thanks for the quick reply.
> I tried your suggestion & it has helped, but there is a problem still.
> the EXEC statement looks like this now:
> ''
> EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross', @.gender =
> 'Male', @.dob = 'Novermber 1 1952', @.comment = '?', @.addr1 = '7Bay Road',
> @.suburb = 1, @.state = 'TAS'
> ''
> I am now getting the following error msg:
> '''
> Server: Msg 8114, Level 16, State 4, Procedure procClientAdd, Line 0
> Error converting data type varchar to smalldatetime.
> ''''
> I have tried a few permutations of the date section, but the same error
> occurs. Is there something wrong with how I am specifying the date?
> Kind regards
> Ross Petersen
>
> "Yosh" wrote:
>
=
Road',
follows:
DateOfBirth,
'Ross',,@.gender =
Road',,|||It is because your date is not valid. Always pass dates like:
declare @.test datetime
set @.test = '1952-11-01'
or
set @.test = '19521101'
which are the standard ways to pass dates. I like the version with dashes,
as it tends to be easier to read.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:1F6942C3-B180-4627-BC6A-BAB556F67626@.microsoft.com...
> Hi Yosh
> Thanks for the quick reply.
> I tried your suggestion & it has helped, but there is a problem still.
> the EXEC statement looks like this now:
> ''
> EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross', @.gender =
> 'Male', @.dob = 'Novermber 1 1952', @.comment = '?', @.addr1 = '7Bay Road',
> @.suburb = 1, @.state = 'TAS'
> ''
> I am now getting the following error msg:
> '''
> Server: Msg 8114, Level 16, State 4, Procedure procClientAdd, Line 0
> Error converting data type varchar to smalldatetime.
> ''''
> I have tried a few permutations of the date section, but the same error
> occurs. Is there something wrong with how I am specifying the date?
> Kind regards
> Ross Petersen
>
> "Yosh" wrote:
>|||Louis,
The format with hyphens is not standard. The two standard formats are
'19521101' and '1952-11-01T00:00:00'
set nocount on
go
set language French
go
declare @.test datetime
set @.test = '1952-11-01'
select month(@.test)
set @.test = '19521101'
select month(@.test)
set @.test = '1952-11-01T00:00:00'
select month(@.test)
go
set language us_english
go
declare @.test datetime
set @.test = '1952-11-01'
select month(@.test)
set @.test = '19521101'
select month(@.test)
set @.test = '1952-11-01T00:00:00'
select month(@.test)
Steve Kass
Drew University
Louis Davidson wrote:

>It is because your date is not valid. Always pass dates like:
>declare @.test datetime
>set @.test = '1952-11-01'
>or
>set @.test = '19521101'
>which are the standard ways to pass dates. I like the version with dashes,
>as it tends to be easier to read.
>|||So with hyphens is not standard, unless it has time attached? I did not
realize that. I had always assumed that the ISO8601 date would default the
time by definition.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23oNancoYFHA.1368@.tk2msftngp13.phx.gbl...
> Louis,
> The format with hyphens is not standard. The two standard formats are
> '19521101' and '1952-11-01T00:00:00'
>
> set nocount on
> go
> set language French
> go
> declare @.test datetime
> set @.test = '1952-11-01'
> select month(@.test)
> set @.test = '19521101'
> select month(@.test)
> set @.test = '1952-11-01T00:00:00'
> select month(@.test)
> go
> set language us_english
> go
> declare @.test datetime
> set @.test = '1952-11-01'
> select month(@.test)
> set @.test = '19521101'
> select month(@.test)
> set @.test = '1952-11-01T00:00:00'
> select month(@.test)
>
> Steve Kass
> Drew University
> Louis Davidson wrote:
>