3. SQL Server Storage Architecture

In an SQL server all the data will be stored in the form of records, these records also called row data. All these records further grouped into a page. The page is a default storage unit of the SQL server. The size of the page is 8kb.

Page Architecture

The page consists of 3 sections

1. Page Header
2. Actual Data
3. Row offset array

Page Header – It consists of Page ID, Page Type, Object ID Header version.

Page ID – To identify a particular page using a unique page ID.

Page Type – What type of page it is either data page or Index page.

In Row the offset location of the record will be stored (2 bytes).

Types of Pages

1. Data Page – stores data entered by user.
2. Index Page – Indexes are pointer which store address of original pages for quickly locating data
3. Free space page – It stores page allocation information and unused space available on pages.
4. Text/Image – It stores large object data (LOB) like Text, Image and XML Data.
5. GAM (Global Allocation Map) or SGAM (Shared Global Allocation Map) – It stores extent allocation information.
6. BCM (Bulk Changed Map) – Stores extents information in a Bulk Operation
7. DCM (Differential Change Map) – It stores modified extents information after Full BackUp.
8. I AM (Index Allocation Map) – Stores extents information that are used by a table (or) Index.
9. These are important types of pages. All these pages are further grouped into an Extent.

SQL Server Extents

Extent is a storage structure consists of 8 consecutive SQL Server pages. Pages in a Extent can be one table (or) upto Eight tables.

There are 2 types of Extents

1. Uniform Extent: If all pages are going to store same table data
2. Mixed Extent: If the pages shared by 2 (or) more tables.

When a table is created and a row is inserted table gets 1 page in mixed extent, when a table grows then these tables moved to a uniform extent. This is to manage space efficiently.

SQL Server File

All the extents further group into a File. A file we will have better control in SQL Server.

There are 2 types of files mainly,

1. MDF (Master Data File)
2. LDF (Log Data File)

MDF – Stores Permanent Data

LDF – Stores changes information will be recorded later these changes apply on MDF Data.

Database

Files combine to form the database. We require a minimum 2 files 1 MF and 1 LDF to create a database. Maximum we can ‘n’ number of files means No limit.

File Groups

Some files stored system data and some store user database data. Logically dividing databases into groups called File Groups.

Storage Structure

Records > Pages > Extents > Files > Database

#Ref. Ram Mohan & Rama Krishna notes