Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Have you tried to set up a trace and see if something strange is going on(may be monthly)? –  DaniSQL Jul 16 '10 at 21:02
add comment

1 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.

share|improve this answer
    
We have set up a weekly job (every sunday) to rebuild all indexes. But there is no monthly job. I really think I will follow your recommendation and call the MS Support. –  Tobi DM May 7 '10 at 9:22
    
This sounds a little crazy - exactly the stuff I would have loved when working at MS (and yes, I did 3rd level support). Expect some heavy lifting there - MS has a nice tool that collects performance parametrics for support in one location. Hoipe you get to 3rd level support fast ;) –  TomTom May 7 '10 at 9:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.