SQLOS and Windows OS – Part I

If you’re a SQL Server professional you may have already familiar with SQLOS. At least you might have heard it but may not know much about it. However everyone familiar with the Windows OS but again may not know much technical aspect of Windows. Windows is a general purpose OS designed to install application programs and manage them. E.g. you can install MS Office into your computer only after configuring Windows OS.

Windows OS

When you execute an application it creates a process in Windows OS. Until such time it is in inactive state (stored in storage). Let’s consider SQL Server. It is just another application as far as Windows is concerned. SQL Server process will create in Windows as soon as you start SQL Server. You can simply see the processes in Windows Task Manager. (See Figure 1.1)

Figure 1.1 – SQL Server Process in Windows Task Manager

In Windows Task Manager right click on sqlservr.exe and select Set Priority then you can notice the priority of the process. It is normal for SQL Server which means Windows treats SQL Server as a normal process. (see Figure 1.2)

Figure 1.2 – SQL Server process priority in Windows OS

As you know multiple users can connect to a SQL Server and do various activities. E.g. run ad-hoc queries, execute stored procedures, etc. Have you ever think how single process handles multiple user requests? That is where threads concept or multi threading comes in. When a user connects to the server, it creates a thread within the process so that user request can be handled through the thread. See below for the definitions of process and thread.

A process, in the simplest terms, is an executing program

A thread is the basic unit to which the operating system allocates processor time

As many users connect to the process the more threads it create.

See Figure 1.3 for graphical representation of process and threads.

Figure 1.3 – Relationship between process and thread

Refer below links to learn more about process and thread.

http://www.differencebetween.net/miscellaneous/difference-between-thread-and-process/

http://www.computerworld.com/s/article/66405/Processes_and_Threads

http://msdn.microsoft.com/en-us/library/ms684841(v=vs.85).aspx

Threads scheduling

Now the question is how these threads interact with the Windows OS. Let’s consider the generic model of interacting threads with the OS. Remember these threads are created in the application process. The process is running in user mode as against the kernel mode. In order to complete the work assigned to a thread it has to communicate with the OS, in another words, there must exists a relationship between user threads and kernel threads. If you consider OS theory, there are three models;

1.       One-to-One model

2.       Many-to-One model

3.       Many-to-Many model

These models are designed by considering the fact that how many user-level threads associate with how many kernel-level threads. E.g. in One-to-One model maps each user thread to a kernel thread. Linux, along with Windows family implement this model.

Let’s consider how Windows handles kernel threads. This is known as threads scheduling. The basic idea of this is how a thread allocates to a CPU so that it can process and finish the task assigned. There are various algorithms available and they are using in different OSs. Since our focus here is Windows OS, will consider the algorithm used in Windows.

Windows implements a priority-driven, preemptive scheduling system. Here preemptive means, once a thread assigned to a CPU it can stop its execution and go to the suspend state due to various reasons. As per this scheduling algorithm, the highest-priority runnable (ready) thread always runs - a priority driven. There are six priority levels in Windows as stated in Figure 1.2.

1. Real time
2. High
3. Above normal
4. Normal
5. Below normal
6. Low

I will discuss more about Windows OS in my future posts. This is extremely important to understand the SQLOS.