SQL Server Storage Internals: Technical Documentation

1. Storage Hierarchy Overview

SQL Server organizes data in a strict hierarchy. To the Operating System, SQL Server is just reading and writing files. However, internally, it manages a complex filing system.

The Hierarchy:

Instance → Database → Filegroups → Files → Extends → Pages → Records

---

2. Physical File Architecture

Every database consists of at least two physical operating system files.

A. The Data Files

These files store the actual data (table rows, index data).

• Primary Data File (.mdf): The starting point of the database. It contains pointers to other files and system tables. Every database has exactly one.

• Secondary Data Files (.ndf): Optional. Used to spread data across multiple physical disks for performance (Striping).

B. The Log File (.ldf)

• Stores the Transaction Log.

• Structure: Unlike data files (which are random access), the Log File is circular and sequential.

• Function: It records every modification (DML/DDL) for recovery purposes. It does not contain "tables"; it contains "Log Records" (Virtual Log Files - VLFs).

C. Filegroups

A logical container for physical files.

• PRIMARY Filegroup: Contains the .mdf file and system tables.

• User-Defined Filegroups: Allows administrators to segregate "Hot Data" (fast SSD) from "Archive Data" (slow HDD) by assigning specific tables to specific filegroups.

---

3. The Fundamental Unit: The Page

The Page is the atomic unit of I/O in SQL Server. The engine never reads a single row; it reads a Page.

• Size: Fixed at 8 KB (8192 bytes).

• Capacity: SQL Server can fit approximately 8060 bytes of actual data per page (after headers).

Page Anatomy

1. Page Header (96 bytes): Stores system info (PageID, object it belongs to, Previous/Next page pointers, free space count).

2. Data Rows: The actual data records are stored serially after the header.

3. Free Space: The empty gap in the middle.

4. Row Offset Array (Slot Array): Located at the very end of the page. It stores a list of pointers (2 bytes each) indicating exactly where each row starts on the page. It is read backwards (Right-to-Left).

Note: If a row exceeds 8KB (e.g., a large text field), SQL Server moves that column to a separate "Row-Overflow" or "LOB" page and leaves a pointer in the original row.

---

4. The Allocation Unit: The Extent

To manage space efficiently, SQL Server does not allocate pages one by one. It allocates them in groups called Extents.

• Definition: A collection of 8 contiguous Pages.

• Size: 8x8 KB = 64KB

• Types:

  • Uniform Extent: All 8 pages belong to a single object (e.g., Table A).

  • Mixed Extent: Pages are shared by different objects (used for very small tables to save space).

---

5. Data Organization Structures

How pages are linked together determines how fast data is retrieved.

A. The Heap (Unordered)

• Definition: A table with no Clustered Index.

• Structure: Data pages are not linked logically. There is no order.

• Lookup Method: IAM (Index Allocation Map) pages track which extents belong to the table. To find a row, SQL must scan every page defined in the IAM.

• Addressing: Rows are identified by a RID (FileID : PageID : SlotNum).

B. The Clustered Index (B-Tree)

• Definition: The data is the index.

• Structure: A Balanced Tree structure.

  • Root Node: The entry point.

  • Intermediate Levels: Navigation nodes.

  • Leaf Nodes: The actual Data Pages.

• Ordering: Pages are doubly linked (Previous/Next pointers), allowing fast sequential scans (e.g., WHERE ID > 100).

---

6. Space Management (The Hidden System)

How does SQL Server know which page has free space without scanning the whole file? It uses special bitmap pages:

1. GAM (Global Allocation Map): Tracks which extents are allocated vs. free. One bit represents one extent.

2. SGAM (Shared Global Allocation Map): Tracks Mixed Extents that have free pages available.

3. PFS (Page Free Space): Tracks how full an individual page is (Empty, 50%, 80%, Full).

---

7. Transaction Management (I/O Architecture)

This explains the movement of data between Disk and RAM.

A. Write-Ahead Logging (WAL) Protocol

1. Modification: Data is changed in the Buffer Pool (RAM).

2. Log Flush: The details of the change are written to the .ldf file synchronously.

3. Dirty Page: The data page in RAM is now "Dirty" (unsaved to disk).

4. Checkpoint: A background process flushes Dirty Pages to the .mdf file to create a "Clean" persistence point.

B. The Lazy Writer vs. Checkpoint

• Checkpoint: Focuses on recovery time. It saves data so that if the server crashes, the replay time is short.

• Lazy Writer: Focuses on RAM pressure. If RAM is full, the Lazy Writer flushes old dirty pages to disk to free up memory for new operations.

---

8. Data Consistency & Protection

A. Page Checksum

To detect Bit Rot or Torn Pages (hardware corruption):

• When writing a page, SQL Server calculates a checksum of the 8KB binary.

• It writes this value into the Page Header.

• When reading the page back later, it recalculates the checksum. If they don't match, it raises Error 824.

B. Recovery (ARIES)

On restart, the storage engine ensures consistency:

1. Analysis: Which transactions were active?

2. Redo: Replay all committed data from .ldf to RAM/Disk.

3. Undo: Rollback uncommitted data.

---

Summary Table