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