About MSSQL Server
Tuesday, March 17th, 2009 | Author:

The architecture of Microsoft MSSQL Server is mainly divided into three components:

1. SQLOS: which implements the basic services required by MS SQL Server, including thread scheduling, I/O stat management and memory management.

2. Relational Engine: which implements the relational database components including support for databases, tables, queries and stored procedures as well as implementing the type system.

3. Protocol Layer: which exposes the MS SQL Server functionality.

SQLOS

SQLOS is the base component in the Windows SQL Server architecture. It implements functions normally associated with the Operating System, thread scheduling, memory management, I/O management, buffer pool management, resource management, synchronization primitives and locking, and deadlock detection. Because the requirements of Windows SQL Server are highly specialized, it implements its own memory and thread management system, rather than using the generic one implemented in the OS. SQLOS also includes synchronization primitives for locking as well as monitoring for the worker threads to detect and recover from deadlocks.

SQLOS handles the memory requirements of MS SQL Server as well. Reducing disc I/O is one of the primary goals of specialized memory management in SQL Server. It maintains a buffer pool, which is used to cache data pages from the disc, and to satisfy the memory requirements for the query processor, and for other internal data structures. SQLOS monitors all the memory allocated from the buffer pool, ensuring that the components return unused memory to the pool, and shuffles data out of the cache to make room for newer data. For changes that are made to the data in buffer, SQLOS writes the data back to the disc lazily, that is when the disc subsystem is either free, or there have been significant numbers of changes made to the cache, while still serving requests from the cache. For this, it implements a Lazy Writer, which handles the task of writing the data back to persistent storage.

WIndows SQL Server normally supports up to 2 GB memory on x86 hardware, though it can be configured to use up to 64 GB if the Address Windowing Extension is used in the supporting operating system. For x64 hardware, it supports 8 TB of memory, and 7 TB for IA-64 systems (currently it is limited by Windows Server 2003 SP1 to 1TB). However, when running x86 versions of SQL Server on x64 hardware, it can access 4 GB of memory without any special configuration.

Relational Engine:

The Relational engine implements the relational data store using the capabilities provided by SQLOS, which is exposed to this layer via the private SQLOS API. It implements the type system, to define the types of the data that can be stored in the tables, as well as the different types of data items (such as tables, indexes, logs etc) that can be stored. It includes the Storage Engine, which handles the way data is stored on persistent storage devices and provides methods for fast access to the data. The storage engine implements log-based transaction to ensure that any changes to the data are ACID compliant. It also includes the query processor, which is the component that retrieves data. MSSQL queries specify what data to retrieve, and the query processor optimizes and translates the query into the sequence of operations needed to retrieve the data. The operations are then performed by worker threads, which are scheduled for execution by SQLOS.

Protocol Layer:

Protocol layer implements the external interface to MS SQL Server. All operations that can be invoked on MSSQL Server are communicated to it via a Microsoft-defined format, called Tabular Data Stream (TDS). TDS is an application layer protocol, used to transfer data between a database server and a client. Initially designed and developed by Sybase Inc. for their Sybase MS SQL Server relational database engine in 1984, and later by Microsoft in Microsoft MS SQL Server, TDS packets can be encased in other physical transport dependent protocols, including TCP/IP, Named pipes, and Shared memory. Consequently, access to MSSQL Server is available over these protocols. In addition, the MSSQL Server API is also exposed over web services.