While troubleshooting the performance issues of SQL server, you might have seen the sessions are in different state like SUSPENDED,RUNNING,RUNNABLE etc. In this post let us try to explain this by going through the threading model of SQL server.
SQL server is like a operating system like windows.It has its own mechanism to handle the memory management, IO and thread scheduling. SQL server might be using the windows API to do these task but still SQL server control how it works for it.
Thread
Thread is the lightweight process or it is part of process.For example running simple select statement can be considered as a process and it might run in the processor as a single thread. Where as a complicated select statement (process) which returns thousands of rows might split into multiple threads which will run on different processor to complete the process much faster.Each thread will get a small amount of time in the processor,then it move out of the processor to give processor time to other thread and it has to wait for the processor time to complete it pending task. The process of moving threads to the processor and moving out of the processor is called as scheduling. This will give an impression that many process are happening but in reality only only one thread can be executed in the processor at any point of time.
SQL Server uses operating system thread to perform its action and it is called as worker thread.There are dedicated thread and pool of threads. There are dedicated thread for check point , dead lock monitoring,etc. The pool of thread is used to process the user request.
Thread Scheduling
As mentioned earlier, SQL server controls its thread scheduling and it use non-preemptive scheduling and windows can not interrupt these threads.When SQL server managing the scheduling of its thread instead operating system, it has more control and it make its own priority.The thread scheduling inside the SQL server done by the SQLOS which is an interface between the SQL server and operating system.Each processor core (logical or physical) which allowed to use by SQL server has a scheduler.For example in a machine with two physical core, with hyper threading enabled, will have four scheduler.There are scheduler for user threads and for internal operation.For some processor can have two scheduler one for user request and other one for internal for database engine. The list of schedulers can be seen by querying the DMV sys.dm_os_schedulers. The offline schedulers are mapped to the processors that are offline in the affinity mask and not being used to process any request. Visible schedules are used to process the user request where as hidden schedulers are used to process the internal request. There is one dedicated scheduler to process the DAC requests.When one of thread needs something from OS(calling a windows API) , that thread has to switch to preemptive mode which enable OS to control when that thread goes to sleep or any other state of the thread.
Scheduler
A schedule has three important component, Processor,Waiter List and Runnable queue. A Processor is the actual processor which process the thread one at a time.Waiter List is the list of threads waiting for resources.Runnable Queue is a queue of threads which has all the resource it need to process but waiting for its turn to get into the processor.Scheduler put the thread in to Runnable Queue then move the thread to Processor and migrate to Waiter List. Each thread keeps going through these three component until the thread works completes.
The waiter list is a list of threads which are suspended and waiting for a resource. This is not a queue as there is no order in which the thread will get the resource.There is no parameter which define the maximum time a thread can be in the waiter list. Theoretically there is no limit but the timeout specified in the query execution session may take effect. While waiting in the Water List , the thread might get canceled due to the execution time out.The Waiter List can be examined by querying the DMV sys.dm_os_waiting_tasks.
The Runnable queue is a pure First-In-First-Out (FIFO) queue.When a thread moves from Waiter List it joins at the bottom of the Runnable queue. We can see the size of runnable queue by looking into the column of runnable_tasks_count column in sys.dm_os_schedules.There is special case when resource governor enabled and relative priorities assigned to multiple workload group for a resource pool.Possible values for priorities are High,Medium and Low which equate to 9,3 and 1. It means that 9 high priority thread and 3 Medium priority thread can override a low priority thread in the Runnable queue.
The waiter list is a list of threads which are suspended and waiting for a resource. This is not a queue as there is no order in which the thread will get the resource.There is no parameter which define the maximum time a thread can be in the waiter list. Theoretically there is no limit but the timeout specified in the query execution session may take effect. While waiting in the Water List , the thread might get canceled due to the execution time out.The Waiter List can be examined by querying the DMV sys.dm_os_waiting_tasks.
The Runnable queue is a pure First-In-First-Out (FIFO) queue.When a thread moves from Waiter List it joins at the bottom of the Runnable queue. We can see the size of runnable queue by looking into the column of runnable_tasks_count column in sys.dm_os_schedules.There is special case when resource governor enabled and relative priorities assigned to multiple workload group for a resource pool.Possible values for priorities are High,Medium and Low which equate to 9,3 and 1. It means that 9 high priority thread and 3 Medium priority thread can override a low priority thread in the Runnable queue.
State of threads
A thread can have three states , Running ,Suspended and Runnable. Running is the state where the thread is currently in the Processor and utilizing the CPU. Only one thread per scheduler can have this state as the Processor can process only one thread at a time.When a thread need a resource to process further it has to wait for the resource.The thread will be move to Waiter List and thread changes the state from Running to Suspended.When required resources are available after a period of time, nothing is stopping the thread from running.But eventually it has to wait for its turn in the Processor. The process that tells the thread that resources are available is called signaling. When resources are available for a thread (thread is signaled) which was in suspended mode it will move to the Runnable queue and wait for its turn with a state called Runnable.When the thread moves to processor it change the state from Runnable to Running.The transition between these states keep happening till the thread completes its work.
There is case where thread by pass the Suspended state and directly move to the Runnable from Running and this is called quantum exhaustion.If a thread does not need to wait for any resources, it will continue to run till its quantum is exhausted. The quantum is fixed to 4ms and not configurable. The last column of the DMV sys.dm_os_schedulers define this value. Even if the thread does not need to wait for any resources,after the completion of its quantum time, it will move out of the processor and its state change from Running to Runnable. The thread move directly from Processor to bottom of Runnable queue bypassing the waiter list as it does not need to wait for a resource.
There is case where thread by pass the Suspended state and directly move to the Runnable from Running and this is called quantum exhaustion.If a thread does not need to wait for any resources, it will continue to run till its quantum is exhausted. The quantum is fixed to 4ms and not configurable. The last column of the DMV sys.dm_os_schedulers define this value. Even if the thread does not need to wait for any resources,after the completion of its quantum time, it will move out of the processor and its state change from Running to Runnable. The thread move directly from Processor to bottom of Runnable queue bypassing the waiter list as it does not need to wait for a resource.
Below script shows the relationship between various DMV
SELECT
[dot].[scheduler_id],
[task_state],
COUNT (*) AS [task_count]FROMsys.dm_os_schedulers dos INNER JOIN sys.dm_os_workers dow ON dos.scheduler_address=dow.scheduler_addressINNER JOIN sys.dm_os_tasks AS [dot] ON dot.task_address=dow.task_addressINNER JOIN sys.dm_exec_requests AS [der] ON [dot].[session_id] = [der].[session_id]INNER JOIN sys.dm_exec_sessions AS [des] ON [der].[session_id] = [des].[session_id]WHERE [des].[is_user_process] = 1 GROUP BY
[dot].[scheduler_id],
[task_state]ORDER BY [task_state],[dot].[scheduler_id]If you liked this post, do like my page on FaceBook
www.PracticalSqlDba.com
