Hi Guys,
I have been analysing our merge replication for the past 2 months with
ongoing performance issues. I have been in constant talks with Microsoft
regarding performance and bug issues within merge replication and have had
numerous fixes and hot fixes set to me.
We have found multiple issues within nested views and revaluation of data.
So the time has come to revaluate some of our database design to resolve
some potential issues.
MS Support sent me this article
(http://www.microsoft.com/technet/pro...n/mergperf.msp
x) relating to performance tuning and optimization and I would have to say
what a superb document it is. However I would like to rebuild our test bed
with automated publisher and subscriber changes to verify bottle neck and
performance. The original people that wrote the where Damian Castro,
Alejandro Miguel, Bren Newman.
What I'm looking for is some automated database scripts for hitting the
publisher server with more that 200 users over a period of time as
subscribers. Does anyone have any idea where I could get theses scripts that
these guys used or would I have to start writing them now.
Any help would be greatly appreciated.
Thanks, Tim.
Capture representative load using profiler. Then modify these statements to
simulate 200 users. Make sure you don't run the script in a batch as this
will not simulate a representative load. You will have to stagger the
statements to reflect the order and frequency they are run in.
Then fire up osql in 200 different sessions to run these scripts.
Note that with merge replication you probably will want to minimize the
number of concurrent sessions.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:%234zgQoG3EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Hi Guys,
> I have been analysing our merge replication for the past 2 months with
> ongoing performance issues. I have been in constant talks with Microsoft
> regarding performance and bug issues within merge replication and have had
> numerous fixes and hot fixes set to me.
> We have found multiple issues within nested views and revaluation of data.
> So the time has come to revaluate some of our database design to resolve
> some potential issues.
> MS Support sent me this article
> (http://www.microsoft.com/technet/pro...n/mergperf.msp
> x) relating to performance tuning and optimization and I would have to say
> what a superb document it is. However I would like to rebuild our test bed
> with automated publisher and subscriber changes to verify bottle neck and
> performance. The original people that wrote the where Damian Castro,
> Alejandro Miguel, Bren Newman.
> What I'm looking for is some automated database scripts for hitting the
> publisher server with more that 200 users over a period of time as
> subscribers. Does anyone have any idea where I could get theses scripts
> that
> these guys used or would I have to start writing them now.
> Any help would be greatly appreciated.
> Thanks, Tim.
>
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Merge replication performance problems
Hi,
I have a merge replication between MSSQL2005 as a server, and SQLExpress
as the clients. The publication contains dynamic filters, ~190 tables and
~300 joins (the longest path in a "join tree" is 12), currently I'm testing
on database that contains ~2GB of data. If I use a precomputed partitions
- it seems to work, but it just kills the performance of the back-end application;
If I don't use precomputed partitions it constantly fails (during subscriber's
synchronization) with "The merge process failed to enumerate changes in articles
with parameterized row filters... Query timeout expired..." error. The problem
is that there's no additional info - so I don't even know what was the problematic
query... Please help
I would consider trying to use static filters if you have a smallish number
of subscribers. You'll need a separate publication for each subscriber but
the performance is much improved. Apart from that you can increase the
querytimeout value as a possibility and use logging if this doesn't work.
Cheers,
Paul Ibison
|||Hello Paul,
Thanks for the answer, I'd like to clarify couple of things:
1. What do you mean by smallish number? less than 10? or less than 100 is
OK too?
2. I've increased the querytimeout for the agent (which didn't help). Can
you please provide me the details for the logging part? With the highest
-HistoryVerboseLevel I still can't see the exact sqls that are executed,
so I have no idea where the timeout error is coming from; and in SQLProfiler
all I see is the calls to sp_setupbelongs procedure...
Thanks again,
Vladimir Kofman
> I would consider trying to use static filters if you have a smallish
> number
> of subscribers. You'll need a separate publication for each subscriber
> but
> the performance is much improved. Apart from that you can increase the
> querytimeout value as a possibility and use logging if this doesn't
> work.
> Cheers,
> Paul Ibison
|||This'll help for logging: http://support.microsoft.com/Default.aspx?id=312292
The reason I mentioned a smallish number of subscribers as a distinction was
purely practical. If you have the time to set up 100 publications with static
filters then I'd definitely recommend this over dynamic filters.
HTH,
Paul Ibison
I have a merge replication between MSSQL2005 as a server, and SQLExpress
as the clients. The publication contains dynamic filters, ~190 tables and
~300 joins (the longest path in a "join tree" is 12), currently I'm testing
on database that contains ~2GB of data. If I use a precomputed partitions
- it seems to work, but it just kills the performance of the back-end application;
If I don't use precomputed partitions it constantly fails (during subscriber's
synchronization) with "The merge process failed to enumerate changes in articles
with parameterized row filters... Query timeout expired..." error. The problem
is that there's no additional info - so I don't even know what was the problematic
query... Please help
I would consider trying to use static filters if you have a smallish number
of subscribers. You'll need a separate publication for each subscriber but
the performance is much improved. Apart from that you can increase the
querytimeout value as a possibility and use logging if this doesn't work.
Cheers,
Paul Ibison
|||Hello Paul,
Thanks for the answer, I'd like to clarify couple of things:
1. What do you mean by smallish number? less than 10? or less than 100 is
OK too?
2. I've increased the querytimeout for the agent (which didn't help). Can
you please provide me the details for the logging part? With the highest
-HistoryVerboseLevel I still can't see the exact sqls that are executed,
so I have no idea where the timeout error is coming from; and in SQLProfiler
all I see is the calls to sp_setupbelongs procedure...
Thanks again,
Vladimir Kofman
> I would consider trying to use static filters if you have a smallish
> number
> of subscribers. You'll need a separate publication for each subscriber
> but
> the performance is much improved. Apart from that you can increase the
> querytimeout value as a possibility and use logging if this doesn't
> work.
> Cheers,
> Paul Ibison
|||This'll help for logging: http://support.microsoft.com/Default.aspx?id=312292
The reason I mentioned a smallish number of subscribers as a distinction was
purely practical. If you have the time to set up 100 publications with static
filters then I'd definitely recommend this over dynamic filters.
HTH,
Paul Ibison
Merge Replication Performance Issues/Trace Files
We have a merge replication configuration with the distributor and subscriber
on one server and the publisher on another. The performance of the merge
slows down more and more as runs. The bandwidth between servers has already
been ruled out because there they run on a Sonet ring and T3 line between
them. We put SQL Profiler trace on the subscriber end of the merge process
using textdata and duration to try to pin point it down to the source of the
problem. However, the Textdata column has unreadable data, for instance
exec([sp_upd_4FAB988B754745CDECFA93F09EC34073]
'8D8EF6A0-8EDA-4C71-934F-0FAE3E79AFFE',
0x000000000000000000000000008007000000000000000000 0000000000000000, 3, 0x00,
296, 0xECFA93F002000000FF,
0xECFA93F001000000ECFA93F001000000ECFA93F001000000 ECFA93F00100000
Does anyone know if there is any way to translate this into the actual query
executed? Otherwise, I don't see how this helps. I got the trace setup
from a Microsoft web site.
This update proc is being run in binary format.
run this query in your subscription database
select name from sysmergearticles where update_proc
='sp_upd_4FAB988B754745CDECFA93F09EC34073'
to determine which table it is updating.
Basically here are the parameters
Rowguid=0x0000000000000000000000000080070000000000 000000000000000000000000,
metadata_type=3,
lineage_old=0x00,
generation=296,
lineage_new=0xECFA93F002000000FF,
colv= 0xECFA93F001000000ECFA93F001000000ECFA93F001000000 ECFA93F00100000
Then there should be a bunch of parameters whose values you have not
supplied me with
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"SandiDBA" <SandiDBA@.discussions.microsoft.com> wrote in message
news:4946584E-0713-4190-BC0A-A35998E63CDA@.microsoft.com...
> We have a merge replication configuration with the distributor and
subscriber
> on one server and the publisher on another. The performance of the
merge
> slows down more and more as runs. The bandwidth between servers has
already
> been ruled out because there they run on a Sonet ring and T3 line between
> them. We put SQL Profiler trace on the subscriber end of the merge
process
> using textdata and duration to try to pin point it down to the source of
the
> problem. However, the Textdata column has unreadable data, for instance
> exec([sp_upd_4FAB988B754745CDECFA93F09EC34073]
> '8D8EF6A0-8EDA-4C71-934F-0FAE3E79AFFE',
> 0x000000000000000000000000008007000000000000000000 0000000000000000, 3,
0x00,
> 296, 0xECFA93F002000000FF,
> 0xECFA93F001000000ECFA93F001000000ECFA93F001000000 ECFA93F00100000
> Does anyone know if there is any way to translate this into the actual
query
> executed? Otherwise, I don't see how this helps. I got the trace
setup
> from a Microsoft web site.
|||Thanks so much, that helps. Do you also know where can I translate the
index being used for the update?
"Hilary Cotter" wrote:
> This update proc is being run in binary format.
> run this query in your subscription database
> select name from sysmergearticles where update_proc
> ='sp_upd_4FAB988B754745CDECFA93F09EC34073'
> to determine which table it is updating.
> Basically here are the parameters
> Rowguid=0x0000000000000000000000000080070000000000 000000000000000000000000,
> metadata_type=3,
> lineage_old=0x00,
> generation=296,
> lineage_new=0xECFA93F002000000FF,
> colv= 0xECFA93F001000000ECFA93F001000000ECFA93F001000000 ECFA93F00100000
> Then there should be a bunch of parameters whose values you have not
> supplied me with
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "SandiDBA" <SandiDBA@.discussions.microsoft.com> wrote in message
> news:4946584E-0713-4190-BC0A-A35998E63CDA@.microsoft.com...
> subscriber
> merge
> already
> process
> the
> 0x00,
> query
> setup
>
>
|||What index?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"SandiDBA" <SandiDBA@.discussions.microsoft.com> wrote in message
news:58CF081D-5141-4F87-B9C9-54ACD0D514AE@.microsoft.com...[vbcol=seagreen]
> Thanks so much, that helps. Do you also know where can I translate the
> index being used for the update?
> "Hilary Cotter" wrote:
Rowguid=0x0000000000000000000000000080070000000000 000000000000000000000000,[vbcol=seagreen]
between[vbcol=seagreen]
of[vbcol=seagreen]
instance[vbcol=seagreen]
|||The update statement must have a where clause in it. I would like to know
what columns the where clause use to find the row to update. Then from
there, determine if an index is being used for optimization.
"Hilary Cotter" wrote:
> This update proc is being run in binary format.
> run this query in your subscription database
> select name from sysmergearticles where update_proc
> ='sp_upd_4FAB988B754745CDECFA93F09EC34073'
> to determine which table it is updating.
> Basically here are the parameters
> Rowguid=0x0000000000000000000000000080070000000000 000000000000000000000000,
> metadata_type=3,
> lineage_old=0x00,
> generation=296,
> lineage_new=0xECFA93F002000000FF,
> colv= 0xECFA93F001000000ECFA93F001000000ECFA93F001000000 ECFA93F00100000
> Then there should be a bunch of parameters whose values you have not
> supplied me with
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "SandiDBA" <SandiDBA@.discussions.microsoft.com> wrote in message
> news:4946584E-0713-4190-BC0A-A35998E63CDA@.microsoft.com...
> subscriber
> merge
> already
> process
> the
> 0x00,
> query
> setup
>
>
|||use the index tuning wizard to help you determine this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"SandiDBA" <SandiDBA@.discussions.microsoft.com> wrote in message
news:732D8E62-ECA5-4D75-B793-6E4F6F0544D6@.microsoft.com...
> The update statement must have a where clause in it. I would like to
know[vbcol=seagreen]
> what columns the where clause use to find the row to update. Then from
> there, determine if an index is being used for optimization.
> "Hilary Cotter" wrote:
Rowguid=0x0000000000000000000000000080070000000000 000000000000000000000000,[vbcol=seagreen]
between[vbcol=seagreen]
of[vbcol=seagreen]
instance[vbcol=seagreen]
on one server and the publisher on another. The performance of the merge
slows down more and more as runs. The bandwidth between servers has already
been ruled out because there they run on a Sonet ring and T3 line between
them. We put SQL Profiler trace on the subscriber end of the merge process
using textdata and duration to try to pin point it down to the source of the
problem. However, the Textdata column has unreadable data, for instance
exec([sp_upd_4FAB988B754745CDECFA93F09EC34073]
'8D8EF6A0-8EDA-4C71-934F-0FAE3E79AFFE',
0x000000000000000000000000008007000000000000000000 0000000000000000, 3, 0x00,
296, 0xECFA93F002000000FF,
0xECFA93F001000000ECFA93F001000000ECFA93F001000000 ECFA93F00100000
Does anyone know if there is any way to translate this into the actual query
executed? Otherwise, I don't see how this helps. I got the trace setup
from a Microsoft web site.
This update proc is being run in binary format.
run this query in your subscription database
select name from sysmergearticles where update_proc
='sp_upd_4FAB988B754745CDECFA93F09EC34073'
to determine which table it is updating.
Basically here are the parameters
Rowguid=0x0000000000000000000000000080070000000000 000000000000000000000000,
metadata_type=3,
lineage_old=0x00,
generation=296,
lineage_new=0xECFA93F002000000FF,
colv= 0xECFA93F001000000ECFA93F001000000ECFA93F001000000 ECFA93F00100000
Then there should be a bunch of parameters whose values you have not
supplied me with
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"SandiDBA" <SandiDBA@.discussions.microsoft.com> wrote in message
news:4946584E-0713-4190-BC0A-A35998E63CDA@.microsoft.com...
> We have a merge replication configuration with the distributor and
subscriber
> on one server and the publisher on another. The performance of the
merge
> slows down more and more as runs. The bandwidth between servers has
already
> been ruled out because there they run on a Sonet ring and T3 line between
> them. We put SQL Profiler trace on the subscriber end of the merge
process
> using textdata and duration to try to pin point it down to the source of
the
> problem. However, the Textdata column has unreadable data, for instance
> exec([sp_upd_4FAB988B754745CDECFA93F09EC34073]
> '8D8EF6A0-8EDA-4C71-934F-0FAE3E79AFFE',
> 0x000000000000000000000000008007000000000000000000 0000000000000000, 3,
0x00,
> 296, 0xECFA93F002000000FF,
> 0xECFA93F001000000ECFA93F001000000ECFA93F001000000 ECFA93F00100000
> Does anyone know if there is any way to translate this into the actual
query
> executed? Otherwise, I don't see how this helps. I got the trace
setup
> from a Microsoft web site.
|||Thanks so much, that helps. Do you also know where can I translate the
index being used for the update?
"Hilary Cotter" wrote:
> This update proc is being run in binary format.
> run this query in your subscription database
> select name from sysmergearticles where update_proc
> ='sp_upd_4FAB988B754745CDECFA93F09EC34073'
> to determine which table it is updating.
> Basically here are the parameters
> Rowguid=0x0000000000000000000000000080070000000000 000000000000000000000000,
> metadata_type=3,
> lineage_old=0x00,
> generation=296,
> lineage_new=0xECFA93F002000000FF,
> colv= 0xECFA93F001000000ECFA93F001000000ECFA93F001000000 ECFA93F00100000
> Then there should be a bunch of parameters whose values you have not
> supplied me with
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "SandiDBA" <SandiDBA@.discussions.microsoft.com> wrote in message
> news:4946584E-0713-4190-BC0A-A35998E63CDA@.microsoft.com...
> subscriber
> merge
> already
> process
> the
> 0x00,
> query
> setup
>
>
|||What index?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"SandiDBA" <SandiDBA@.discussions.microsoft.com> wrote in message
news:58CF081D-5141-4F87-B9C9-54ACD0D514AE@.microsoft.com...[vbcol=seagreen]
> Thanks so much, that helps. Do you also know where can I translate the
> index being used for the update?
> "Hilary Cotter" wrote:
Rowguid=0x0000000000000000000000000080070000000000 000000000000000000000000,[vbcol=seagreen]
between[vbcol=seagreen]
of[vbcol=seagreen]
instance[vbcol=seagreen]
|||The update statement must have a where clause in it. I would like to know
what columns the where clause use to find the row to update. Then from
there, determine if an index is being used for optimization.
"Hilary Cotter" wrote:
> This update proc is being run in binary format.
> run this query in your subscription database
> select name from sysmergearticles where update_proc
> ='sp_upd_4FAB988B754745CDECFA93F09EC34073'
> to determine which table it is updating.
> Basically here are the parameters
> Rowguid=0x0000000000000000000000000080070000000000 000000000000000000000000,
> metadata_type=3,
> lineage_old=0x00,
> generation=296,
> lineage_new=0xECFA93F002000000FF,
> colv= 0xECFA93F001000000ECFA93F001000000ECFA93F001000000 ECFA93F00100000
> Then there should be a bunch of parameters whose values you have not
> supplied me with
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "SandiDBA" <SandiDBA@.discussions.microsoft.com> wrote in message
> news:4946584E-0713-4190-BC0A-A35998E63CDA@.microsoft.com...
> subscriber
> merge
> already
> process
> the
> 0x00,
> query
> setup
>
>
|||use the index tuning wizard to help you determine this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"SandiDBA" <SandiDBA@.discussions.microsoft.com> wrote in message
news:732D8E62-ECA5-4D75-B793-6E4F6F0544D6@.microsoft.com...
> The update statement must have a where clause in it. I would like to
know[vbcol=seagreen]
> what columns the where clause use to find the row to update. Then from
> there, determine if an index is being used for optimization.
> "Hilary Cotter" wrote:
Rowguid=0x0000000000000000000000000080070000000000 000000000000000000000000,[vbcol=seagreen]
between[vbcol=seagreen]
of[vbcol=seagreen]
instance[vbcol=seagreen]
Merge replication performance dropping. Opinions?
I've been tasked with improving our company's merge replication
performance. We are using SQL merge replication and lately we are seeing
a decrease in the performance. Specifically, we are seeing more errors,
retries, latency, and even missing data. I hope someone can provide
insight or share experiences on replicating at this scale.
Our configuration is such:
- One master server replicates to three distribution servers. These
servers are on the same LAN. The replication jobs are scheduled to run
every 10 minutes.
- Each of the three distribution servers replicates to ~15 servers (total
of 45 subscribers). These subscribers are on connections ranging from
64k to 256k. The replication jobs are scheduled to run once an hour
(staggered times). The subscription is a push subscription running at
the distributor. The subscriptions are filtered per location.
- The publication has 200+ articles, although data changes frequently in
only about 20 tables.
- The data being replicated is primarily either inserts at the
subscribers, or common data inserted/updated at the master. We have 18
tables >1M rows and 3 tables >10M rows. Because of row filtering, not
all these rows get replicated to each subscriber.
The errors:
- No errors between master and distributors, but of course they are on
the LAN.
- "The process could not check the existence of generation at the
Subscriber"
- "The process could not connect to Subscriber xxx"
- "The process could not enumerate changes at the Subscriber"
- "The merge process could not apply the replication metadata" (This
error was returned on a "Completed" task)
- "The process could not query row data at the Subscriber"
Other notes/questions:
- I've tried changing all the subscriber profiles to Slow Link, with not
much improvement.
- The replication taks are launched with QueryTimeout=30000. I believe
this to be excessive, but can't be sure.
- Task durations have run as long as 8 hours.
- Since most of the errors are accompanied by "General network error", I
can accept that our WAN sucks. How can I mitigate this risk?
- Querying the MSMerge tables, I am seeing what I think to be outrageous
row counts: 10M rows in MSMerge_contents at the master, 2M rows in
MSMerge_tombstone at the master, 8M rows in MSMerge_errorlineage at the
distributor. Does this indicate anything seriously wrong?
- Database Indexes are defragged once a week.
- I have just modified some triggers to reduce the amount of data being
replicated; a few days will show if it was helpful.
I appreciate any feedback, comments, or suggestions. Thank you!
If your subscribers connect frequently you should try to drop the retention
period to something small, like 4 days.
Also you might want to evaluate how you have created your publication to see
if there are any optimizations you can make. For example do you need
filters? Is Keep partition changes is true?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Anachostic" <anachostic@.remove.700cb.net> wrote in message
news:Xns9950690A3F584anachostic@.66.250.146.128...
> I've been tasked with improving our company's merge replication
> performance. We are using SQL merge replication and lately we are seeing
> a decrease in the performance. Specifically, we are seeing more errors,
> retries, latency, and even missing data. I hope someone can provide
> insight or share experiences on replicating at this scale.
> Our configuration is such:
> - One master server replicates to three distribution servers. These
> servers are on the same LAN. The replication jobs are scheduled to run
> every 10 minutes.
> - Each of the three distribution servers replicates to ~15 servers (total
> of 45 subscribers). These subscribers are on connections ranging from
> 64k to 256k. The replication jobs are scheduled to run once an hour
> (staggered times). The subscription is a push subscription running at
> the distributor. The subscriptions are filtered per location.
> - The publication has 200+ articles, although data changes frequently in
> only about 20 tables.
> - The data being replicated is primarily either inserts at the
> subscribers, or common data inserted/updated at the master. We have 18
> tables >1M rows and 3 tables >10M rows. Because of row filtering, not
> all these rows get replicated to each subscriber.
> The errors:
> - No errors between master and distributors, but of course they are on
> the LAN.
> - "The process could not check the existence of generation at the
> Subscriber"
> - "The process could not connect to Subscriber xxx"
> - "The process could not enumerate changes at the Subscriber"
> - "The merge process could not apply the replication metadata" (This
> error was returned on a "Completed" task)
> - "The process could not query row data at the Subscriber"
> Other notes/questions:
> - I've tried changing all the subscriber profiles to Slow Link, with not
> much improvement.
> - The replication taks are launched with QueryTimeout=30000. I believe
> this to be excessive, but can't be sure.
> - Task durations have run as long as 8 hours.
> - Since most of the errors are accompanied by "General network error", I
> can accept that our WAN sucks. How can I mitigate this risk?
> - Querying the MSMerge tables, I am seeing what I think to be outrageous
> row counts: 10M rows in MSMerge_contents at the master, 2M rows in
> MSMerge_tombstone at the master, 8M rows in MSMerge_errorlineage at the
> distributor. Does this indicate anything seriously wrong?
> - Database Indexes are defragged once a week.
> - I have just modified some triggers to reduce the amount of data being
> replicated; a few days will show if it was helpful.
> I appreciate any feedback, comments, or suggestions. Thank you!
>
|||Thanks for your response.
Regarding retention, is this the "Subscriptions expire and may be
dropped..." in the Publication>Properties>General tab? If so, ours is set
to 60 days. I figure reducing the retention period will reduce the size of
the MSMerge_content tables? And would this change require dropping and
recreating all the subscriptions? That's not an option at the current
time.
I could not find where the "keep partition changes" setting is. The only
reference in BO is with SQL-DMO.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:#QmoFD4rHHA.1296@.TK2MSFTNGP06.phx.gbl:
> If your subscribers connect frequently you should try to drop the
> retention period to something small, like 4 days.
> Also you might want to evaluate how you have created your publication
> to see if there are any optimizations you can make. For example do you
> need filters? Is Keep partition changes is true?
performance. We are using SQL merge replication and lately we are seeing
a decrease in the performance. Specifically, we are seeing more errors,
retries, latency, and even missing data. I hope someone can provide
insight or share experiences on replicating at this scale.
Our configuration is such:
- One master server replicates to three distribution servers. These
servers are on the same LAN. The replication jobs are scheduled to run
every 10 minutes.
- Each of the three distribution servers replicates to ~15 servers (total
of 45 subscribers). These subscribers are on connections ranging from
64k to 256k. The replication jobs are scheduled to run once an hour
(staggered times). The subscription is a push subscription running at
the distributor. The subscriptions are filtered per location.
- The publication has 200+ articles, although data changes frequently in
only about 20 tables.
- The data being replicated is primarily either inserts at the
subscribers, or common data inserted/updated at the master. We have 18
tables >1M rows and 3 tables >10M rows. Because of row filtering, not
all these rows get replicated to each subscriber.
The errors:
- No errors between master and distributors, but of course they are on
the LAN.
- "The process could not check the existence of generation at the
Subscriber"
- "The process could not connect to Subscriber xxx"
- "The process could not enumerate changes at the Subscriber"
- "The merge process could not apply the replication metadata" (This
error was returned on a "Completed" task)
- "The process could not query row data at the Subscriber"
Other notes/questions:
- I've tried changing all the subscriber profiles to Slow Link, with not
much improvement.
- The replication taks are launched with QueryTimeout=30000. I believe
this to be excessive, but can't be sure.
- Task durations have run as long as 8 hours.
- Since most of the errors are accompanied by "General network error", I
can accept that our WAN sucks. How can I mitigate this risk?
- Querying the MSMerge tables, I am seeing what I think to be outrageous
row counts: 10M rows in MSMerge_contents at the master, 2M rows in
MSMerge_tombstone at the master, 8M rows in MSMerge_errorlineage at the
distributor. Does this indicate anything seriously wrong?
- Database Indexes are defragged once a week.
- I have just modified some triggers to reduce the amount of data being
replicated; a few days will show if it was helpful.
I appreciate any feedback, comments, or suggestions. Thank you!
If your subscribers connect frequently you should try to drop the retention
period to something small, like 4 days.
Also you might want to evaluate how you have created your publication to see
if there are any optimizations you can make. For example do you need
filters? Is Keep partition changes is true?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Anachostic" <anachostic@.remove.700cb.net> wrote in message
news:Xns9950690A3F584anachostic@.66.250.146.128...
> I've been tasked with improving our company's merge replication
> performance. We are using SQL merge replication and lately we are seeing
> a decrease in the performance. Specifically, we are seeing more errors,
> retries, latency, and even missing data. I hope someone can provide
> insight or share experiences on replicating at this scale.
> Our configuration is such:
> - One master server replicates to three distribution servers. These
> servers are on the same LAN. The replication jobs are scheduled to run
> every 10 minutes.
> - Each of the three distribution servers replicates to ~15 servers (total
> of 45 subscribers). These subscribers are on connections ranging from
> 64k to 256k. The replication jobs are scheduled to run once an hour
> (staggered times). The subscription is a push subscription running at
> the distributor. The subscriptions are filtered per location.
> - The publication has 200+ articles, although data changes frequently in
> only about 20 tables.
> - The data being replicated is primarily either inserts at the
> subscribers, or common data inserted/updated at the master. We have 18
> tables >1M rows and 3 tables >10M rows. Because of row filtering, not
> all these rows get replicated to each subscriber.
> The errors:
> - No errors between master and distributors, but of course they are on
> the LAN.
> - "The process could not check the existence of generation at the
> Subscriber"
> - "The process could not connect to Subscriber xxx"
> - "The process could not enumerate changes at the Subscriber"
> - "The merge process could not apply the replication metadata" (This
> error was returned on a "Completed" task)
> - "The process could not query row data at the Subscriber"
> Other notes/questions:
> - I've tried changing all the subscriber profiles to Slow Link, with not
> much improvement.
> - The replication taks are launched with QueryTimeout=30000. I believe
> this to be excessive, but can't be sure.
> - Task durations have run as long as 8 hours.
> - Since most of the errors are accompanied by "General network error", I
> can accept that our WAN sucks. How can I mitigate this risk?
> - Querying the MSMerge tables, I am seeing what I think to be outrageous
> row counts: 10M rows in MSMerge_contents at the master, 2M rows in
> MSMerge_tombstone at the master, 8M rows in MSMerge_errorlineage at the
> distributor. Does this indicate anything seriously wrong?
> - Database Indexes are defragged once a week.
> - I have just modified some triggers to reduce the amount of data being
> replicated; a few days will show if it was helpful.
> I appreciate any feedback, comments, or suggestions. Thank you!
>
|||Thanks for your response.
Regarding retention, is this the "Subscriptions expire and may be
dropped..." in the Publication>Properties>General tab? If so, ours is set
to 60 days. I figure reducing the retention period will reduce the size of
the MSMerge_content tables? And would this change require dropping and
recreating all the subscriptions? That's not an option at the current
time.
I could not find where the "keep partition changes" setting is. The only
reference in BO is with SQL-DMO.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:#QmoFD4rHHA.1296@.TK2MSFTNGP06.phx.gbl:
> If your subscribers connect frequently you should try to drop the
> retention period to something small, like 4 days.
> Also you might want to evaluate how you have created your publication
> to see if there are any optimizations you can make. For example do you
> need filters? Is Keep partition changes is true?
Merge Replication on SQL Server 2000 -- too slow?
I'm setting up a Merge replication over a very high bandwidth line, and I'm
running into many performance problems.
1. I'm publishing the data one-way from the Publisher to one Subscriber at
this time. Once the data are synchronized, it's taking forever to push delta
data since the snapshot. I changed to high volume Merge profile, and it's
still way behind. Millions of transactions are waiting at the Publisher.
2. After the initial data synchronization, MSMerge_contents at the
Subscriber contains millions of rows. Why is that? There's no activity at
the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
UPDATE data pushing back to the Publisher from the Subscriber. I don't know
why what needs to be updated? There's no application or query running at the
Subscriber. This scares me.
3. Because there's millions of entries in MSMerge_contents table, add/drop
articles from replication causes major problem. What's the best way to
handle this data? I'm replicating 200+ tables and some tables have 10+
millions rows.
Thanks,
Hung
Hi. if you have to do ONE way replication, go for transactional replication
instead; it's way faster than merge replication.
Since it's merge replication, the subscriber db's msmerge_contents table
would grow as every single dml would be recorded into it and would remain
depending upon the retention period u have specified.
Also schedule to defrag the msmerge_contents, msmerge_gen_history,
msmerge_tombstone tables as they are likely to get large.
"Hung" wrote:
> I'm setting up a Merge replication over a very high bandwidth line, and I'm
> running into many performance problems.
> 1. I'm publishing the data one-way from the Publisher to one Subscriber at
> this time. Once the data are synchronized, it's taking forever to push delta
> data since the snapshot. I changed to high volume Merge profile, and it's
> still way behind. Millions of transactions are waiting at the Publisher.
> 2. After the initial data synchronization, MSMerge_contents at the
> Subscriber contains millions of rows. Why is that? There's no activity at
> the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
> UPDATE data pushing back to the Publisher from the Subscriber. I don't know
> why what needs to be updated? There's no application or query running at the
> Subscriber. This scares me.
> 3. Because there's millions of entries in MSMerge_contents table, add/drop
> articles from replication causes major problem. What's the best way to
> handle this data? I'm replicating 200+ tables and some tables have 10+
> millions rows.
> Thanks,
> Hung
>
>
|||well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
pushing it one-way from Production server only. Are you saying that
MSMerge_Contents table at the Subscriber contains all rows from the intial
snapshot as well? If so, can I clear it after the intial snapshot synch
because data are pushing one-way right now from the Publisher? I don't want
or expect to see data merging back from the Subscriber at this point. The
one time I saw data propagating back from the Subscriber, I was scared at
the least and didn't understand why that would be possible.
After the inital data synching from the snapshot, ongoing data synching just
can't seem to keep up. We have a big pipe open between the two servers.
Another scare I had was that I got duplicated data at the Publisher itself.
I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
For that one day, for every row from TableB, there were two identical rows
in TableC. How could this be possible? If the trigger fired on TableA fired
twice for some reason, data from TableB should already be cleared from the
first trigger fire. Merge Replication trigger doesn't move data from TableB
to TableC. I couldn't explain that behavior, and I'm scare to turn
replication back on now. Any idea?
Thanks,
Hung
"T" <T@.discussions.microsoft.com> wrote in message
news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...[vbcol=seagreen]
> Hi. if you have to do ONE way replication, go for transactional
> replication
> instead; it's way faster than merge replication.
> Since it's merge replication, the subscriber db's msmerge_contents table
> would grow as every single dml would be recorded into it and would remain
> depending upon the retention period u have specified.
> Also schedule to defrag the msmerge_contents, msmerge_gen_history,
> msmerge_tombstone tables as they are likely to get large.
> "Hung" wrote:
|||Hi.
The subscriber's msmerge_contents should not contain data from the snapshot
file, it contains transactions after the snapshot.
data being pushed back from subscriber is likely the case with merge
replication and u can't stop this beahvious.
for second prob., you would be getting duplicate records in TableC if it
were also published and trigger also exist at subscriber for TableA.
When record gets inserted in publisher tableC through TableA delete, same
event will fire at subscriber's TableA and will cause the trigger at
Subscriber also insert into TableC.
now when u replicate changes, publisher TableC record merges with
Subscriber's TableC record and you get two records at both sides.
Disable that trigger at subscriber, you will get TableC updated at
subscriber if it's included in replication.
"Hung" wrote:
> well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
> pushing it one-way from Production server only. Are you saying that
> MSMerge_Contents table at the Subscriber contains all rows from the intial
> snapshot as well? If so, can I clear it after the intial snapshot synch
> because data are pushing one-way right now from the Publisher? I don't want
> or expect to see data merging back from the Subscriber at this point. The
> one time I saw data propagating back from the Subscriber, I was scared at
> the least and didn't understand why that would be possible.
> After the inital data synching from the snapshot, ongoing data synching just
> can't seem to keep up. We have a big pipe open between the two servers.
> Another scare I had was that I got duplicated data at the Publisher itself.
> I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
> For that one day, for every row from TableB, there were two identical rows
> in TableC. How could this be possible? If the trigger fired on TableA fired
> twice for some reason, data from TableB should already be cleared from the
> first trigger fire. Merge Replication trigger doesn't move data from TableB
> to TableC. I couldn't explain that behavior, and I'm scare to turn
> replication back on now. Any idea?
> Thanks,
> Hung
> "T" <T@.discussions.microsoft.com> wrote in message
> news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...
>
>
|||"T" <T@.discussions.microsoft.com> wrote in message
news:0AAC406D-BD4B-4F8C-A034-3C454A22AFA5@.microsoft.com...
> Hi.
> The subscriber's msmerge_contents should not contain data from the
> snapshot
> file, it contains transactions after the snapshot.
> data being pushed back from subscriber is likely the case with merge
> replication and u can't stop this beahvious.
>
I'd expect the same thing that the subscriber's msmerge_contents table
should be empty since there's no activity at the Subscriber. But I do get
millions of rows in the Subscriber's msmerge_contents table after the first
initialization. I should have queried back to the source tables to figure
out where these rows in msmerge_contents belong.
I understand about data pushing back from the Subscriber is part of
Merge Replication. In my case, the Subscriber isn't taking any live traffic
from the web or query, so I don't expect the Subscriber to have any data
change to push back to the Publisher. Because after the initialization, the
Subscriber's msmerge_contents contains millions, those entries probably get
pushed back? Again, I don't understand why subscriber's msmerge_contents
would have data. I've tried to clear and reinitialize many times. The one
time where I allowed the Subscriber to push data back, it had millions
UPDATE to upload and with 100 entries batch, that seemed to be eternity.
> for second prob., you would be getting duplicate records in TableC if it
> were also published and trigger also exist at subscriber for TableA.
> When record gets inserted in publisher tableC through TableA delete, same
> event will fire at subscriber's TableA and will cause the trigger at
> Subscriber also insert into TableC.
> now when u replicate changes, publisher TableC record merges with
> Subscriber's TableC record and you get two records at both sides.
> Disable that trigger at subscriber, you will get TableC updated at
> subscriber if it's included in replication.
This is a logical explanation. I don't remember if I allowed Subscriber
to push data back during this time or not. I probably did. Otherwise, it
shouldn't happen. Disabling trigger at the Subscriber would create problem
when both Publisher and Subscriber are Live at the same time. For example,
there's web orders inserting into the Subscriber when it's in production,
then I do want to subscriber's trigger on TableA to move data from TableB to
TableC and upload all data to the Publisher. So, if I disable Subscriber's
trigger, this would create a problem. I thought Merge Replication are
triggers-aware and wouldn't fire twice? It knows that data are pushing from
the Publisher to the Subscriber, therefore, Subscriber's trigger won't fire?
or I misunderstand it. How do I avoid this without disabling Subscriber's
triggers because both servers might be in Production at the same time.
Thanks.
[vbcol=seagreen]
> "Hung" wrote:
running into many performance problems.
1. I'm publishing the data one-way from the Publisher to one Subscriber at
this time. Once the data are synchronized, it's taking forever to push delta
data since the snapshot. I changed to high volume Merge profile, and it's
still way behind. Millions of transactions are waiting at the Publisher.
2. After the initial data synchronization, MSMerge_contents at the
Subscriber contains millions of rows. Why is that? There's no activity at
the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
UPDATE data pushing back to the Publisher from the Subscriber. I don't know
why what needs to be updated? There's no application or query running at the
Subscriber. This scares me.
3. Because there's millions of entries in MSMerge_contents table, add/drop
articles from replication causes major problem. What's the best way to
handle this data? I'm replicating 200+ tables and some tables have 10+
millions rows.
Thanks,
Hung
Hi. if you have to do ONE way replication, go for transactional replication
instead; it's way faster than merge replication.
Since it's merge replication, the subscriber db's msmerge_contents table
would grow as every single dml would be recorded into it and would remain
depending upon the retention period u have specified.
Also schedule to defrag the msmerge_contents, msmerge_gen_history,
msmerge_tombstone tables as they are likely to get large.
"Hung" wrote:
> I'm setting up a Merge replication over a very high bandwidth line, and I'm
> running into many performance problems.
> 1. I'm publishing the data one-way from the Publisher to one Subscriber at
> this time. Once the data are synchronized, it's taking forever to push delta
> data since the snapshot. I changed to high volume Merge profile, and it's
> still way behind. Millions of transactions are waiting at the Publisher.
> 2. After the initial data synchronization, MSMerge_contents at the
> Subscriber contains millions of rows. Why is that? There's no activity at
> the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
> UPDATE data pushing back to the Publisher from the Subscriber. I don't know
> why what needs to be updated? There's no application or query running at the
> Subscriber. This scares me.
> 3. Because there's millions of entries in MSMerge_contents table, add/drop
> articles from replication causes major problem. What's the best way to
> handle this data? I'm replicating 200+ tables and some tables have 10+
> millions rows.
> Thanks,
> Hung
>
>
|||well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
pushing it one-way from Production server only. Are you saying that
MSMerge_Contents table at the Subscriber contains all rows from the intial
snapshot as well? If so, can I clear it after the intial snapshot synch
because data are pushing one-way right now from the Publisher? I don't want
or expect to see data merging back from the Subscriber at this point. The
one time I saw data propagating back from the Subscriber, I was scared at
the least and didn't understand why that would be possible.
After the inital data synching from the snapshot, ongoing data synching just
can't seem to keep up. We have a big pipe open between the two servers.
Another scare I had was that I got duplicated data at the Publisher itself.
I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
For that one day, for every row from TableB, there were two identical rows
in TableC. How could this be possible? If the trigger fired on TableA fired
twice for some reason, data from TableB should already be cleared from the
first trigger fire. Merge Replication trigger doesn't move data from TableB
to TableC. I couldn't explain that behavior, and I'm scare to turn
replication back on now. Any idea?
Thanks,
Hung
"T" <T@.discussions.microsoft.com> wrote in message
news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...[vbcol=seagreen]
> Hi. if you have to do ONE way replication, go for transactional
> replication
> instead; it's way faster than merge replication.
> Since it's merge replication, the subscriber db's msmerge_contents table
> would grow as every single dml would be recorded into it and would remain
> depending upon the retention period u have specified.
> Also schedule to defrag the msmerge_contents, msmerge_gen_history,
> msmerge_tombstone tables as they are likely to get large.
> "Hung" wrote:
|||Hi.
The subscriber's msmerge_contents should not contain data from the snapshot
file, it contains transactions after the snapshot.
data being pushed back from subscriber is likely the case with merge
replication and u can't stop this beahvious.
for second prob., you would be getting duplicate records in TableC if it
were also published and trigger also exist at subscriber for TableA.
When record gets inserted in publisher tableC through TableA delete, same
event will fire at subscriber's TableA and will cause the trigger at
Subscriber also insert into TableC.
now when u replicate changes, publisher TableC record merges with
Subscriber's TableC record and you get two records at both sides.
Disable that trigger at subscriber, you will get TableC updated at
subscriber if it's included in replication.
"Hung" wrote:
> well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
> pushing it one-way from Production server only. Are you saying that
> MSMerge_Contents table at the Subscriber contains all rows from the intial
> snapshot as well? If so, can I clear it after the intial snapshot synch
> because data are pushing one-way right now from the Publisher? I don't want
> or expect to see data merging back from the Subscriber at this point. The
> one time I saw data propagating back from the Subscriber, I was scared at
> the least and didn't understand why that would be possible.
> After the inital data synching from the snapshot, ongoing data synching just
> can't seem to keep up. We have a big pipe open between the two servers.
> Another scare I had was that I got duplicated data at the Publisher itself.
> I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
> For that one day, for every row from TableB, there were two identical rows
> in TableC. How could this be possible? If the trigger fired on TableA fired
> twice for some reason, data from TableB should already be cleared from the
> first trigger fire. Merge Replication trigger doesn't move data from TableB
> to TableC. I couldn't explain that behavior, and I'm scare to turn
> replication back on now. Any idea?
> Thanks,
> Hung
> "T" <T@.discussions.microsoft.com> wrote in message
> news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...
>
>
|||"T" <T@.discussions.microsoft.com> wrote in message
news:0AAC406D-BD4B-4F8C-A034-3C454A22AFA5@.microsoft.com...
> Hi.
> The subscriber's msmerge_contents should not contain data from the
> snapshot
> file, it contains transactions after the snapshot.
> data being pushed back from subscriber is likely the case with merge
> replication and u can't stop this beahvious.
>
I'd expect the same thing that the subscriber's msmerge_contents table
should be empty since there's no activity at the Subscriber. But I do get
millions of rows in the Subscriber's msmerge_contents table after the first
initialization. I should have queried back to the source tables to figure
out where these rows in msmerge_contents belong.
I understand about data pushing back from the Subscriber is part of
Merge Replication. In my case, the Subscriber isn't taking any live traffic
from the web or query, so I don't expect the Subscriber to have any data
change to push back to the Publisher. Because after the initialization, the
Subscriber's msmerge_contents contains millions, those entries probably get
pushed back? Again, I don't understand why subscriber's msmerge_contents
would have data. I've tried to clear and reinitialize many times. The one
time where I allowed the Subscriber to push data back, it had millions
UPDATE to upload and with 100 entries batch, that seemed to be eternity.
> for second prob., you would be getting duplicate records in TableC if it
> were also published and trigger also exist at subscriber for TableA.
> When record gets inserted in publisher tableC through TableA delete, same
> event will fire at subscriber's TableA and will cause the trigger at
> Subscriber also insert into TableC.
> now when u replicate changes, publisher TableC record merges with
> Subscriber's TableC record and you get two records at both sides.
> Disable that trigger at subscriber, you will get TableC updated at
> subscriber if it's included in replication.
This is a logical explanation. I don't remember if I allowed Subscriber
to push data back during this time or not. I probably did. Otherwise, it
shouldn't happen. Disabling trigger at the Subscriber would create problem
when both Publisher and Subscriber are Live at the same time. For example,
there's web orders inserting into the Subscriber when it's in production,
then I do want to subscriber's trigger on TableA to move data from TableB to
TableC and upload all data to the Publisher. So, if I disable Subscriber's
trigger, this would create a problem. I thought Merge Replication are
triggers-aware and wouldn't fire twice? It knows that data are pushing from
the Publisher to the Subscriber, therefore, Subscriber's trigger won't fire?
or I misunderstand it. How do I avoid this without disabling Subscriber's
triggers because both servers might be in Production at the same time.
Thanks.
[vbcol=seagreen]
> "Hung" wrote:
Subscribe to:
Posts (Atom)