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

Question

Post reply

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:

Time Connection Action Details Severity Alarm

4/2/2010 5:30:00 PM SQL1VS1\INS1 Alarm raised An average 8 process threads are currently waiting to be dealt with by the CPU. Low CPU - Processor Queue Length Alarm

When I check the session that are running at that time, I found the below:

spid SQL User Status Blocked By Last Command DBName CPU IO Mem Current Wait Time (ms) Program Last Wait Type Last Wait Resource Login Time Last Batch Time Time Since Last Batch Tran Count Host Name Host Process Net Address Net Library System

65 ABC\mssqlsrv dormant AWAITING COMMAND master 0 0 3 0 Microsoft SQL Server MISCELLANEOUS 4/2/2010 5:30:00 PM 4/2/2010 5:30:00 PM 94 sec 0 SQL1VS1 4116 0017A4770010 TCP/IP N

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: 150457 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

Process PID % CPU Mem Usage (MB) VM Size (MB) Elapsed time Handles Threads Page Faults / sec IO / sec

Idle 0 792.23 0.02 0.00 23d 02:24:54 0 8 0.00 0.00

Process PID % CPU Mem Usage (MB) VM Size (MB) Elapsed time Handles Threads Page Faults / sec IO / sec

lsass 468 2.00 47.94 46.66 23d 02:24:44 1,419 74 5.25 92.99

Process PID % CPU Mem Usage (MB) VM Size (MB) Elapsed time Handles Threads Page Faults / sec IO / sec

sqlservr 4848 1.25 114.79 254.00 14d 18:47:05 848 58 3.88 2.65

Process PID % CPU Mem Usage (MB) VM Size (MB) Elapsed time Handles Threads Page Faults / sec IO / sec

svchost 856 0.91 50.16 35.93 23d 02:24:27 2,539 58 103.09 14.01

Process PID % CPU Mem Usage (MB) VM Size (MB) Elapsed time Handles Threads Page Faults / sec IO / sec

wmiprvse 5100 0.65 14.52 10.89 4d 13:16:24 287 8 3.61 0.53

Process PID % CPU Mem Usage (MB) VM Size (MB) Elapsed time Handles Threads Page Faults / sec IO / sec

sqlservr 6200 0.60 123.44 309.03 14d 18:46:17 972 83 1.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: 150457 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: 150457 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