I have a question for anyone who mas some tips/pointers for optimizing SQL merge replication publications.
The front end web server is running IIS 6.0 on Windows 2003 x86 Server Standard (Server A). The back end database server is running SQL 2000 Standard on Windows 2003 x86 Standard (Server B). The merge replication clients connect via HTTPS over the Internet from a custom C#.NET 2005 application using SQL 2005 Mobile running on Windows Mobile 5.0 (Client).
The publication itself has several filters on it. The entry point uses the user's Windows username to start the filter. Based on the user, it then filters the records in multiple tables. There are 68 articles and 44 filter statements. The filters extend multiple layers deep, in other words they are not all filtering off the HOST_NAME() variable, some tables filter from records in tables that filter from the HOST_NAME() variable. The publication is set to minimize data sent to the clients, and considers a subscription out of date if it has not synced in the last 4 days. All the rowguids are indexed as well.
There are approximately 35 clients actively using the application at any given time. On average, a client will initiate a merge replication 3-4 times per hour from 8am-5pm. Generally, a sync will take between 10 seconds and 2 minutes to complete, with most of them being around 30 seconds on average.
When a client starts a sync, there is a spike to about 50% on the server's CPU graph. If multiple clients attempt to sync at the same time the CPU utilization can be pushed to 100% for extended periods (more than 30 seconds).
I recently completed a project to increase the bandwidth available to the clients, and plan to reduce the number of filters significantly (although this will obviously increase the amount of data going to the clients and the storage needs on the individual devices). I also plan on changing the setting to not minimize the amount of data sent to the clients.
Having said all that, does anyone have any information about how to further optimize merge publications to mobile clients? The next publication will be on SQL 2005 x64 Standard if I can solve the issues in the text environment. I would like to enhance the publication as much as possible to make the end user experience better than it currently is.
Thanks!
You're talking about CPU usage at the publisher, correct?
Can you double check that all columns involved in the merge join filters are indexed as well? If the columns are not indexed, this leads to table scans during syncs which can result in high CPU usage.
Are you also getting conflicts? There's a performance issue (which will be fixed in SP1) that can slow things down due to missing indexes on some conflict tables, but this shouldn't be an issue unless you're getting hundreds and hundreds of conflicts.
ALso, how "deep" are your filters? Do the merge join filters have 1 to many relationships, or many to many (see the @.join_unique_key parameter). The more levels deep you are, or any level that contains a @.join_unique_key = 0, can negatively affect performance.
Regardless, you can always run profiler at the publisher and trace a single subscriber to see which procs are consuming the most time. You can start with RPC:completed or SP:completed, and just grab the duration. You'll quickly see which procs are the problematic one. From there, you can then enable SP:StatmentEnded and enable ExecutionPlan to see exactly what statement and why it's slow.
|||
Indexes were definetly a piece of the puzzle. The @.join_unique_key was also in play, so thanks for putting me on to that one. For anyone else who is using Merge Replication with SQL Mobile, there are 2 very useful articles:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_replmergepartitioned.asp
No comments:
Post a Comment