What happens when SQL restarts to improve speed?

[BlakeK]

For some reason our database has begun to slow down after the service has been running for a day or two.
If I start up the server, and run a set of queries, their results are instantaneous. After the server has been running for a day or so, these same queries will take minutes to execute, and some run until killed (no results returned after 1 hour!).
If I stop and restart SQL, then everything returns to being instanteous! I can't figure out what is happening to caus these slow downs. Does SQL have a memory leak that is only being purged when the server is restarted?
I'm not sure how much info I need to provide to be relevant and helpful concerning our setup, so here is some general info...
Hardware is a dual 3.0 Xeon server with 4GB RAM, and a RAID 5 disk array.
OS is Windows Server 2003 Standard.
SQL is SQL 2000 Standard SP3.
No other programs run on this machine, as it is a dedicated SQL server.
Backups are set to run on the database once a week.
Transaction log backups are set to run twice a day.
Integrity Checks are set to run once a day (Job calling xp_sqlmaint with -CkDb option)
I can't run Optimizations (Job calling xp_sqlmaint with -RebldIdx option) because this changes the table schemas and the server has to be up 24/7. I use to do this once a day when we were on a SQL 7.0 server. I'm not sure why this job changes the table schemas in 2000, but it does, so that's why I disabled it).
Same with the Update Statistics job (Job calling xp_sql_maint with -UpdOptiStats option). Caused the same problems under 2000, so it is disabled.

The database itself is configured as follows:
Both data file and transaction log are set to automatically grow by 10%.
Auto shrink and auto create statistics are on, all other options are off.

As I said originally, this server returns SQL very fast when first started, but as time goes on it gets slower and slower, until restarted and then it is fast again.
I am trying to figured out what is slowing it down that is being fixed by a restart. Since restarting the server every day isn't the best solution, I am looking for any other suggestions!
If any additional info about my configuration would help, let me know.
Thanks

 

[mrdenny]

Things that happen when SQL is restarted.
The Cache is cleared.
TempDB is rebuilt.
Procedures are recompilled on first run (I think).

It may be that your cache is filling up, and SQL isn't clearing it.

Check your cache hit ratio through out the day, and while it's slowwing down. See if anything comes up. Also check your drives. Something could be going on with tempdb.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

Have you verified it's just the SQL install that's slowing down and not something else like the Windows OS that is running the SQL Server?

Could be something on the Server actually has a memory leak or network protocols that aren't needed or even running services that are unnecessary.

First, like Denny suggested, run Profiler and keep an eye on Current Activity. Secondly, when things slow down, check other programs on the Server, look for Spyware or viruses (I always check for these things "just in case"), and verify if other network activity (printing, email, etc) is slow or not. If it is, then the problem might not be your SQL install.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[limester]

At what part of the optimization task in the db maintennace plan are the schemas of the tables changed?

I am worried as I have created a plan that will just optimize the database to reorganize index and data pages using the same fillfactor the indexes were created with. Is this still safe so that the schema is not affected?

Cheers

 

[bessebo]

The schema should not be affected by a db maintenance.