50% CPU usage showing on HT Server ??

[dumdum]

I'll keep this short. Also, I am not one of the IT people at my office. I'm just trying to find an answer for them. I do understand SQL Server somewhat and am comfortable making any adjustments to the software

We Host our web site in house. It's backend is a new install of SQL 2000 running on a Dell P4 HT machine with 512mb ram

For a while we were getting 100% CPU usage about 60% of the time for both Virtual CPUs when only 2 or 3 people would be accessing the database. Nothing major, just a few small queries etc. At peak hours we have approx 25 people accessing it constantly and the thing is brought to it's knees.

I noticed that in the task manager that priority was given to "background Services". I changed this to "Applications" and the 100% flatliners went away.

Here's what's weird. Late at night before I leave and no one is on our web site or using the SQL box both virtual CPU's will show that they are running at an average of 50%.

Please help. I can provide more specific information if required.

We are always looking for new members at our computer forums:

http://forums.jlrforum.net

 

[pabowen]

I think you have more questions than answers at this point. You did not state, but I am guessing that your high cpu utilization is being caused by SQL? or is it possible that another process is involved (Anti-virus performing dynamic checking can be infamous for this).

Other questions remain how is your database configured? single or multiple drives? Transaction log is it on the same drive as the dbase?. How large is the actual dbase? And was it created in house or by a third party?

A high level view is to use task manager to see who or what is utilizing the cpu. If you turn on the 'show kernel' it will display a red line indicating the power being consumed by OS service and drives.

Is it possible that the queries that are being run are poorly designed? perhaps joining across a bunch of tables? What about missing indexes?

I think before we can look at this any further we are going to need some more detailed information. Unfortuneatly it is not an easy question and the answer may be found in a dozen different places.