Proving CPU Pressure – or – What’s a Signal Wait?
This is not an entirely new post, I won’t lie. Lately I’ve been asked about signal waits quite a few times, so I wanted to point to a past post. In Scheduler Yields = CPU Pressure…or do they? I discussed signal waits and talked a bit about wait event analysis. Here’s some additional and summary information… Wait event analysis is the practice of looking at SQL Server wait events to determine the cause of specific instance/database/statement/application behavior. Microsoft refers to this as analyzing waits and queues. Basically, the execution model for a session dictates that each session is either running, waiting or sleeping. The queues organize sessions into buckets of running, runnable or suspended. If a session is running, it is actively running and using CPU resources. If a session is runnable, it is waiting for CPU time. If a session is suspended, it is waiting for another physical resource to become available. This is best described using the [fantasy version of a] supermarket analogy where CPU is a cashier, a session is a person in line (in the queue to use the UK vernacular) and a resource is anything from change to preferred shopper cards to coupons:
- When a customer gets to the cashier, he/she is running (using CPU)
- Everyone else in line is runnable, waiting for time with the cashier
- As soon as the customer needs to reach for change, dig for coupons, he/she is immediately moved to the back of the line (put in the suspended queue) and the next customer in line (in the runnable queue) gets to move up to the cashier
Where people get confused is when signal waits come into play. Signal waits are actual waits for CPU time – pure CPU wait measurements. So, if you are analyzing wait events, and are trying to come up with an aggregated time for instance/database/statement/application activity, you need to add up the wait event time AND the signal wait time to get a complete picture. I’ll post more on this topic at a later time to go into more detail. If you have any questions or want more information on specific information, please comment.
