Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer
    
Well I assumed that storage was an issue so I rented 50gb of SAN space on SAS drives but I didn't see anything worth writing home about. It definitely reduced the issue of the sites not responding once per hour but it just means the sites slow down a lot. I did check the performance monitoring and the bottleneck at the time was the hard drives. I really thought that would ease the server. –  Chris Lomax May 23 '11 at 14:10
1  
Or even the DB design. Might not have the best indices. –  mfinni May 23 '11 at 14:11
    
As mfinni says, Chris, it could even be a software/design issue. It may be (and yes I have seen this one) that rather than having one particular "fault" that needs to be corrected, you have a number of small issues that aren't much by themselves but overall add up to poor performance (maybe you need a bit more RAM, maybe your storage isn't organised quite right, maybe the processors could be a bit faster/need a bit more cache, etc) and you will need to work on all these areas combined to see great performance rather than just pushing everything into one area. –  RobM May 23 '11 at 14:15
    
Well the issue only raises it's head when the SSIS packages are running. The software itself is tuned to death. I have run SQL profiler on all queries in my db, I make sure all query plans are using the correct indexes etc etc. The issue only really comes around when the packages are running. The package updates around a million rows in the db when it runs –  Chris Lomax May 23 '11 at 14:18
    
I hear that, clearly the SSIS job is pushing it over the edge. It may be that this needs a re-design (could you run a few 'lower impact' jobs at a higher frequency to get the same result?), or that you need to capacity plan with this in mind as well as your general SQL Server load (I'm sure you already are trying to do this!). –  RobM May 23 '11 at 14:22

In most cases, RAM. Not very often with today's servers are you CPU-bound, which is why virtualization works so well, but demands a ton of RAM.

share|improve this answer
1  
While this is true in general, Robert makes the very good point that for this scenario, Chris shouldn't guess. He can run SQL performance tools (profiler, tuning adviser) to see what his actual bottleneck is. It could even be something like missing indices, and no new hardware is needed. –  mfinni May 23 '11 at 14:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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