MSDN Documentation

Data Files and Transaction Log Files

SQL Server databases are composed of two types of files:

• Primary Data Files (.mdf): Contain startup information for the database and pointers to other files. They also hold the actual data.
• Secondary Data Files (.ndf): Optional files that can be used to distribute data across multiple disks or file systems.
• Transaction Log Files (.ldf): Record all transactions and database modifications. They are crucial for recovery and replication.

Understanding filegroup management is essential for performance and storage optimization. Data can be organized into filegroups, allowing for granular control over data placement and I/O distribution.

Pages and Extents

The smallest unit of storage in SQL Server is a page, which is 8 KB in size. Pages are grouped into extents. An extent is a group of eight contiguous pages, totaling 64 KB.

• Heap Extents: Used for tables without a clustered index.
• Uniform Extents: All pages in the extent belong to the same object.
• Mixed Extents: Contain pages from multiple objects, typically used by smaller objects.

The storage engine efficiently manages allocation and deallocation of pages and extents to optimize disk space usage and access times.

Buffer Manager

The Buffer Manager is responsible for managing the data cache in memory (the buffer pool). It:

• Caches frequently accessed data pages in RAM to reduce disk I/O.
• Handles requests for data pages, checking if they are already in the buffer pool.
• If a page is not in memory, it reads it from disk into the buffer pool.
• Manages the eviction of pages from the buffer pool when memory is scarce.
• Ensures data consistency by writing modified pages (dirty pages) back to disk.

Effective buffer management is critical for overall database performance.

Lock Manager and Concurrency Control

The Storage Engine uses a sophisticated lock manager to ensure data consistency and integrity in a multi-user environment. It controls concurrent access to data by:

• Acquiring locks on data resources (pages, rows, tables) when they are accessed.
• Releasing locks when they are no longer needed.
• Detecting and resolving deadlocks.
• Supporting different lock types (shared, exclusive, update) to balance data protection and concurrency.

Understanding isolation levels and their impact on locking is essential for writing correct and performant queries.

Transaction Log Management

The transaction log is central to the Storage Engine's ability to guarantee ACID (Atomicity, Consistency, Isolation, Durability) properties for transactions.

• All data modifications are first written to the transaction log.
• This ensures that even if a system failure occurs, the database can be recovered to a consistent state.
• Log shipping and mirroring technologies rely on the transaction log for disaster recovery.

CREATE TABLE dbo.Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName NVARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL
);