How to fix performance degredation

I have a SQL DB on one server, but I am using 2 servers for load balancing when I do my nightly DW refresh. One server is much more powerful than the other (faster and more processors, more memory).

Over time, my nightly process is slower and slower as the size of the DB grows (now 15GB) and larger amounts of data need to be put in using insert/update SQL statements (by the way we are using Cognos DecisionStream).

Does anyone have any suggestions for me as to how to shrink the overnight processing time for the refresh, or what is the best way?

At this point, deleting old data is not an option. On the table are the following options, what are the best in your experiences and what do you suggest?

1. Upgrade server #2, so it is powerful as server #1.
2. Buy a 3rd server and do even more load balancing to have more of the refresh running at the same time.
3. Use another DB instead of SQL, like Oracle, DB2, etc.

Any answers are greatly appreciated.

Lastly, for #2 above (adding a 3rd server), can SQL server handle multiple servers running update processes to the same DB? Will I even decrease the times at all by adding servers and load balancing, or will the increased load from 3 servers to one DB slow all of them down too much?

THANKS!uhhhh, do you have any clue what the bottleneck is here? What do your performance monitor counters look like? During this run time, look at the processor utilization, network utilization, disk queue lengths on both servers, memory utilization, buffer cache hit ratios, etc. What do they tell you?

Also, run profiler and see if you can see where you're getting table scans, slow running pieces, etc. The answer isn't usually to throw more hardware. It to do performance tuning.|||I suspect the bottleneck is related to adding more data, bigger fact tables, and this combined with the update/insert statements. These are the processes that are really slowing down.

I have not done any performance monitoring or profiling. Where do I set up Perf. monitor, is this in SQL server? Can I look at the logs the next day, or are these monitors used to look at real-time. My refresh starts at 9pm and goes until 5am. Profiler is in SQL server, right?

Do you have any suggestions, or can you forward me to documentation as to how to best set up the monitoring (so that doesn't use up a lot more than I need to), analyze the logs and change settings?

THanks.|||You can look at www.sql-server-performance.com . Performance Monitor is a utility that comes free with Windows. Profiler comes with SQL Server.sql