Pages : In SQL Server the data is stored in pages.In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.
There are Eight Kinds of Pages in SQL Server
Data pages : In Data pages all user data is stored
Index Pages : Index entries will stored
Text/Image Pages : Large object data types like text,ntext,image,varbinary(max),nvarbinary(max) and xml.
GAM & SGAM : Information about whether extents are allocated.
PFS pages : Information about free space available on pages.
IAM pages : Information about extents used by a table or Index per allocation unit.
Bulk Changed Map : Extents modified by Bulk operations since the last backup log statement.
Differential changed Map : Extents that have changed since the last backup database statement.
Extents : Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.
There are two types of Extents
Uniform : They are owned by a single object; all eight pages in the extent can only be used by the owning object.
Mixed : Multiple objects will use same extent. Each of the eight pages in the extent can be owned by a different object.
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.
No comments:
Post a Comment