I already posted this one on stackoverflow but someone gave me the hint to that I might have more luck on serverfault. We use SQL Server 2005 on an Windwos Server 2008. Ther Server has 48 GB RAM. SQL Server is configured to use 40 GB RAM. There is only one database hosted (About 70 GB). The only app beside SQL Server is our App-Server which connects the clients to the database.
Now we encounter the following problem:
After a restart of the server our the performance is great. The server grabs the 40 GB RAM wich it is allowed to and then runs fast as hell. But after about 4 weeks the system becomes slower and slower. The execution of statements (seen in the profiler) is raising slowly. But I cannot see that there is something going wrong on the server.
- CPU usage is at about 20%
- I/O also seems to be no Problem
- The process monitor does also not show that there are strange apps or something like that.
- Eventlog does also have no interessting messages
- No open transactions or blockings to see
- We do not use cursors in our app
We tried already the following things without effect:
Droped the cache by using the statements
DBCC FreeProcCache DBCC FREESYSTEMCACHE('ALL') DBCC DropCleanbuffers
Restarted the Appserver we are using.
- Restart the sql server service
But nothing did help exept restarting the whole server. Any ideas?