SQL SERVER – What is SQL Server Operating System?

Sometimes the strangest of questions come from unusual places. This blogging journey is all about revisiting the basics. I have always felt getting some of these basic questions can make us learn even more and look into the internals of how products work. In my recent visit to SQLPass and on one of my book signing times, one of the attendees walked up to me and said do you know about the SQL Server Operating System? I was surprised because it didn’t strike to me at first shot. I went ahead to ask – “What do you mean sir? Where did you see it?”. The reply was – “I read your wait stats book and when I was looking at the DMVs for wait stats, I saw something with SOS* event. I searched the internet to find out it was SQL Server OS. So what is this OS?”. I was pleasantly relieved to hear it because I checked this blog because I couldn’t find anything.

This blog is all about explaining some of the functions of SQLOS and what it is not to be assumed as. The SQL Operating System (SQLOS) was introduced in SQL Server 2005. SQLOS is a very thin abstraction layer. SQLOS is NOT:

1. A wrapper for Operating System APIs.
2. A bridge to other Operating Systems. The SQLOS does not allow us to port to other operating systems. That is not the intent of the SQLOS.
3. A shared component library. For a matter of fact, each instance of SQL Server has its own SQLOS. So if you have 2 instances of SQL installed each instance has its own SQLOS.

In reality, the SQLOS performs the following critical functions for SQL Server:

1. Scheduler and IO completion. The SQLOS is responsible for scheduling threads for CPU consumption. Most threads in SQL Server are run in cooperative mode, which means the thread is responsible for yielding so that other threads can obtain CPU time. Most IO is asynchronous.  The SQLOS is responsible for signaling threads when IO is completed.
2. Synchronization primitives: SQL server is a multi-threaded application, so SQLOS is responsible for managing thread synchronizations.
3. Memory management: Different components within SQL Server, example plan cache, CLR, lock manager etc request memory from the SQLOS. Therefore, the SQLOS can control how much memory a component within SQL Server is consuming.
4. Deadlock detection and management of the same.
5. Exception handling framework.
6. Hosting services for external components such as CLR and MDAC. SQL Server will run threads that are associated with external component in preemptive mode. Preemptive mode allows the SQLOS to prevents runaway threads (threads which will not yield and allow other threads to get CPU execution). Also the SQLOS can keep track of the memory these external components consume. For example, for CLR the SQLOS can invoke garbage collection if the CLR process is taking up too much memory.

As you can see, SQLOS seems to be a great resource management capability as it makes sure the SQL Server instance can be always up and running.

As I wrap up this blog, I am interested in knowing if you ever encountered SOS related waits on your environments? What were the values and what have you done to mitigate the same? Would like to learn from you on these outliers.

Reference : Pinal Dave (https://blog.sqlauthority.com)