I currently have a server which runs SSIS 2008 and SQL 2008. I want to improve the performance of the server and keep the performance of the server steady when SSIS is running.
I have an SSIS package that runs once per hour on the server and it slows the server down for around 3 minutes. In this 3 minutes some sites can stop responding altogether due to the load on the tables and locking. This process needs to run once per hour to keep stock levels up to date and also pricing so the process is essential.
My question is, will throwing more RAM at the server sort the issue or will I have to resort to putting another CPU in and in the same respect another license of SQL?
It's currently the following spec
Quad Core Xeon 2.8 x 1
8gb Ram
Windows datacenter 2008 32bit
2 x 7,500 rpm 500gb drives
Proposed spec
Quad Core Xeon 2.9 x 1
24gb Ram
Windows standard 2008 64bit
2 x 15,000 rpm 300gb sas drives
Do you think this will pose a significant increase in reliability?
EDIT
After some investigation into possible bottle necks it appears that when my SSIS import starts it is reading ~200 million b/min, when its close the end it is reading over 1 billion b/min. The processor is around 20% usage and the RAM is at 53%? I have modified the SSIS slightly to do more items in parallel and it does help a little but the issue still remains? Anyone any ideas?
To note, I have my log file and data file on separate discs. The log is on my E partition and the data is on F which is a separate SAS SAN drive. I'm not sure how the SAS SAN allocates resources but it is a shared SAN which is in the datacenter. Would it be better to fit two SAS drives to the server rather than using a shared resource?
Best Answer
It's probably an idea to do some performance monitoring, identify the actual bottleneck and address it. RAM and CPU are both equally "important" to a server, and its better to address this sort of thing scientifically rather than just throw a bunch of resources at it to see what sticks.
When you say "will throwing more RAM at the server sort the issue or will I have to resort to putting another CPU in" then the answer isn't one of the other of those two things. The answer is always "It depends on what is making it slow down, measure the performance of the system and find out". It could equally well be storage that is the bottleneck.