Internals

sql server internal architecture

Advertisements

 

Database Physical Structure & Internals:

SQL server internals Allocation Map is important for DBAs. It will help to understand the database internals.

Database:

A Database is a collection of data that is stored (or) organized. We can manage and access data easily.
When we create a new database, by default two files will be created physically.
We can create more than one log files and data files (NDF).

  • Data file (MDF)
  • Log file (LDF)

Data File:
All the data will be stored in a data file. Internally the data’s are stored in data pages.

A database file has unique FileID and PageID.

Log files do not have pages. They have series of log records. I will cover the Log file section in my next article.

Data Page:

The data page is a fundamental unit of data storage. Each data page has 8KB size (128 pages per MB). Each page has 96 byte header system information.

 

All the data rows are stored in data page. Expect BLOB (text, ntext, image, xml, varchar (max), nvarchar & varbinary)

Extents:

Extents are fundamental units in which space is allocated to table (or) an index. Extents are 8 contiguous pages 64KB size. (16 extents per MB).

Two Types of extents

  • Uniform/Dedicated extents
  • Mixed extents

 

Uniform Extents:

Uniform extents are owned by a single object. All the 8-pages can be used to owning object.

 

Mixed Extents:

When we create a new table (or) an index of default pages are allocated to mixed extents first.

When the data grow and its reach 8 pages then it switched to uniform extents.

 

How the Data Storage Size Internally For Page & Extents:

 
Storage Size Page Extents
Size per KB 8KB(1-Page) 64KB (8-Pages)
Size per MB 128 Pages 16 Extents
 
 

IAM: (Index Allocation Map)
IAM page tracks with which extents within the specified GAM interval belongs to a single entity (or) file. A Database file has multiple files some of the DB size more than 4 GB

 

IAM interval size is 64000 extents i.e. 4GB

 

GAM: (Global Allocation Map)

Data files split up into GAM intervals (Conceptually)

Bit –>1 Free

An extent is available for allocation.

Bit –> 0 Allocated

An extent is already allocated.

 

GAM interval size is 64000 extents i.e. 4GB

 

SGAM: (Shared Global Allocation Map)

SGAM bitmaps are exactly same as GAM.

Bit –> 1 mixed extent one (or) more free page available

An extent is mixed one (or) more unallocated page available.

Bit –> 0 Either Uniform/Mixed extent No unallocated pages

An extent is either uniform (or) mixed all are already allocated.

 

SGAM interval size is 64000 extents i.e. 4GB

PFS: (Page Free Space)
PFS has tracks with which pages have free space.

 

A PFS page is the first page after the file header page in a data file (with page number 1).
Next comes a GAM (with page number 2) followed by an SGAM (page 3).

PFS interval size is 8088 pages i.e. 64MB

PFS does not have bitmap only have byte map.
DCM: (Differential Changed Map)

DCM tracks extents that have changed since the last BACKUP DATABASE statement.

Bit –> 1 extent modified

Extent has been modified since the last BACKUP DATABASE.

Bit –> 0 extents is not modified

Extent has not been modified since the last BACKUP DATABASE.

 

BCM: (Bulk Changed Map)

BCM tracks extents that have been modified by bulk operations since the last BACKUP LOG statement.

Bit –> 1 extents modified

Extent has been modified by a bulk logged operation after the last BACKUP LOG statement.

Bit –> 0 extent is not modified

Extent has not been modified by bulk logged operations .Although the BCM pages appear by default in all databases.

 

How the Data Stored/Allocated Internally for the entity:

(Allocation Maps)

 
Type of storage

(Bit Type)

GAM
SGAM
DCM
BCM
Bit 1 Extent Free Mixed Extent at least one free pages available Extent modified Extent modified
Bit 0 Extent Allocated Either Uniform (or) Mixed No free space Extent not modified
Extent not modified




 
 
Referred links :
 



hacklink panel
hacklink al
hacklink
sollet
hdxvipizle
venusbet
deneme bonusu veren siteler
deneme bonusu veren siteler
jojobet
matbet
Betkanyon
Bahisal Giriş
Bahisal
Winxbet
Deneme Bonusu Veren Siteler
Unibahis Giriş
Unibahis
En iyi bahis siteleri
marsbahis”>
Bahis Siteleri
En yi Casino Siteleri
Canlı Casino Siteleri
Casino Siteleri
Canlı Bahis Siteleri
Canlı Casino Siteleri
asyabahisgo1.com
betforward1.org
backlink al
1xbet güncel giriş
bahis forum
seo paneli

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

5 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

6 + 1 =