September 8, 2005 at 1:26 pm
One of my server keeps reach 100% CPU, is there any way to track down what caused the problem. I saw some of the sleeping processes but they took a lot of cpu. Is there a faster way to kill those sleeping process at the same time.
Please help me since I'm in urgent issue.
Thanks,
Minh
September 8, 2005 at 2:20 pm
Under the "processes" tab, order by "cpu" descending to see what is causing it to max out!!!
**ASCII stupid question, get a stupid ANSI !!!**
September 8, 2005 at 2:25 pm
I always it's sqlserver.exe. My cpu constanstly reaches 100% and caused slowness for my website.
Minh Vu
September 8, 2005 at 3:45 pm
Minh Vu - since YOU know best if this could be related to datbase design flaws or not - here's something from BOL to help you troubleshoot:
"Monitoring CPU Usage
Monitor an instance of Microsoft® SQL Server™ periodically to determine if CPU usage rates are within normal ranges. A continually high CPU usage rate may indicate the need for a CPU upgrade or the addition of multiple processors. Alternately, a high CPU usage rate may indicate a poorly tuned or designed application. Optimizing the application can lower CPU utilization.
A good way to determine this is to use the Processor:% Processor Time counter in System Monitor (Performance Monitor in Microsoft Windows NT® 4.0). This counter monitors the amount of time the CPU spends processing a nonidle thread. A consistent state of 80 to 90 percent may indicate the need for a CPU upgrade or the addition of more processors. For multiprocessor systems, a separate instance of this counter should be monitored for each processor. This value represents the sum of processor time on a specific processor. To determine the average for all processors, use the System: %Total Processor Time counter instead.
Optionally, you can also monitor:
Processor: % Privileged Time
This counter corresponds to the percentage of time the processor is spending executing Windows NT 4.0 or Microsoft Windows® 2000 kernel commands such as processing SQL Server I/O requests. If this counter is consistently high when the Physical Disk counters is high, consider a faster or more efficient disk subsystem.
Note Different disk controllers and drivers use different amounts of kernel processing time. Efficient controllers and drivers use less privileged time, leaving more processing time available for user applications, increasing overall throughput.
Processor: %User Time
This counter corresponds to the percentage of time the processor is spending executing user processes such as SQL Server.
System: Processor Queue Length
This counter corresponds to the number of threads waiting for processor time. A processor bottleneck develops when threads of a process require more processor cycles than are available. If more than a few processes are trying to utilize the processor's time, you might need to install a faster processor or an additional processor if you are using a multiprocessor system.
When you examine processor usage, consider the type of work the instance of SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor's time can be used. If this causes the performance of other applications to suffer, try changing the workload (for example, by dedicating the computer to running the instance of SQL Server).
Values around 100 percent, where many client requests are executing, may indicate that processes are queuing up, waiting for processor time, and causing a bottleneck. Resolve the problem by adding more powerful processors."
**ASCII stupid question, get a stupid ANSI !!!**
September 9, 2005 at 7:54 am
What kind of system are you running this on? How many processors, RAM, and how much memory as allocated to SQL Server and the OS.
September 9, 2005 at 11:05 am
Hi,
U can run profilor and see what process is taking time.
from
killer
September 14, 2005 at 1:45 pm
I know that there are several issues with the application designs; however, I had a hard time to persuade the developers. I captured a lot of embeded sql statement that took tremendous amount of CPU. Can someone please explain for me why this kind of statement took a lot of CPU:
Select case when meta_title is null or meta_title='' then name else meta_title end meta_title
from products where product_number='X1-38062'
Thanks,
Minh Vu
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply