Monday 23 May 2011

How the Data Stores in SQL Server

Data Page

 

Data pages are the fundamental area to store data in SQL server.

 

The data pages contains

  1. Pages header
  2. Data Row
  3. Row Offset or pointers.

 

The maximum size of a data page is 8 KB

 

Extents

 

Extents are basic units in which page are managed. An extents are 8 physical continuous pages, that is 8*8=64 KB.

 

SQL Server has two types of extents:

·             Uniform extents are owned by a single object; all eight pages in the extent can   only be used by the owning object.

·             Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

Type of Data Pages

The following table shows the page types used in the data files of a SQL Server database.

Page type

Contents

Data

Data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON.

Index

Index entries.

Text/Image

Large object data types:

·             text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data

Variable length columns when the data row exceeds 8 KB:

·             varchar, nvarchar, varbinary, and sql_variant

Global Allocation Map, Shared Global Allocation Map

Information about whether extents are allocated.

Page Free Space

Information about page allocation and free space available on pages.

Index Allocation Map

Information about extents used by a table or index per allocation unit.

Bulk Changed Map

Information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit.

Differential Changed Map

Information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.

 

 

 

Published By : Mr. Joydeep Das

 

No comments:

Post a Comment