June 28, 2006 at 9:12 pm
The client production server CPU starts thrashing. Task manager indicates that SQL server is gobbling CPU cycles. Having a look at the replication monitor, it is obvious that indivual synchronisations to the mobile devices are taking significantly longer expected.
Observing an indivual synchronisation attempt, the Upload changes to Publisher rows are very quickly resolved.
The Download changes to Subscriber seems to take up a very long time.
Along the way, the estimated completion does a few interesting things, like going from 100% complete with no estimated time to complete, back to seomthing like 77% with 2 minutes left to complete.
This sort of behaviour occurs when there are only a hundred rows to download.
Synchronisations for minimal amounts of data suddenly taking anywhere from 2 to 15 minutes. Totally unacceptable form the client perspective but seems that 2005 behaves quite different from 2000 and the tricks are yet to reveal themselves.
Note - It is a server hardware issue as there is in excess of 3 GB ram, the database is on a SAN and there are 4 3Ghz CPUS in operation.
Any possible help appreciated as this issue is beginning to drag on.
July 3, 2006 at 8:00 am
This was removed by the editor as SPAM
October 9, 2006 at 5:50 pm
Hi,
I have not an answer for you, but I am experiencing exactly the same thing. Also a lot of unexpected deletes. Did you find the reason?
Atle
October 9, 2006 at 6:09 pm
What we did was change the way we set up synchronisation. We utilised the pre computed partitions using partition_options = 3.
However, this comes at a cost to subscriber functionality. For instance, you have a flag on a table to indicate that the record is required by a subscriber. You want to clear that flag when the subscriber has finished with the record. As that flag is part of the filter clause for determining what data goes to which subscriber, you cannot change that value at the subscriber - only at the publisher.
This meant we had to make a few application changes, but synchronisation now works very well.
The changes mean that at synchronisation time there is no longer the need to calculate what data the subscriber should get, it already has it waiting. The flip side is that it is a little bit slower sending changes back to the server as it ensures that data goes into any partitions correctly.
October 11, 2006 at 5:10 am
Thanks Steve for the information. I need to read about partition_options. Hope it works fir me to.
Regards
Atle
October 12, 2006 at 11:37 am
You state, "Note - It is a server hardware issue as there is in excess of 3 GB ram, the database is on a SAN and there are 4 3Ghz CPUS in operation."
I presume you meant to say "it is NOT a server issue...."; but let me ask a few questions.
Are you on Server 2003? Have you turned on AWE in the OS? In the database? Is hyperthreading technolology (HTT) turned on? Are two of your four CPUs "virtual CPUs"? Have you tested the application with and without HTT on?
We are going through the same process right now, asking the same questions, and performing the necessary tests UNDER LOAD to determine which works best for us.
Your database software will not use more than 2 GB without AWE. And if you have SP4 installed (DB version 2039) you must also install a hotfix (up to version 2040) or your DB memory will still be restricted to 1/2 of available memory with AWE implemented.
It seem that some operations take TWICE as long to exectute with HTT on, vs. off. This is contrary to Microsoft's statement that SQL Server is "HTT aware" on Server 2003.
You appear to have "solved" your problem; but perhaps you can enhance performance with the right answers to these questions.
Good luck. And please post.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
October 15, 2006 at 3:10 pm
Thanks for the pointers David. Yes I did mean "NOT" a hardware issue. But obviously even with a good set of hardware, you still need to ensure your configuration is optimal.
I am sure that the guys who do all the server maintenance (OS and SQL) will consider these issues. And I'll be bringing your list of questions to their attention. It would be nice to have the server configured
It is SQL 2K5 so I don't think that SP4 will apply. Are you aware of similar behaviour with SQL 2K5?
Also, the OS is 2003.
October 17, 2006 at 11:48 am
yes, SP4 is SS 2000, not 2005, and no, I am not aware of any issue with 2005 SPs. But, AWE still gets "turned on"... (unless you're running a 64 bit CPU, which would not need it)... and there are some additional flags in addition to the /3GB switch setting in the boot.ini if you have more than 4 GB memory. (we don't).
Cheers!
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply