SQL Server Storage Architecture Explained: How SQL Server Stores Data Internally

 

Introduction

Have you ever wondered what happens when you insert a row into a SQL Server table?

Where does the data actually go?

How does SQL Server find free space?

How are indexes stored?

Why do DBAs talk about Pages, Extents, GAM, SGAM, PFS, and IAM?

Understanding SQL Server Storage Architecture is one of the most important skills for any DBA, Database Architect, or Performance Engineer because almost every performance issue eventually leads back to how data is stored internally.

In this article, we will simplify SQL Server's internal storage mechanism using practical examples.

Why Storage Architecture Matters

Understanding storage internals helps you:

✅ Troubleshoot performance issues

✅ Understand index fragmentation

✅ Optimize large tables

✅ Reduce disk I/O

✅ Improve backup and restore strategies

✅ Analyze space usage

✅ Perform advanced DBA troubleshooting

The Foundation: Data Pages

SQL Server stores all data in Pages.

A Page is the smallest unit of storage.

Page Size - 1 Page = 8 KB

Every database is built using thousands or millions of pages.

Imagine a book.

• Book = Database
• Chapters = Tables
• Pages = SQL Server Data Pages

Data Page Structure

What the Header Stores

• Page ID
• Object ID
• Free Space Information
• Next Page Pointer

SQL Server uses these details to locate data quickly.

Extents: A Group of Pages

Managing individual pages would be inefficient.

Instead SQL Server groups pages together.

1 Extent = 8 Pages

Since each page is 8 KB: 8 × 8 KB = 64 KB

Hence 1 Extent = 64 KB

Visual representation of an extent:

Extent

├─ Page 1

├─ Page 2

├─ Page 3

├─ Page 4

├─ Page 5

├─ Page 6

├─ Page 7

└─ Page 8

Types of Extents

Uniform Extent

All 8 pages belong to the same object.

Table A

├─ Page1

├─ Page2

├─ Page3

...

 └─ Page8

Page1 → TableA

How SQL Server Finds Free Space

Now comes the interesting part.

How does SQL Server know where free pages exist?

The answer is:

Allocation Maps

These are special pages maintained by SQL Server.

PFS Page (Page Free Space)

PFS keeps track of:

• Free space on pages
• Empty pages
• Allocated pages

Think of it as: "Space Availability Register"

When new data arrives: INSERT INTO Customers

SQL Server first checks PFS.

GAM (Global Allocation Map)

GAM tracks extents that are completely free.

Free Extent ?

YES → Available

NO → Occupied

Think of GAM as: "Free Land Registry"

SGAM (Shared Global Allocation Map)

Tracks mixed extents that still have free pages.

Mixed Extent + Free Pages Available

Used mostly for small objects.

IAM (Index Allocation Map)

IAM connects tables and indexes to physical pages.

Without IAM SQL Server cannot locate table data.

Visualization:

Customer Table

│

▼

IAM Page

│

▼

Page 100

Page 101

Page 102

 Page 103

Think of IAM as: Google Maps for SQL Server

How an Insert Really Works

Suppose:

INSERT INTO Employee VALUES (1001,'Ajit')

SQL Server performs:

Step 1

Check PFS - Any page with free space?

Step 2

If none available:

Check GAM - Any free extent available?

Step 3

Allocate new extent

Step 4

Update IAM

Step 5

Store Row

Step 6

Commit Transaction

All this happens in milliseconds.

Real DBA Scenario

Imagine a table containing: 500 Million Rows

If pages become fragmented:

• More disk reads
• More I/O
• Slower queries

This is why DBAs run: ALTER INDEX REBUILD or ALTER INDEX REORGANIZE

Regular maintenance improves page organization and performance.

Key Interview Questions

Q1. What is a Page in SQL Server?

Smallest storage unit of size 8 KB.

Q2. What is an Extent?

Group of 8 Pages. (Size 64 KB)

Q3. What does GAM track?

Free extents.

Q4. What does SGAM track?

Mixed extents with available pages.

Q5. What does IAM do?

Maps database objects to physical pages.

Q6. What does PFS track?

Page allocation and free space.

Summary

SQL Server stores data in a highly organized structure:

Database

▼

File

▼

Extent (64 KB)

▼

Page (8 KB)

▼

Rows

SQL Server then uses:

• PFS
• GAM
• SGAM
• IAM

to manage storage efficiently.

Understanding these concepts will make performance tuning, capacity planning, and troubleshooting significantly easier.