SQL Server Performance Forum – Threads Archive

CPU running dog slow, but CPU reporting 10% usage

Hello- This would be a first time post for me. I am a sys admin with little SQL experience, so I am learning as I go here. We have a server running dual 1.4 GHZ processors with a Gig of RAM with RAID 5 configuration. The server runs only SQL server 2000 as well as IIS because this server is dedicated to run an application using IIS that works in conjunction with some SQL databases on the same server. Also there are users (about 15-30 at one time max) that use an Access FE that the customer programmed that interacts with some other SQL databases on the BE on the same server. Also running on the server is .NET Framework 1.1 which is needed to interact with the application software. Now saying that, here is the problem: Every other day I am having to reboot the server because the CPU resources appear to be 100% utilized. Symptoms: It takes awhile to log onto the machine, it takes awhile to run any application period, programs stop responding after you try and open them, The access FE connection to the SQL on the customer side runs extremely slow, The web application’s connections to the SQL databases run extremely slow, Tivoli monitoring stops responding, etc. Once I reboot the machine, it is fine for about a day and then this happens all over again. The real enigma is that in task manager, the CPU utilization averages 10% usage, the memory usage shows about 250 MB free, and none of the processes except sqlsrvr.exe show any real memory usage. I have read around on the net and they say SQL server is designed to use up most available memory and will not release it back to the server unless it is requested. I read the Performance Monitoring section of this website and tracked the performance of:
Process Object: % Processor Time
System Object: Processor Queue Length.
System: Context Switches/Sec
Processor time averaged 199. something, processor queue length averaged about .3, and context switching maxed at 1600. I have talked to the vendor of the software running on the machine because it appears that since the upgrade to their latest software we have had this problem more frequently. They are claiming there are no known issues, so I am wondering if there is something in SQL that could be causing these problems? In the past I have monitored the servers because the same thing happened even before the upgrade, but just not as often. I am left scratching my head and have no clue what could be causing this. It appears something is using the CPU resources, but none of the performance monitors are picking it up. If anyone could help out I would greatly appreciate it. Thanks!

For the slow running queries take help of PROFILER which helps to monitor the activty and submit the trace to Index tuning wizard for better index recommendations that may help to gain performance. BTW what are the memory settings on SQL server?
Are there any other applications running parallely?
How about DBCC & DB Health checks?
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Try to Monitor (Performance) all instances in process. If there is something taking CPU, monitor should show. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

The Memory settings are as follow on the server: Dynamically configure SQL Server memory Min: 0 MB Max: 1024 MB and the Configured Values are checked. The only other application running is off a webpage in IIS, but it works with .NET Framework 1.1 and uses Microsoft Mobile Internet Toolkit as well as Message Queuing. The application has three processes running that use on average about 10 megs each. I have tried using Profiler in the past, but the problem does not look to be any specific query running slow, it is all the SQL connections that end up running slow. Also, this should tell you how new I am to this….I am not sure how to do DBCC and DB Health checks…

Well, your information explains the reason of CPU running slow being IIS and other .NET application and also capture counters realted to Memory, Total server memory, Process & processor. With regard to DBCC checks I mean to ask whether do you perform any db maintenance plans that deals in integrity checks and db reindexing etc. If not try to run DBCC DBREINDEX during low traffic on the database and see the performance. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Hi ya, as Luis suggested start perfmon, and monitor the processor utilisation of all instances of the process counter. If you do this as a normal trace (not a counter trace) and set the display to histograph, then you can easily spot the process that is using the largest amount of CPU. (you can ignore total and idle for this of course) Once you have established the process behind the problem, then you can hone in further. If it is SQL then use Profiler to find RPC or Batches taking longer than 100ms to complete. If it is something else then try setting up perfmon to record memory<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />ages/sec cpu:%processor time network:total bytes/sec It may well be an application component stuck in an infinite loop. I’ve had that here before… Cheers Twan

]]>