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
nice article,
ReplyDelete-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
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
ReplyDeleteHi Dhiva:
ReplyDeleteThis 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