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!
Schedulers
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.
A 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 : http://technet.microsoft.com/en-us/library/ms188743.aspx
Sys.dm_os_wait_stats : http://technet.microsoft.com/en-us/library/ms179984(v=SQL.105).aspx
Why wait statistics are important and how you can start collecting them now! : http://www.dotnine.nl/index.php/wait-stats/16-why-wait-statistics-are-important-and-how-you-can-start-collecting-them-now
