I am looking to improve the performance of my sql server databases.
I currently have a dual location system, the database server setup is basically a quad xeon with 4gb at my office and a double xeon with 4gb at a remote webhosting location. There are separate application/web/intranet servers at each site. The two databases servers are replicated with the local server publishing to the remote server.
The relational database holds circa 26 million records, growing by a volume of 10,000 per day, there are approximately 50,000 queries performed per day.
My theory is that the replication of the two databases is causing a slowdown; despite fast network connections (averaging 200ms between servers) the replication seems to place a large load on the local server. Would it be sensible to replicate to a second local server and then replicate to the remote server, placing any burden on the second server?
I am planning to upgrade the local server to a high capacity 4+ cpu 64bit server, my problem is that although I have noticed a slow down in performance over time, I am unsure how to go about measuring and quantifying this in order to diagnose the bottlenecks and ensure that investing in a new server would be worthwhile. Where would one be best advised to start this project?
Hi Gavin. What type of replication scheme have you implemented? Transactional/merge? Peer-to-peer, read-only subscriber, queud/immediate updating subscriber, etc.? Where to start on the research would have a lot to do with what type of topology you are using.
How are you getting to the conclusion that replication is responsible for the slowdown?
|||Hi - it is a transactional type replication.
Chad Boyd MSFT wrote:
How are you getting to the conclusion that replication is responsible for the slowdown?
When turing off replication, the performance was improved. It seems that the bandwidth between servers would explain this.
What can I tell you about the topology?
|||Hi Gavin. Is it a bi-directional replication setup? i.e. are the subscribers set to replicate updates back to the publisher, or are the subscribers simply read-only? What type of link exists between the sites (T1, T3, partial T, etc.)?
From the general sounds of things, you don't have an extremely busy write server, so a decent link between the 2 sites sounds sufficient for what you have, which is why it would be surprising to hear that the bottleneck is the network bandwidth...of course, it most certainly could be depending on the types of transactions you are seeing, this is just me thinking out loud.
You mentioned when turning off replication that performance improved, do you mean that end-users received responses to queries faster? Or you noticed particular counters drop significantly? Or possibly blocking/locking issues disipated?
I'd be surprised if the link is the bottleneck, since when you say performance improved I'm going to assume you mean end-users started seeing faster response times to requests...if that's the case, it would seem that there is something occuring on the box itself that is slowing down the response times (of course, that could be the replication agent keeping a lock on something because it is waiting for a response from the subscriber across a slow link, but in transactional replication, that's not as common as with merge, where the agents are querying tables directly...in transactional replication, the log is read directly).
Anything you can post that explains what you are seeing in terms of what is showing you performance is improved? Counters, query response times, etc.?
|||I am also working with large volumes of data average of 12million records per table and a total of 23million record.No cluster or Indexes, this is because the data is to bulk to change.While running queries i find that my application hangs even if i set the ODBC timeout to 0. Unlike you i working with a normal X86 2.86 GHZ and 504MB RAM.
Please advice
|||thank you for all your replies and assistance so far.
Chad - to answer your questions the subscribers are all read-only and there is a T1 link between sites.
As I am a developer and not a database specialist I have decided that I need to bring in some outsourced consultancy. Before I do this I would like to do some research so that I can learn as much as possible I would like to be up to speed on this and have as much background knowledge as possible.
I think that the first thing that I should do is to measure the facts as much as possible. Could you please advise me as to what tools and applications I can utilise to gather statistical facts?
What can I learn from my log files? What monitoring tools can I install?
