SQL Server thread scheduling cycle

Here is a simple diagram illustrating how SQL Server thread scheduling works.

SQL Server Thread cycle

Whenever a new thread arrives it got a slice of CPU. This is shown as Running.

When the thread has to wait for a resource it moves to Suspended list.

When the thread in the Suspended list is notified for the available resources, the thread moves to Runnable queue.

Meanwhile the next thread in Runnable queue will move to Running for it’s share of CPU.

This cycle goes on till the thread finishes the work.

The time taken by a thread between leaving the Running state and again enters Running state is called Wait Time.

The time taken by a thread between leaving the Suspended list and entering the Running state (i.e. time spend in Runnable queue) is called Signal Wait Time.

You can see these states when you query the DMV sys.dm_exec_cached_plans on status column or executing  sp_who

The Wait time is a very important factor in SQL Server troubleshooting. You can read more about in SQL Server 2005 Performance tuning using Waits and Queues.

Advertisements

Leave a comment

August 1, 2014 · 4:03 PM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s