|
|
|
date: Tue, 8 Jul 2008 04:11:00 -0700,
group: microsoft.public.sqlserver.replication
back
Re: Merge Replication Deadlocks
Thanks for your input so far.
Our data lines are pretty crappy, if we were to pause the merge agents we
might not catchup until the weekend so that's business wise not possible.
We now have paralel upload/download enabled (necesary because of the bad
data lines), with two concurrent agents each. Our idea is to lower the
downloadread values, and also lowering the polling interval (because
otherwise the sync time would increase). Before doing so, I'll make a
meassure system to check the impact of the changes. Any other input?
"Hilary Cotter" wrote:
> PollingInterval will change how long the merge agent waits after processing
> all changes and applying them on the subscriber and publisher. If it
> processes all changes in 1 second it will be waiting for 59 seconds if you
> use the default, if you change it to 32 it will only be waiting 32 seconds.
> You basically have to time your synchronizations to see if this will be a
> factor or not.
>
> While changing the DownloadRead and Write ChangesPerBatch will affect the
> lenght of time locks are held, it will also increase your sync time.
>
> I would your user transactions to verify they are JIT and not holding locks
> too long, also check your indexing to make sure everything is optimal.
> Update statistics and rebuild your indexes if they are fragmented.
>
> A quick check to see where the problem lies is to see if the merge agents
> lock at night when no-one is on the system. For example stop the merge
> agents from running during the day or for some hours so unsync'd changes
> build up, then at night when no-one is on the system, start the agents up
> and see if you still see the locking.
>
> Note that sp_MSMakeGeneration holds application locks which are pretty
> severe, so you might want to evaluate how many concurrent agents you have
> running.
> "Hugo" wrote in message
> news:8787A855-16A0-400E-ACAF-2206BC061991@microsoft.com...
> > On our servers that participate in the same merge replication various
> > deadlocks happen. Below some examples. Iâve done some research but have
> > not
> > come up with a solution so far. What is the best way to prevent these
> > deadlocks from happening?
> >
> > 1) sp_MSupdategenhistory conflicts with sp_MSmakegeneration
> > 2) sp_MSmakegeneration conflicts with sp_MSenumgenerations90
> > 3) sp_MSupdategenhistory conflicts with sp_mergemetadataretentioncleanup
> > 4) sp_MSenumchangesdirect conflicts with user transactions
> > 5) MSmerge_ins_sp_ conflicts with MSmerge_upd_sp
> > 6) MSmerge_upd_sp conflicts with user transactions
> >
> > I am guessing I can lower the amount of deadlocks of example (2) by
> > changing
> > the Merge Agent Profile âPollingInterval parameter value from 32 (current)
> > to
> > 60 (default) or even higher, but I am also guessing deadlocks wonât
> > disappear
> > this way.
> >
> > Also I read here
> > (http://www.eggheadcafe.com/forumarchives/SQLServerreplication/Mar2006/post26059633.asp)
> > to change the DownloadReadChangesPerBatch parameter (lower it from 100 to
> > 25). Do I need to change the DownloadWriteChangesPerBatch also to get
> > better
> > results? What are the downsides changing those parameters?
> >
> > Are there other things I can tune or change?
> >
> > Hugo
> >
>
date: Tue, 8 Jul 2008 08:02:07 -0700
author: Hugo
Re: Merge Replication Deadlocks
How long do your sync's take. Polling interval is really only relevant for
the contiuous option, it appears that you agents run continuously.
"Hugo" wrote in message
news:ACF441D9-99C0-41B2-94F4-E1DFA75F02D3@microsoft.com...
> Thanks for your input so far.
>
> Our data lines are pretty crappy, if we were to pause the merge agents we
> might not catchup until the weekend so that's business wise not possible.
>
> We now have paralel upload/download enabled (necesary because of the bad
> data lines), with two concurrent agents each. Our idea is to lower the
> downloadread values, and also lowering the polling interval (because
> otherwise the sync time would increase). Before doing so, I'll make a
> meassure system to check the impact of the changes. Any other input?
>
>
>
> "Hilary Cotter" wrote:
>
>> PollingInterval will change how long the merge agent waits after
>> processing
>> all changes and applying them on the subscriber and publisher. If it
>> processes all changes in 1 second it will be waiting for 59 seconds if
>> you
>> use the default, if you change it to 32 it will only be waiting 32
>> seconds.
>> You basically have to time your synchronizations to see if this will be a
>> factor or not.
>>
>> While changing the DownloadRead and Write ChangesPerBatch will affect the
>> lenght of time locks are held, it will also increase your sync time.
>>
>> I would your user transactions to verify they are JIT and not holding
>> locks
>> too long, also check your indexing to make sure everything is optimal.
>> Update statistics and rebuild your indexes if they are fragmented.
>>
>> A quick check to see where the problem lies is to see if the merge agents
>> lock at night when no-one is on the system. For example stop the merge
>> agents from running during the day or for some hours so unsync'd changes
>> build up, then at night when no-one is on the system, start the agents up
>> and see if you still see the locking.
>>
>> Note that sp_MSMakeGeneration holds application locks which are pretty
>> severe, so you might want to evaluate how many concurrent agents you have
>> running.
>> "Hugo" wrote in message
>> news:8787A855-16A0-400E-ACAF-2206BC061991@microsoft.com...
>> > On our servers that participate in the same merge replication various
>> > deadlocks happen. Below some examples. Iâve done some research but have
>> > not
>> > come up with a solution so far. What is the best way to prevent these
>> > deadlocks from happening?
>> >
>> > 1) sp_MSupdategenhistory conflicts with sp_MSmakegeneration
>> > 2) sp_MSmakegeneration conflicts with sp_MSenumgenerations90
>> > 3) sp_MSupdategenhistory conflicts with
>> > sp_mergemetadataretentioncleanup
>> > 4) sp_MSenumchangesdirect conflicts with user transactions
>> > 5) MSmerge_ins_sp_ conflicts with MSmerge_upd_sp
>> > 6) MSmerge_upd_sp conflicts with user transactions
>> >
>> > I am guessing I can lower the amount of deadlocks of example (2) by
>> > changing
>> > the Merge Agent Profile âPollingInterval parameter value from 32
>> > (current)
>> > to
>> > 60 (default) or even higher, but I am also guessing deadlocks wonât
>> > disappear
>> > this way.
>> >
>> > Also I read here
>> > (http://www.eggheadcafe.com/forumarchives/SQLServerreplication/Mar2006/post26059633.asp)
>> > to change the DownloadReadChangesPerBatch parameter (lower it from 100
>> > to
>> > 25). Do I need to change the DownloadWriteChangesPerBatch also to get
>> > better
>> > results? What are the downsides changing those parameters?
>> >
>> > Are there other things I can tune or change?
>> >
>> > Hugo
>> >
>>
date: Tue, 8 Jul 2008 11:47:52 -0400
author: Hilary Cotter
Re: Merge Replication Deadlocks
Yesterday one server which was still in the merge replication did not have
any users working on it, and only 2 deadlocks did happen, both
sp_MSupdategenhistory conflicts with sp_MSmakegeneration.
Does this 'proof' the user transactions are causing the deadlocks? The
programmers might as easily argue that the replication is buggy? Any advice
on how to handle this further?
"Hilary Cotter" wrote:
> How long do your sync's take. Polling interval is really only relevant for
> the contiuous option, it appears that you agents run continuously.
>
> "Hugo" wrote in message
> news:ACF441D9-99C0-41B2-94F4-E1DFA75F02D3@microsoft.com...
> > Thanks for your input so far.
> >
> > Our data lines are pretty crappy, if we were to pause the merge agents we
> > might not catchup until the weekend so that's business wise not possible.
> >
> > We now have paralel upload/download enabled (necesary because of the bad
> > data lines), with two concurrent agents each. Our idea is to lower the
> > downloadread values, and also lowering the polling interval (because
> > otherwise the sync time would increase). Before doing so, I'll make a
> > meassure system to check the impact of the changes. Any other input?
> >
> >
> >
> > "Hilary Cotter" wrote:
> >
> >> PollingInterval will change how long the merge agent waits after
> >> processing
> >> all changes and applying them on the subscriber and publisher. If it
> >> processes all changes in 1 second it will be waiting for 59 seconds if
> >> you
> >> use the default, if you change it to 32 it will only be waiting 32
> >> seconds.
> >> You basically have to time your synchronizations to see if this will be a
> >> factor or not.
> >>
> >> While changing the DownloadRead and Write ChangesPerBatch will affect the
> >> lenght of time locks are held, it will also increase your sync time.
> >>
> >> I would your user transactions to verify they are JIT and not holding
> >> locks
> >> too long, also check your indexing to make sure everything is optimal.
> >> Update statistics and rebuild your indexes if they are fragmented.
> >>
> >> A quick check to see where the problem lies is to see if the merge agents
> >> lock at night when no-one is on the system. For example stop the merge
> >> agents from running during the day or for some hours so unsync'd changes
> >> build up, then at night when no-one is on the system, start the agents up
> >> and see if you still see the locking.
> >>
> >> Note that sp_MSMakeGeneration holds application locks which are pretty
> >> severe, so you might want to evaluate how many concurrent agents you have
> >> running.
> >> "Hugo" wrote in message
> >> news:8787A855-16A0-400E-ACAF-2206BC061991@microsoft.com...
> >> > On our servers that participate in the same merge replication various
> >> > deadlocks happen. Below some examples. Iâve done some research but have
> >> > not
> >> > come up with a solution so far. What is the best way to prevent these
> >> > deadlocks from happening?
> >> >
> >> > 1) sp_MSupdategenhistory conflicts with sp_MSmakegeneration
> >> > 2) sp_MSmakegeneration conflicts with sp_MSenumgenerations90
> >> > 3) sp_MSupdategenhistory conflicts with
> >> > sp_mergemetadataretentioncleanup
> >> > 4) sp_MSenumchangesdirect conflicts with user transactions
> >> > 5) MSmerge_ins_sp_ conflicts with MSmerge_upd_sp
> >> > 6) MSmerge_upd_sp conflicts with user transactions
> >> >
> >> > I am guessing I can lower the amount of deadlocks of example (2) by
> >> > changing
> >> > the Merge Agent Profile âPollingInterval parameter value from 32
> >> > (current)
> >> > to
> >> > 60 (default) or even higher, but I am also guessing deadlocks wonât
> >> > disappear
> >> > this way.
> >> >
> >> > Also I read here
> >> > (http://www.eggheadcafe.com/forumarchives/SQLServerreplication/Mar2006/post26059633.asp)
> >> > to change the DownloadReadChangesPerBatch parameter (lower it from 100
> >> > to
> >> > 25). Do I need to change the DownloadWriteChangesPerBatch also to get
> >> > better
> >> > results? What are the downsides changing those parameters?
> >> >
> >> > Are there other things I can tune or change?
> >> >
> >> > Hugo
> >> >
> >>
>
date: Tue, 15 Jul 2008 00:04:09 -0700
author: Hugo
Re: Merge Replication Deadlocks
Hi,
No, I do not have a solution for this yet. If you do, please share it. I'll
do the same.
Best regards,
"Amy2006" wrote:
> Hugo,
> Did you get a solution to this deadlocking issue?
> We are getting the same type of deadlocking in the MSmerge_genhistory table.
> There are 100 - 200 Subscribers trying to sync every min. and this is
> causing much deadlocking.
> I am looking at increasing the sync interval but this is not desirable.
>
> Any help would be great.
> Thanks.
>
>
> 2008-09-25 10:09:31.44 spid4s Deadlock encountered .... Printing
> deadlock information
> 2008-09-25 10:09:31.44 spid4s Wait-for graph
> 2008-09-25 10:09:31.44 spid4s
> 2008-09-25 10:09:31.44 spid4s Node:1
>
> 2008-09-25 10:09:31.44 spid4s KEY: 9:72057597064445952 (0800fb47ae3f)
> CleanCnt:3 Mode:X Flags: 0x0
> 2008-09-25 10:09:31.44 spid4s Grant List 1:
> 2008-09-25 10:09:31.44 spid4s Owner:0x368EBE40 Mode: X
> Flg:0x0 Ref:1 Life:02000000 SPID:135 ECID:0 XactLockInfo: 0x3F67A97C
> 2008-09-25 10:09:31.44 spid4s SPID: 135 ECID: 0 Statement Type:
> UPDATE Line #: 62
> 2008-09-25 10:09:31.44 spid4s Input Buf: RPC Event: Proc [Database
> Id = 32767 Object Id = 793474348]
> 2008-09-25 10:09:31.44 spid4s Requested By:
> 2008-09-25 10:09:31.44 spid4s ResType:LockOwner
> Stype:'OR'Xdes:0x06635608 Mode: U SPID:119 BatchID:0 ECID:0
> TaskProxy:(0x17166378) Value:0x369f3500 Cost:(5/196)
> 2008-09-25 10:09:31.44 spid4s
> 2008-09-25 10:09:31.44 spid4s Node:2
>
> 2008-09-25 10:09:31.44 spid4s KEY: 9:72057597064577024 (1000078f79b2)
> CleanCnt:2 Mode:U Flags: 0x0
> 2008-09-25 10:09:31.44 spid4s Grant List 1:
> 2008-09-25 10:09:31.44 spid4s Owner:0x3E8EF760 Mode: U
> Flg:0x0 Ref:1 Life:00000001 SPID:119 ECID:0 XactLockInfo: 0x0663562C
> 2008-09-25 10:09:31.44 spid4s SPID: 119 ECID: 0 Statement Type:
> UPDATE Line #: 163
> 2008-09-25 10:09:31.44 spid4s Input Buf: RPC Event: Proc [Database
> Id = 32767 Object Id = 103774808]
> 2008-09-25 10:09:31.44 spid4s Requested By:
> 2008-09-25 10:09:31.44 spid4s ResType:LockOwner
> Stype:'OR'Xdes:0x3F67A958 Mode: X SPID:135 BatchID:0 ECID:0
> TaskProxy:(0x5CF4A378) Value:0x41b57ae0 Cost:(0/356)
> 2008-09-25 10:09:31.44 spid4s
> 2008-09-25 10:09:31.44 spid4s Victim Resource Owner:
> 2008-09-25 10:09:31.44 spid4s ResType:LockOwner
> Stype:'OR'Xdes:0x06635608 Mode: U SPID:119 BatchID:0 ECID:0
> TaskProxy:(0x17166378) Value:0x369f3500 Cost:(5/196)
> 2008-09-25 10:09:31.44 spid18s deadlock-list
> 2008-09-25 10:09:31.44 spid18s deadlock victim=process92e4d8
> 2008-09-25 10:09:31.44 spid18s process-list
> 2008-09-25 10:09:31.44 spid18s process id=process92e4d8
> taskpriority=5 logused=196 waitresource=KEY: 9:72057597064445952
> (0800fb47ae3f) waittime=4328 ownerId=194624139
> transactionname=user_transaction lasttranstarted=2008-09-25T10:09:27.110
> XDES=0x6635608 lockMode=U schedulerid=3 kpid=228 status=suspended spid=119
> sbid=0 ecid=0 priority=-5 transcount=2
> lastbatchstarted=2008-09-25T10:09:27.080
> lastbatchcompleted=2008-09-25T10:09:27.080
> clientapp={CD292107-6272-4D88-A809-D44434303F0A} hostname=975 hostpid=7040
> loginname=APINT\apiRDM isolationlevel=read committed (2) xactid=194624139
> currentdb=9 lockTimeout=4294967295 clientoption1=673384544
> clientoption2=128024
> 2008-09-25 10:09:31.44 spid18s executionStack
> 2008-09-25 10:09:31.44 spid18s frame
> procname=mssqlsystemresource.sys.sp_MSmakegeneration line=163 stmtstart=10828
> stmtend=11958 sqlhandle=0x0300ff7f587a2f0639854d014e9a00000100000000000000
> 2008-09-25 10:09:31.44 spid18s update dbo.MSmerge_genhistory with
> (rowlock)
> 2008-09-25 10:09:31.44 spid18s set genstatus = 3,
> 2008-09-25 10:09:31.44 spid18s coldate = getdate(),
> 2008-09-25 10:09:31.44 spid18s nicknames = @nickbin
> 2008-09-25 10:09:31.44 spid18s where generation <=
> @current_max_gen and genstatus = 4 and
> 2008-09-25 10:09:31.44 spid18s coldate not in (select
> login_time from sys.dm_exec_sessions) and
> 2008-09-25 10:09:31.44 spid18s subscriber_number not in
> (select s.subscriber_number from dbo.sysmergesubscriptions s
> 2008-09-25 10:09:31.44 spid18s
> inner join sys.dm_exec_sessions p on p.program_name = s.application_name
> collate database_default)
> 2008-09-25 10:09:31.44 spid18s inputbuf
> 2008-09-25 10:09:31.44 spid18s Proc [Database Id = 32767 Object Id =
> 103774808]
> 2008-09-25 10:09:31.44 spid18s process id=processbd46b8
> taskpriority=0 logused=356 waitresource=KEY: 9:72057597064577024
> (1000078f79b2) waittime=4328 ownerId=194624135
> transactionname=user_transaction lasttranstarted=2008-09-25T10:09:27.110
> XDES=0x3f67a958 lockMode=X schedulerid=4 kpid=2676 status=suspended spid=135
> sbid=0 ecid=0 priority=0 transcount=2
> lastbatchstarted=2008-09-25T10:09:27.110
> lastbatchcompleted=2008-09-25T10:09:27.017
> clientapp={032973B3-0D8D-4CD1-801B-6DCD76765F04} hostname=1004 hostpid=7040
> loginname=APINT\apiRDM isolationlevel=read committed (2) xactid=194624135
> currentdb=9 lockTimeout=4294967295 clientoption1=536938592
> clientoption2=128024
> 2008-09-25 10:09:31.44 spid18s executionStack
> 2008-09-25 10:09:31.44 spid18s frame
> procname=mssqlsystemresource.sys.sp_MSupdategenhistory line=62 stmtstart=3390
> stmtend=4004 sqlhandle=0x0300ff7f2c754b2fda1c4d014e9a00000100000000000000
> 2008-09-25 10:09:31.44 spid18s update dbo.MSmerge_genhistory with
> (rowlock)
> 2008-09-25 10:09:31.44 spid18s set genstatus = 2,
> 2008-09-25 10:09:31.44 spid18s art_nick = case when
> isnull(@art_nick,0) <> 0 then @art_nick else art_nick end,
> 2008-09-25 10:09:31.44 spid18s coldate= getdate(),
> 2008-09-25 10:09:31.44 spid18s changecount = @changecount
> 2008-09-25 10:09:31.44 spid18s where generation = @gen -- and
> guidsrc = @guidsrc
> 2008-09-25 10:09:31.44 spid18s inputbuf
> 2008-09-25 10:09:31.44 spid18s Proc [Database Id = 32767 Object Id =
> 793474348]
> 2008-09-25 10:09:31.44 spid18s resource-list
> 2008-09-25 10:09:31.44 spid18s keylock hobtid=72057597064445952
> dbid=9 objectname=Prod_database.dbo.MSmerge_genhistory
> indexname=c1MSmerge_genhistory id=lock1f46e0c0 mode=X
> associatedObjectId=72057597064445952
> 2008-09-25 10:09:31.44 spid18s owner-list
> 2008-09-25 10:09:31.44 spid18s owner id=processbd46b8 mode=X
> 2008-09-25 10:09:31.44 spid18s waiter-list
> 2008-09-25 10:09:31.44 spid18s waiter id=process92e4d8 mode=U
> requestType=wait
> 2008-09-25 10:09:31.44 spid18s keylock hobtid=72057597064577024
> dbid=9 objectname=Prod_database.dbo.MSmerge_genhistory
> indexname=nc2MSmerge_genhistory id=lock3d3c3400 mode=U
> associatedObjectId=72057597064577024
> 2008-09-25 10:09:31.44 spid18s owner-list
> 2008-09-25 10:09:31.44 spid18s owner id=process92e4d8 mode=U
> 2008-09-25 10:09:31.44 spid18s waiter-list
> 2008-09-25 10:09:31.44 spid18s waiter id=processbd46b8 mode=X
> requestType=wait
>
date: Wed, 1 Oct 2008 06:11:05 -0700
author: Hugo
|
|
