The SQL Server Operating System (SQLOS)

The SQL Server Operating System (SQLOS) is a separate application layer at the lowest level of the SQL Server Database Engine that both SQL Server and SQL Reporting Services run atop.

Earlier versions of SQL Server have a thin layer of interfaces between the storage engine and the actual operating system through which SQL Server makes calls to the operating system for memory allocation, scheduler resources, thread and worker management, and synchronization objects. However, the services in SQL Server that needed to access these interfaces can be in any part of the engine. SQL Server requirements for managing memory, schedulers, synchronization objects, and so forth have become more complex. Rather than each part of the engine growing to support the increased functionality, a single application layer has been designed to manage all operating system resources that are specific to SQL Server.

The two main functions of SQLOS are scheduling and memory management. Other functions of SQLOS include the following:

Synchronization

Synchronization objects include spinlocks, mutexes, and special reader/ writer locks on system resources.

Memory Brokers

Memory brokers distribute memory allocation between various components within SQL Server, but do not perform any allocations, which are handled by the Memory Manager.

SQL Server Exception Handling

Exception handling involves dealing with user errors as well as system-generated errors.

Deadlock Detection

The deadlock detection mechanism doesn’t just involve locks, but checks for any tasks holding onto resources, that are mutually blocking each other.

Extended Events

Tracking extended events is similar to the SQL Trace capability, but is much more effi cient because the tracking runs at a much lower level than SQL Trace. In addition, because the extended event layer is so low and deep, there are many more types of events that can be tracked. The SQL Server 2008 Resource Governor manages resource usage using extended events.

Asynchronous IO

The difference between asynchronous and synchronous is what part of the system is actually waiting for an unavailable resource. When SQL Server requests a synchronous I/O, if the resource is not available the Windows kernel will put the thread on a wait queue until the resource becomes available. For asynchronous I/O, SQL Server requests that Windows initiate an I/O. Windows starts the I/O operation and doesn’t stop the thread from running. SQL Server will then place the server session in an I/O wait queue until it gets the signal from Windows that the resource is available.

CLR hosting

Hosting Common Language Runtime (CLR) inside the SQLOS allows managed .NET code to be used natively inside SQL Server. In SQL Server 2012, CLR hosting changed to .NET 4.0, which includes changes to memory reporting and garbage collection for AppDomains loaded by SQLOS. Memory allocations for SQLCLR can include any-page allocations inside SQLOS as well as virtual committed allocations from the Windows operating system.

Final Thoughts

SQLOS was built for the SQL Server 2005 release. Back then, SQL Server was a Windows-only application, so SQLOS was never intended to provide platform independence or portability. It was not created to help porting the database engine to other operating systems. There was no need for a PAL.