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

our server is running win2003 with mssql 2005 on the same box with 2GB ram. the website itself is based on dnn 4. We are seeing these high cpu usage spikes (mssql) of 95-99% even with no traffic.sometimes they appear with regular intervals with no traffic.

What can possible cause this? how do find the root cause?

share|improve this question

3 Answers 3

Do you have content indexing turned on in DNN? It could be DNN running an index of your site content. This indexing is performed in the background and will often happen during times when you have no other traffic activity hitting the database.

I would recommend setting up a SQL Server Profiler trace on your database and see what is going on during the spike times.

share|improve this answer

During a period of high CPU usage, you need to either jump on the box and use Activity Monitor, or type in something like sp_who2. Also check to see what SQL Server agent jobs are scheduled, when - and see if this correlates. This will give you an idea of who is doing what.

Squillman's suggestion to use Profiler is good advice, especially to monitor when unattended - and check back later to see what was going on.

share|improve this answer
    
I HAVE RUN THE SP_WHO2 COMMAND AND HERE IS THE RESULT: docs.google.com/… –  herman Apr 4 '11 at 23:44
  1. Check for SQL Agent jobs running.
  2. Check to make sure you don't have auto-shrink enabled for any of your databases.
  3. If the server is not dedicated to SQL, make sure memory configuration is set to leave RAM for other processes.
  4. Profiler/sp_who as others have suggessted. Careful with Profiler, it can stress a stressed out server even more if you are capturing too much info.
share|improve this answer
    
1. no sql agent jobs are running. 2. auto shrink is NOT enabled. 3: how do i allocate ram for other processes and how much should be allocated for sql? and how do i make these settings and where? –  herman Apr 4 '11 at 22:58

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.