15

When I'm tracking waits with sp_BlitzFirst, I get this detail:

<?ClickToSeeDetails -- 
For 20 seconds over the last 5 seconds, SQL Server was waiting on this 
particular bottleneck.


 -- ?>

Should that read "for 20 times over the last 5 seconds?" Finding was CLR_SEMAPHORE.

24

No - wait stats can (and more often than not, will) total more than the physical time spent for the server itself.

Imagine a situation where 2 separate threads spend the same second waiting for some resource - you would have 2 seconds of wait time for 1 second of clock time.

Each thread has it's own waits - the message is telling you that 20 seconds of wait time for that particular resource occurred over the last 5 seconds of clock time.

Or to put it another way, each core can be running multiple queries at the same time, and multiple cores can add up to even more waits. Which is to say, the unit is really thread·seconds, not seconds.

8

It might also be helpful to work through an example. Consider the three most common states for a worker:

RUNNING = Worker is currently running either nonpreemptively or preemptively.

RUNNABLE = The worker is ready to run on the scheduler.

SUSPENDED = The worker is currently suspended, waiting for an event to send it a signal.

Workers with a state of RUNNING can generate wait time. For example, if the worker needs to run code in the OS instead of in SQLOS then it may enter a preemptive or external wait. During that time it'll be running code on its associated CPU but it'll still be generating waiting time.

Workers with a state of RUNNABLE can generate wait time (as far as I know they always do). If the worker was signaled that a resource was available then it may accumulate signal wait time based on the last wait. If the worker exhausted its previous 4 ms quantum then it may accumulate SOS_SCHEDULER_YIELD wait time.

Workers with a state of SUSPENDED can generate wait time. Consider a worker that is waiting for a lock. It will generate wait time until it is signaled that the lock resource that it needs is available. Some suspended workers don't generate wait time, including those not associated with a task.

My desktop has four logical cores, so the default max worker count is 512. It's almost certainly impractical, but on this machine I could theoretically generate 512 seconds of wait time per second if I managed to get every worker waiting on something at once. As core/worker counts increase that number can get even higher.

You can see more than one second of waits per second even you aren't running any queries against SQL Server. On my machine, the following query seems to generate between 9-14 rows:

SELECT [state], last_wait_type, wait_started_ms_ticks
FROM sys.dm_os_workers
WHERE [state] IN ('SUSPENDED', 'RUNNABLE')
AND task_address IS NOT NULL
AND wait_started_ms_ticks <> 0
AND wait_started_ms_ticks >= start_quantum;

I can take a snapshot of total wait time since I last restarted the server and compare it to a new total after waiting ten seconds:

DECLARE @start_wait_time_ms BIGINT;

SELECT @start_wait_time_ms = SUM(wait_time_ms)
FROM sys.dm_os_wait_stats
WHERE wait_type <> 'WAITFOR';

WAITFOR DELAY '00:00:10';

SELECT SUM(wait_time_ms) - @start_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type <> 'WAITFOR';

Sometimes the math works out. The last time I ran it the delta was 101339 ms. In other words, I had over 10 seconds of waits per second just from system tasks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.