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
Best Answer
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?