SQL OS is a kind of operating system built for SQL Server and works on top of Windows Operating System. SQL OS will depend on windows API to perform tasks but it has its own way of handling things. SQL OS was introduced in SQL 2005 and it has the major functions like IO Management, Memory Management and Resource Scheduling. Prior to SQL Server 2005, SQL 2000 uses UMS (User Mode Scheduling) to performing scheduling and other tasks. The introduction of SQL OS is to bring everything into one place and we will have better visibility what is happening at what level, all these can be monitored through DMV’s, the DMV’s starts with sys.dm_os provides complete information of working of SQL OS such as schedulers information, wait tasks information, memory clerks etc.,
A CPU is a single-core or multi-core processor, core means processor, single core means one processor and multi core means two processors, even though a processor is one chip internally it has two physical cores. Dual core has 2 cores, Quad core has 4 cores, Hexa core has 6 cores, Octo core has 8 cores, Deca core has 10 cores. Intel CPUs uses a technology called “Hyperthreading” using which a single core processor itself represents has two logical cores to the system, which means if we use a quad core processor with hyperthreading technology you will be able to see 8 cores in the task manager. These are called logical CPUs these will be visible to Operating System (OS)
Thread
A thread is a process or a part of process, Select statement is a process in SQL Server it might run as a single thread in the process (if it is simple query) or it splits into multiple threads and runs on multiple processors to complete the process (if it is complex query) to complete the process faster. Each thread will get some amount of time in the processor and it will move out of the processor to give processor time to other waiting threads the process of moving threads into processor and out from processor is known as scheduling.
Scheduler
Scheduler is a logical CPU in SQL Server, Each scheduler is mapped to one logical processor shown by operating system, and one logical processor can have multiple schedulers assigned to it. Visible schedulers are helps to process the user requests, hidden schedulers are only visible to SQL Server internal processes, and one scheduler is dedicated for DAC (Dedicated Administrator Connection). The scheduler’s information is available in sys.dm_os_schedulers DMV.
Scheduler maintains two components, Wait List and Runnable queue. The processor is processes one thread at a time, Wait list is the list of threads waiting for resources, Runnable queue is a set of threads that has resources but waiting for its turn to get into the processor, Scheduler will put thread into runnable queue and move that thread to processor. The runnable queue follows FIFO when thread moves from wait list it joins at the end of runnable queue.
The wait list is a list of threads waiting for resources and this is unordered queue, the waiting thread will pushed into runnable queue when it founds the resources. The thread which is in the wait list can be cancelled due to execution time out, sys.dm_os_waiting_tasks helps to examine the wait list queue.
Thread States
The thread can have any of the three states, Running, Runnable, Suspended, Running is the state where the thread is currently executing 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. While a task is running it might requires other resources to proceed further (data page, memory etc) that time the thread state will become suspended and moves to wait list and it remains there until it gets the resource. Once the resource is available the task is moved to runnable state and waiting in queue for processor time. It gets executed once the task before in the queue is executed.
