We Have to Reboot Server to Improve Performance 1

[JohnBates]

Hi experts,

This is SQL Server 2000 on a xeon 3 Ghz processor w/ 1 GB of memory. I have SQL Server set to dynamically allocate memory.

We are in a phase of a project where we have to query million of rows and copy large tables to other databases.
(When the project goes into production, fortunately, we won't be doing so much querying/copying.)

After a few hours of this, we notice a fairly significant slow-down - I'd say approx. 40 to 60 % slower than earlier.

A restart of the server always improves performance for several few hours... then we need another reboot.

Soon, I will add another 1GB of RAM and probably increase the size of the tempdb files also.

** But why does SQL Server's performance increase significantly by simply restarting the server ? **

If I understood that, I may be able to adjust a few things in the SQL Server configuration to keep performance more on a stable level.

Thanks, John

 

[jgoodman00]

How big is the DB in question?
How big is the Transaction log?

What size is the pagefile for the system?
How much free space is there on the disk/s?

It is probably also worth checking out the following MSDN chapter for info on monitoring the performance of the server:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_00mr.asp

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures