SQL Server Scheduler
by SQL SERVER : SQL SERVER SCHEDULER
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.
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.
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.
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.
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.
Below script shows the relationship between various DMV
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.
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] |
Statistics Says It All
16
Years of Experience
3000
Gratified Students
100
Training Batches
9600
Training Hours
Subscribe
Please subscribe our technical blog to get recent updates.