7.9.08

Page Vs Extent in sql server

Extent Vs Page?
Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.
The size of the page is 8KB.
Eight consecutive pages will form an extent 8 * 8KB = 64KB. Thus I/O level operation will be happening at pages level.
The pages will hold a template information at the start of each page (header of the page). They are,
1. page number,
2. page type,
3. the amount of free space on the page,
4. the allocation unit ID of the object that owns the page.

Extents will be classifed into two types,

1. Uniform extents
2. Mixed extents

Uniform Extents:
It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.
Mixed Extents:
Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.

Property of SQL Server :
Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.
Herecomes, our fragmentation and reindexing concepts.
Regards,
Venkatesan Prabu .J

3 comments:

  1. nice article,
    -Pages and Extents Architecture
    -Files and Filegroups Architecture
    -Transaction Log Architecture
    -Tables and Index Data Structures
    -Query Processing Architecture
    -Memory Management Architecture
    -Thread and Task Architecture

    ReplyDelete
  2. hi venky, could you pls blog regarding IAM,GAM,SGAM & the above topics in detail with examples which will clear the concepts more clear and use full for newbie like me .thanks in advance.Dhiva

    ReplyDelete
  3. Hi Dhiva:
    This is a great article by Brent Ozar about differnt types of pages. Please take a look.
    http://sqlserverpedia.com/wiki/Data_Pages_&_Extents

    Thank,
    Abi

    ReplyDelete