An average 8 process threads are currently waiting to be dealt with by the CPU

Question

An average 8 process threads are currently waiting to be dealt with by the CPU

Mani-584606

SSChampion

Points: 11788
More actions
April 2, 2010 at 6:43 pm

#156838

Hi,
We are using Spot light monitoring tool to Monitor the sql instances and I got the below alarm:
TimeConnectionActionDetailsSeverityAlarm
4/2/2010 5:30:00 PMSQL1VS1\INS1 Alarm raisedAn average 8 process threads are currently waiting to be dealt with by the CPU.LowCPU - Processor Queue Length Alarm
When I check the session that are running at that time, I found the below:
spidSQL UserStatusBlocked ByLast CommandDBNameCPUIOMemCurrent Wait Time (ms)ProgramLast Wait TypeLast Wait ResourceLogin TimeLast Batch TimeTime Since Last BatchTran CountHost NameHost ProcessNet AddressNet LibrarySystem
65ABC\mssqlsrvdormantAWAITING COMMANDmaster0030Microsoft SQL ServerMISCELLANEOUS4/2/2010 5:30:00 PM4/2/2010 5:30:00 PM94 sec0SQL1VS1 41160017A4770010TCP/IPN
Could you please explain why the process thereads are waiting while the above session is running? Is that normal or do I need investigate for any issue with the CPU?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

The alarm relates to the overall processor queue length, and is user-configurable. Whether you should be concerned or not depends on whether this is a frequent condition or just 'a blip'.

Sustained processor utilization above 80% is generally cause for concern. Spikes in activity, especially if SQL Server is running many parallel queries or other operations, are not necessarily a worry, and may well be completely normal.

The SQL Server process details you listed relate to a dormant connection. AWAITING COMMAND means it is doing nothing, waiting for a request to come in. Not the cause of the spike.

Read the following article for one approach to monitoring such things:

Microsoft White Paper: Waits and Queues Performance Methodology

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Mani-584606 SSChampion Points: 11788 More actions · Answer 2

Paul,

Thanks for your help. today, I noticed 3 times that Processor queue length alarms as below and process running at the time shows as Idle & which is taking all of CPU. But there were no users using the application(it's a BizTalk application) at the time the Processor queue length was high

CPU - Processor Queue Length: An average 12.5 process threads are currently waiting to be dealt with by the CPU

ProcessPID% CPUMem Usage (MB)VM Size (MB)Elapsed timeHandlesThreadsPage Faults / secIO / sec
Idle0792.230.02 0.0023d 02:24:54 0 8 0.00 0.00
ProcessPID% CPUMem Usage (MB)VM Size (MB)Elapsed timeHandlesThreadsPage Faults / secIO / sec
lsass4682.0047.94 46.6623d 02:24:441,419745.25 92.99
Process PID% CPUMem Usage (MB)VM Size (MB)Elapsed timeHandlesThreadsPage Faults / secIO / sec
sqlservr 48481.25 114.79 254.00 14d 18:47:05848583.88 2.65
Process PID% CPUMem Usage (MB)VM Size (MB)Elapsed timeHandlesThreadsPage Faults / secIO / sec
svchost 8560.9150.16 35.93 23d 02:24:272,53958103.09 14.01
Process PID% CPUMem Usage (MB)VM Size (MB)Elapsed timeHandlesThreadsPage Faults / secIO / sec
wmiprvse 51000.65 14.52 10.89 4d 13:16:2428783.61 0.53
Process PID% CPUMem Usage (MB)VM Size (MB)Elapsed timeHandlesThreadsPage Faults / secIO / sec
sqlservr 62000.60 123.44 309.03 14d 18:46:17972831.80 1.62

A sustained processor queue length greater than ten can indicate processor congestion

How can I make sure whether it's a sql server issue or windows processor issue?

and also how to find what are the process running at the time High processor queue length from SSMS or any t-sql scripts. Could you give some more inputs on this

thanks

Mani-584606 SSChampion Points: 11788 More actions · Answer 3

processor queue length:

Processor Queue Length is the number of threads in the processor queue. Unlike the disk counters, this counter counters, this counter shows ready threads only, not threads that are running. There is a single queue for processor time even on computers with multiple processors. Therefore, if a computer has multiple processors, you need to divide this value by the number of processors servicing the workload. A sustained processor queue of less than 10 threads per processor is normally acceptable, dependent of the workload.

So here we have 8 processor. So do I need to dive the number 12.5 by 8?

that means 12.8/8 = 1.6. So now the actual processor queue length is 1.6 & which is normal right?

please clarify me

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 4

System: Processor Queue Length: (N/A) This counter shows the number of threads in the processor queue. Monitor this counter to ensure that it remains less than two times the number of core CPUs.

In our case you have 8 processors, so the processor Queue length should be < 16 (i.e 2 times the no.of processors)

Here you have processor Queue length 12.8 which less than 16 and I believe it's normal.

Please correct me if I'm giving wrong information!!

Paul White SSC Guru Points: 150467 More actions · Answer 5

Mani-584606 (4/5/2010)
...process running at the time shows as Idle & which is taking all of CPU.

The 'Idle' process is just used to count unused CPU.

There is no real process called 'Idle' - it is just the way Windows monitors free CPU time 🙂

With 8 processors, your alarm value could comfortably be set at 80 (ten threads per processor).

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 6

With 8 processors, your alarm value could comfortably be set at 80 (ten threads per processor).

System: Processor Queue Length: (N/A) This counter shows the number of threads in the processor queue. Monitor this counter to ensure that it remains less than two times the number of core CPUs

from above, with 8 processors, Processor queue length should be <16 (2*8 =16 [less than two times the number of core]) right?

But you are telling 10 threads per processor? Could you please clarify me which is correct?

thanks

Paul White SSC Guru Points: 150467 More actions · Answer 7

rambilla4 (4/6/2010)
But you are telling 10 threads per processor? Could you please clarify me which is correct?

There is no one 'correct' value - it depends on the application - I was using the level quoted by Mani earlier. I am assuming the system in question runs SharePoint / MOSS, since the quote comes from:

http://blogs.msdn.com/ketaanhs/archive/2010/03/13/moss-performance-counters.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi