Processor bottleneck

Last Post 27 Dec 2007 01:36 AM by AlexB_SQL. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

26 Dec 2007 09:56 AM

I am having problems with a SQL Server 2000, running on a HP-MSA1000 storage with 20 databases.

Using system monitor, I could see that Processor average user is arount 95% and sqlservr process is the process that consumes allmost whole CPU time.

Most part of the time, disks have Avg. Disk Queue Length below 2. As any DB. sometimes I see peek usage of data disks making this counter goes above 2, but it is not its normal behavior.

I tried to use sysprocesses and sql profiler, but I could not see what is the process that makes sql server usess 100% of CPU time.

What else could I do? What should I search on monitoring tools to find out why SQL Server is behaving like that?

Should I enable trace flags? what flags? sqldiag would help me?

Thanks in advance.

New Member
New Member

27 Dec 2007 01:36 AM
Hi rm,

To do themonitoring, I am using sysprocesses as text below,but some program_name values are showed as "-" and I would like to know if it is possible to find out this progam_name?

select cmd,cpu,physical_io,blocked,
waittime,waittype, lastwaittype,
cast(datepart(hh,last_batch) as char(2))+':'+ cast(datepart(mi,last_batch) as char(2)) --as [last],
from sysprocesses p inner join sysdatabases d on p.dbid=d.dbid
where p.status='runnable'
order by CPU DESC,PHYSICAL_IO,lastwaittype,waittype desc

cmd cpu physical_io blocked memusage program_name
---------------- ----------- -------------------- ------- ----- ----------- ------------------------------------------------SELECT 112101 12409 0 9 :28 1553 -
SELECT 50706 4887 0 9 :28 568 -
SELECT 22532 355 0 9 :28 1236 Cdcinte_bmcveic
SELECT 2819 854 0 9 :28 93 SQL Query Analyzer
SET OPTION ON 781 0 0 9 :28 8 Microsoft® Windows® Operating System
SELECT 469 11 0 9 :28 12 Microsoft® Windows® Operating System
SELECT 236 13 0 9 :28 3 Microsoft® Windows® Operating System
New Member
New Member

28 Dec 2007 12:58 AM
It is an application that uses IIS for some parts e executables for other ones. I cannot figure out what executable or IIS component causes this processor botleneck
Does anyone know why program name is showed as "-" on sysprocesses and, because of that, on sp_who2?
New Member
New Member

01 Jan 2008 02:50 PM
Unless you are designing a replacement for the Space Shuttle, the 100% spin is related to poorly designed queries/sprocs, missing & deteriorated indexing.

Easiest to detect: SQL Profiler duration > 3000 batches, t-sql . (You can start higher and proceed lower)

Look for READS HOGS. Ignore the low reads sprocs which were blocked.

Generally if a sproc is doing over 5000 reads, it is a target for optimization. Exception are infrequently executed batch jobs.

Start with reindexing all your tables with 70% fill factor ( static tables 90%).

Kalman Toth
SQL Server 2005 Performance Tuning -

Acceptable Use Policy