SQLOS provides user-level operating system services (hence the name “SQLOS”) to the rest of the server. Components in the SQL Server Engine make use of the services provided by SQLOS to schedule individual or multiple tasks, allocate memory, and so forth.
In the past, scalability was the major issue in SQL Server because it supported only small or medium-sized workloads due to its engine capabilities. With this issue, Microsoft had to make massive changes to SQL server to serve the demands of the large workload
The key observation here is that DBMS and OS schedulers must work together. Either OS must have built-in support for DBMS or DBMS must have a special scheduling layer.
If I summarized the SQLOS:
- Application Layer between SQL Server components and the OS.
- Provides more refined control of resource allocation.
- Centralizes resource allocation to provide more efficient management and accounting.
- Abstracts the concepts of resource management from components such as the Query Engine & Query Optimizer.
SQLOS currently consists of the following components:
- Scheduling subsystem
- Memory management
- Error/exception handling
- Deadlock detection
- Hosting external components
The SQLOS was created to centralize common low-level tasks within the SQL Server process. Having a central location for these tasks means less duplication of code within the various components of the engine, but it also offers the flexibility to adjust SQL Server to new and advanced hardware architectures without impacting the other areas of SQL Server code.
The SQLOS behaves very much like an operating system. It abstracts the concept of memory management, I/O, scheduling, etc. from the other components within the SQL engine. In this way, these components do not need to worry about managing things like NUMA and Resource Governor, they simply make resource allocation calls to the SQLOS via an API.
The SQL engine is still a process like any other process running on a Windows server. It does not have any special privileges or priority over other processes. The SQLOS does not bypass Windows, it simply manages the resources within the SQL Server process space in an efficient way for SQL Server.
The two main functions of SQLOS are scheduling and memory management. Other functions of SQLOS include the following.
- Synchronization – This object type includes spinlocks, mutexes (mutual exclusions), and special reader/writer locks on system resources.
- Memory brokers – Memory brokers distribute memory allocation between various components within SQL Server but don’t 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. Chapter 13 covers deadlocks involving locks (by far the most common kind).
- Extended Events – Tracking extended events is similar to the SQL Trace capability but is much more efficient because the tracking runs at a much lower level than SQL Trace. Also, because the Extended Event layer is so low and deep, many more types of events can be tracked.
- Asynchronous I/O – The difference between asynchronous and synchronous is that part of the system is actually waiting for an unavailable resource. When SQL Server requests a synchronous I/O, the Windows kernel puts the thread on a wait queue until the resource becomes available if the resource isn’t 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 then places 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.