Storage Engine Overview

The SQL Server Database Engine is responsible for managing and manipulating data. At its core, the storage engine handles the physical storage of data on disk and its retrieval into memory. This section provides an overview of the key components and concepts within the storage engine.

Key Components

Buffer Manager

The buffer manager is responsible for managing the buffer cache, which is the main memory area where data pages are read from and written to disk. It ensures that data pages are efficiently brought into memory and kept there for reuse, minimizing disk I/O operations. When data is requested, the buffer manager first checks the buffer cache. If the page is not found, it reads it from disk and places it in the cache. It also manages page replacement algorithms to free up space in the cache when it's full.

Lock Manager

The lock manager controls concurrent access to data by multiple users. It grants locks on data resources (like rows, pages, or tables) to prevent conflicts and ensure data integrity. Different types of locks exist (e.g., Shared, Exclusive, Update) with varying compatibility. The lock manager also detects and resolves deadlocks, which occur when a set of transactions are waiting for each other to release locks.

Transaction Manager

The transaction manager ensures that all operations within a transaction are atomic, consistent, isolated, and durable (ACID properties). It interacts with the logging system to record all changes made by a transaction. In case of system failures, the transaction manager uses the log to undo incomplete transactions and redo committed transactions.

Logging System (Write-Ahead Logging - WAL)

The logging system, often implemented using Write-Ahead Logging (WAL), records all modifications to the database before those modifications are written to the actual data pages on disk. These log records are written sequentially to the transaction log file. This ensures that even if a system crashes, the database can be recovered to a consistent state by replaying the transaction log.

Storage Engine APIs

The storage engine exposes a set of APIs that are used by other components of the Database Engine, such as the query processor, to interact with data. These APIs abstract away the complexities of physical data storage, allowing higher-level components to focus on logical data operations.

Data Storage Structures

Pages

Data in SQL Server is stored in fixed-size units called pages, which are typically 8 KB in size. Each page contains a header with metadata, a slot array for row pointers, and the actual data rows. Pages are the fundamental unit of I/O between disk and memory.

Extents

Extents are collections of eight contiguous pages. SQL Server uses extents to allocate space for data. There are two types of extents:

• Uniform Extents: All eight pages in a uniform extent are allocated to a single object (e.g., a table or index).
• Mixed Extents: A mixed extent can contain pages from multiple objects, typically used by smaller objects.

Tables and Indexes

Data is organized into tables. Indexes are data structures that improve the performance of data retrieval operations by providing a sorted order of data based on one or more columns. Common index types include clustered indexes (which define the physical storage order of the table) and non-clustered indexes (which are separate structures pointing to data rows).

Storage Engine Operations

Read Operations

When a query needs to access data, the storage engine first checks the buffer cache. If the required page is present, it's returned directly. If not, the buffer manager reads the page from disk into the buffer cache and then returns it.

Write Operations

When data is modified, the storage engine first writes a log record to the transaction log (Write-Ahead Logging). Then, the modified data page is marked as "dirty" in the buffer cache. Eventually, the SQL Server buffer manager writes these dirty pages back to disk.

Transaction Management

The storage engine plays a crucial role in transaction management by ensuring ACID properties. The transaction manager, in conjunction with the logging system and lock manager, guarantees that transactions are reliable and that the database remains in a consistent state, even in the face of concurrent access or system failures.