1. I need to upgrade to sp3a on a distributor
that has about 40 publications to various
subscribers. I will be going from sql2k rtm to
sql2k sp3a. Are there any issues as far as
replication goes patching from rtm to sp3a? I
cannot afford to reinitilize any of these
publications.
2. Is there like a file size limit on using the
compress feature of a snapshot? I was able to
compress a 1gb table fine, but when I tried a
10gb one it always fails with a time out.
tia
-comb
btw, Hilary I just ordered your book for
45bucks.. hope it helps.
1. There are not issues that I am aware of. Your best bet is to stop all
agents, upgrade the distributor, then the publisher and then the
subscribers.
It is critical to upgrade the distributor and the publisher simultaneously.
The subscribers can wait.
2. You may need to restore your publication database on your subscriber and
then fix it up (ie NFR on all triggers, constraints, and identity columns).
Then do a nosync subscription and do a validation and try to sync any
discrepancies manually.
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1bf96d8fc0db9a09896dc@.news.newsreader.com ...
> 1. I need to upgrade to sp3a on a distributor
> that has about 40 publications to various
> subscribers. I will be going from sql2k rtm to
> sql2k sp3a. Are there any issues as far as
> replication goes patching from rtm to sp3a? I
> cannot afford to reinitilize any of these
> publications.
> 2. Is there like a file size limit on using the
> compress feature of a snapshot? I was able to
> compress a 1gb table fine, but when I tried a
> 10gb one it always fails with a time out.
> tia
> -comb
> btw, Hilary I just ordered your book for
> 45bucks.. hope it helps.
>
|||In article <eEcwkWfxEHA.748
@.TK2MSFTNGP14.phx.gbl>, hilary.cotter@.gmail.com
says...
Actually everyone is already at sp3a except the
dist. Pub and Sub are already sp3a. So I am
just going to do the distributor. I think that
should go on without a hitch right? I shouldn't
have to do anything to any of the db's on the
servers (pub/sub) that are already sp3a.
Do you have any websites or documentation that
explains how to do step 2. in a step by step
format?
Thanks for the info.
> 1. There are not issues that I am aware of. Your best bet is to stop all
> agents, upgrade the distributor, then the publisher and then the
> subscribers.
> It is critical to upgrade the distributor and the publisher simultaneously.
> The subscribers can wait.
> 2. You may need to restore your publication database on your subscriber and
> then fix it up (ie NFR on all triggers, constraints, and identity columns).
> Then do a nosync subscription and do a validation and try to sync any
> discrepancies manually.
>
> "Combfilter" <adsf@.asdf.com> wrote in message
> news:MPG.1bf96d8fc0db9a09896dc@.news.newsreader.com ...
>
>
Showing posts with label publications. Show all posts
Showing posts with label publications. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
High CPU utilization on Merge Replication with SQL 2005 Mobile
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
High CPU utilization on Merge Replication with SQL 2005 Mobile
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
High CPU utilization on distributor and sp_MSget_repl_commands
Hi,
In SQL 2005 Replication Monitor i was not seeing details for any of the publications on the "Distributor to Subscriber Histroy Tab" so i decided to stop and start synchronisation on this one publication. At this time there were approximayely 20000 undistributed commands. After the stop/start of the distribution agent, i started seeing messages like "x trasactions with x commands were delivered". Then i went and restarted all the other distribution agents using the Replication Monitor.
Has anyone experienced this kind of a behaviour?
The second issue is that our trasnactional replication looked to have caught up but i was supprised find that the distribution server was running at 100%. A profiler trace of the distribution database revealed that sp_MSget_repl_commands procedure was being executed and costing approximately in excess of 400 000 reads, 7000 in CPU cost and 15sec in duration. To me it looked as if sp_MSget_repl_commands has chosen an inefficient execution plan but then realised i couldn't recompile system procedures. I think a stop and start of the SQL instance is the only option i have.
PK
You can't recompile the proc itself, but you can force a recompile by updating the stats on the table. This should in theory trigger a recompile for the proc, and I'm sure it applies to procs in the resource db as well, but don't quote me on it.
Do you know if it was the replication monitor or the distribution agent that was making the proc call you were profiling? In pre-RTM days, there was a performance bug for the call made by the replication monitor, making it very expensive. I assume you're on RTM version of SQL 2005?
Without knowing what the plan looks like for the queries inside the proc, it's hard to say what the problem is.
Subscribe to:
Posts (Atom)