SQL Server 2005 runs slow after a couple hours
Posted on 2007-10-03
We are having some trouble with our SQL server 2005 database. Here's the scenario...
We are running a .NET front and application which talks to the SQL customer tracking database. Both SQL server and the application run on the same box under Windows Server 2003 Standard. Hardware is a Dell 2950, single dual core CPU, 4gb ram, 4disk raid 1+0.
Here's the problem. Everything runs fine for 2-3 hours, moderate CPU usage, no memory paging, only occasional disk reads... Then performance drops off. Queries generate large disk reads, disk queues shoot up and everything grinds to a stop. Performance continues to drop off for another hour or so until finally users are getting timeout errors.
I jump into management studio and restart the SQL server, then everything runs fine for another 2-3 hours.
What is causing SQL server to suddenly need so many disk reads, and how do I resolve it?
According to perfmon, there is still memory free, and there is no paging going on, just large disk reads that are slowing everything down.