Sql-server – SQL Server becomes slow after restart

sql server

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:

  1. Droped the cache by using the
    statements

    DBCC FreeProcCache
DBCC FREESYSTEMCACHE('ALL')
DBCC DropCleanbuffers

  2. Restarted the Appserver we are using.

  3. Restart the sql server service

But nothing did help exept restarting the whole server. Any ideas?

Best Answer

Call Microsoft. Seriously - this is funny. Unless you overlooked something this should not happen. Not after WEEKS.

  • We can assume the server is getting hot within a day, right? As in: fully utilizing it's resources and handling normal load (a cold server is slower because obviously the caches are mpty).

  • Any sort of caching should not come to pass after WEEKS. I could understand come pollution from bad query plans in stored procedures coming around after some days, but not 4 weeks....

...except: do you do something in a monthyl interval that is RELATED To the time the server gets slow?

Someone will definitely need to take a look at the complete performance metrics from the server and spend some time finding that one out then. Nice caese for PSS (Professional Support Services) ;)

How do you do index reorganization?

I would look for a monthly (30 day interval) process you start that then makes the server slow for some reason.

Related Topic