The SQL Server Database Engine is actually made up of three distinct parts that work together to process queries. These are the SQLOS, the Relational Engine, and the Storage Engine.
When a query is processed, it is passed to the database engine or more specifically, the Relational Engine. This is where the query is processed and an execution plan is created. The Storage Engine uses the execution plan to determine how data will be accessed from the database, specifically which indexes and data structures will be used to retrieve data.
For both the Relational and Storage engines to operate they need resources from the Operating System and this is where the SQLOS component of the database engine comes into play. The SQLOS is used to monitor and manage resources and performs the following tasks:
- Thread Scheduling: When a task needs access to the CPU, SQLOS will schedule worker threads to complete those tasks in a cooperative mode, meaning threads could yield CPU access.
- Memory Management: There are many components within SQL Server that need memory; such as the procedure cache, buffer cache, and the lock manager. SQLOS is responsible for controlling how much memory each component is consuming.
- Transaction Management: Transactions are used when data or objects are updated in the database. The SQLOS uses a combination of components like the Buffer Pool, Lock Manager, and Deadlock Monitor to process these transactions.
- Exception Handling: When there is a syntax or runtime error, the SQLOS is responsible for handling those exceptions.
- Locking Framework: The SQLOS will control concurrency access data to control transaction isolation by using locks so that competing transactions do not interfere with each other.
- Deadlock Detection: SQLOS is used to track down and resolve deadlock issues.