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