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

How to find version (build)

How do you find out what version of SQL 2000 you are running?
I need to load build 8.00.534 to solve my problem connecting using
linked servers with a Progress database as this is supposed to fix our
problem.
Thanks,
Tom.
Hi,
Execute the below command from Query Analyzer:-
Select @.@.version
8.00.534 is for SP2 , after that install the patch for slammer worm as well.
Check the versions and build number in below site :-
http://www.krell-software.com/mssql-builds.htm
Thanks
Hari
MCDBA
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:408DF32E.2040205@.deltanet.com...
> How do you find out what version of SQL 2000 you are running?
> I need to load build 8.00.534 to solve my problem connecting using
> linked servers with a Progress database as this is supposed to fix our
> problem.
> Thanks,
> Tom.
>
|||SELECT @.@.VERSION ? http://www.aspfaq.com/2160
I strongly, strongly, strongly recommend using this build only for the
required time and only if you are installing it on an isolated machine. If
this build is exposed to the Internet, you are very likely to be complaining
of Slammer very soon...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:408DF32E.2040205@.deltanet.com...
> How do you find out what version of SQL 2000 you are running?
> I need to load build 8.00.534 to solve my problem connecting using linked
> servers with a Progress database as this is supposed to fix our problem.
> Thanks,
> Tom.
>

How to find version (build)

How do you find out what version of SQL 2000 you are running?
I need to load build 8.00.534 to solve my problem connecting using
linked servers with a Progress database as this is supposed to fix our
problem.
Thanks,
Tom.Hi,
Execute the below command from Query Analyzer:-
Select @.@.version
8.00.534 is for SP2 , after that install the patch for slammer worm as well.
Check the versions and build number in below site :-
http://www.krell-software.com/mssql-builds.htm
Thanks
Hari
MCDBA
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:408DF32E.2040205@.deltanet.com...
> How do you find out what version of SQL 2000 you are running?
> I need to load build 8.00.534 to solve my problem connecting using
> linked servers with a Progress database as this is supposed to fix our
> problem.
> Thanks,
> Tom.
>|||SELECT @.@.VERSION ? http://www.aspfaq.com/2160
I strongly, strongly, strongly recommend using this build only for the
required time and only if you are installing it on an isolated machine. If
this build is exposed to the Internet, you are very likely to be complaining
of Slammer very soon...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:408DF32E.2040205@.deltanet.com...
> How do you find out what version of SQL 2000 you are running?
> I need to load build 8.00.534 to solve my problem connecting using linked
> servers with a Progress database as this is supposed to fix our problem.
> Thanks,
> Tom.
>

How to find version (build)

How do you find out what version of SQL 2000 you are running?
I need to load build 8.00.534 to solve my problem connecting using
linked servers with a Progress database as this is supposed to fix our
problem.
Thanks,
Tom.Hi,
Execute the below command from Query Analyzer:-
Select @.@.version
8.00.534 is for SP2 , after that install the patch for slammer worm as well.
Check the versions and build number in below site :-
http://www.krell-software.com/mssql-builds.htm
Thanks
Hari
MCDBA
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:408DF32E.2040205@.deltanet.com...
> How do you find out what version of SQL 2000 you are running?
> I need to load build 8.00.534 to solve my problem connecting using
> linked servers with a Progress database as this is supposed to fix our
> problem.
> Thanks,
> Tom.
>|||SELECT @.@.VERSION ? http://www.aspfaq.com/2160
I strongly, strongly, strongly recommend using this build only for the
required time and only if you are installing it on an isolated machine. If
this build is exposed to the Internet, you are very likely to be complaining
of Slammer very soon...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:408DF32E.2040205@.deltanet.com...
> How do you find out what version of SQL 2000 you are running?
> I need to load build 8.00.534 to solve my problem connecting using linked
> servers with a Progress database as this is supposed to fix our problem.
> Thanks,
> Tom.
>