1

We've been looking at a SQL based legacy system for the past few months trying to improve performance and have done as much as possible with the code base so now we're turning our attention to the infrastructure.

The problem is our legacy system encounters SQL timeouts, occasionally.

This legacy system is on the same server that runs all the SQL databases for the company. There are things like Sharepoint, Blackberry services, Server Virtualisation database for x9 virtual servers, numerous client-specific reporting databases that run on one SQL database on a mid-range server. The server's three years old. Can you give me an idea if this server is likely to be overloaded by these business services, and if so which one is most likely to be responsible? The company has, say 100-150 employees. Server has 4gb ram.

Additionally;

LegacySystemDB size: 14040384

SharepointSite1 size: 6581568

vCenter 3412928

SharepointSite2 size: 903032

Other db's approximately 50-100,000

Thanks

Improve this question
3
  • What version of SQL? – DanBig Sep 20 '10 at 13:26
  • What does PerfMon tell you? – Please Delete Me Sep 20 '10 at 13:34
  • SQL 2005. What would PerfMon tell me? Never used it. Ok I'll do that, thanks – user38553 Sep 20 '10 at 13:44
4

The answer here is easy, buy more RAM and install it in the server. With only 4 Gigs of RAM, SQL can probably only use 2 Gigs with the other 2 Gigs being used by Windows. That only gives SQL about 1 Gig of memory for it to buffer data in. Once that buffer is full any other data that SQL needs has to come from the disks. My guess is that you are pulling data from the disk, leaving it in memory for just a few seconds and then flushing it out to make room for new data to go into memory.

Start by adding in more RAM. Go for 16 Gigs total (12 more) and see how that helps performance.

Then have someone do some performance tuning on the indexes and see if there are any indexes which need to be added or removed. Have you used SQL Profiler to identify the expensive queries and tune them?

Improve this answer
9
  • 1
    More RAM - only if his architecture and edition of Windows server support it. – mfinni Sep 20 '10 at 17:39
  • 2
    Also - don't suggest RAM as the first thing. You're just guessing; the OP should do some performance analysis of his system. Maybe hes' got a slow disk that holds tempdb and it's getting hammered. Maybe there are other problems in his IO setup that he hasn't described or even known to look for. Suggesting "more RAM" is not guaranteed to be the best first approach; it could be a waste of money. – mfinni Sep 20 '10 at 17:41
  • Agreed. While it looks like RAM is an easy culprit here (4GB is very low) looking for things like Buffer I/O problems would be an easy way to see if you even need to get more RAM. – Sean Howat Sep 20 '10 at 17:58
  • Espeically that very likely the server has no proer disc subsystem. – TomTom Sep 20 '10 at 18:04
  • Normally I wouldn't just suggest more RAM, but with only 4 Gigs (that means pretty much no more than 2 for SQL) you can't really tell if the disks are able to keep up with the load until the database can cache enough info in RAM. This is especially true when dealing with the vCenter database as it reads and writes lots of information through out the day. I'm betting that if the OP were to look at the buffer cache data well find that it's not keeping data in cache more than a few seconds. Until the system has enough cache address disks will be very expensive. RAM is cheap. – mrdenny Sep 20 '10 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.