How wait statistics are generated

In our last article “Why wait statistics are important and how you can start collecting them now!” we discussed a method of capturing wait statistics and a way to analyze them. Some background information on how wait statistics are generated by the SQLOS will certainly help you with your analysis!


The first important part to know about are schedulers. SQL Server creates a scheduler for every logical processor in your system (+1 for the DAC). These schedulers have the job of scheduling CPU time for your threads. You can actually view your schedulers by querying the sys.dm_os_schedulers DMV.

scheduler has 3 phases (or states) where threads go through so they can be executed by a available CPU, this is illustrated by the image below:


The 3 phases, or states, are as follows:

RUNNING: The tread is being run by the CPU
RUNNABLE: The thread has all resources it needs available and is waiting in queue to get run on the CPU.
SUSPENDED: The thread is waiting for additional resources

A thread will always go through all of these 3 phases while being executed, generally a thread will move through the phases in the following order:

1) A thread will start on the SUSPENDED queue while it’s needed resources are gathered
2) When the resources are available the thread will move to the next phase and enter the RUNNABLE queue, a FIFO queue of threads.
3) The thread has reached the top of the RUNNABLE queue and moves to the RUNNING phase, this means the thread is getting executed by the CPU. If the thread needs additional resources or it has been running on the CPU for too long it will be returned to the SUSPENDED queue and moves through the phases again.

Wait Statistics

Now that we know how schedulers execute threads and the various phases involved we can take a look how our wait statistics are being generated. I have modified the scheduler image above to show you where the wait times are being generated:


The Resource wait time is the time a thread spends in the SUSPENDED phase.
The Signal wait time is the time a thread spends in the RUNNABLE phase (or queue) before it is being executed by a CPU.
If we add both these values together we get the total wait time known as wait time.

For example, if we have a thread that spend 2ms in the SUSPENDED phase and 3ms in the RUNNABLE phase we would get a wait time of 5ms.

SQL Server records these different wait times into 2 DMV’s you can use to monitor your wait statistics: sys.dm_os_waiting_tasks and sys.dm_os_wait_stats.

The sys.dm_os_waiting_tasks DMV will show you want is running on your schedulers at the time you query the DMV. The column wait_duration_ms is the time the thread has been waiting, the column wait_type shows you the type of wait the thread is waiting on.
Using this DMV can give you a quick information when your SQL Server is slow, as you can see what SQL Server is actually waiting for. If you see a lot of the same wait types or very high wait times it can mean the access to that specific resource is slowing your server down.

The sys.dm_os_wait_stats DMV is a cumulated view that gets cleared every time you start your SQL Server. Querying this view will give you all available wait types and the time spend waiting on them since you started your SQL Server (or cleared them manually).
The column wait_time_ms shows you the total wait time of the specific wait type, and the column signle_wait_time_ms shows you the exact time that wait type has spent waiting in the RUNNABLE queue. Next to those columns you can also see how many times that specific wait type occurred by looking at the waiting_tasks_count column.

As you can see both these DMV’s can you a lot of information about how your SQL Server is performing and what it is waiting for.

Additional reading

Sys.dm_os_waiting_tasks : //
Sys.dm_os_wait_stats : //
Why wait statistics are important and how you can start collecting them now! : //

45 comments to “How wait statistics are generated”

You can leave a reply or Trackback this post.
  1. Currently it sounds like Movable Type is the
    preferred blogging platform out there right now.
    (from what I’ve read) Is that what you’re
    using on your blog?

  2. Fair odds can be 2.00 for both and if you guess 10$ on over and the identical on beneath, you’re
    going to get 20$ back, no matter what takes

  3. Hi there colleagues, how is the whole thing, and
    what you desire to say about this article, in my view its
    in fact amazing designed for me.

  4. I read this piece of writing fully concerning the difference of most
    up-to-date and preceding technologies, it’s amazing article.

  5. Hi there, of course this paragraph is actually
    nice and I have learned lot of thinfs from it on the topic of blogging.

  6. I don’t know if it’s just me or if perhaps everyone else experiencing
    issues with your website. It looks like some of the written text on your content are running
    off the screen. Can somebody else please comment and let me know if this
    is happening to them as well? This may be a issue with
    my web browser because I’ve had this happen previously. Thank you

  7. I don’t know whether it’s just me or if everyone else encountering problems with your blog.
    It appears as though some of the written text in your posts are running off the screen. Can someone else please provide feedback and let me know if this is happening to them too?
    This could be a issue with my browser because I’ve had this happen before.

    Thank you

  8. First of all I want to say wonderful blog! I had a quick
    question which I’d like to ask if you do not mind.
    I was interested to know how you center yourself and clear your head
    prior to writing. I’ve had trouble clearing my mind in getting my ideas out there.
    I do take pleasure in writing however it just seems like the first 10 to 15 minutes are generally wasted simply just trying to figure out how
    to begin. Any ideas or tips? Cheers!

Leave a Reply to Cancel reply

Your email address will not be published.