SQL Server Storage Architecture II

In continuation with previous post,

The page size is 8kb and 96 bytes are reserved for page header and data rows will be stored after the header and rows can stored in sequential or any order. 

Page Header (96 bytes)
Data Rows
RowOffset –(36 bytes) It contains address of all records that are stored in data page.

When we use DBCC IND command it displays the pageIDs, we can see what actually is stored on those pages using DBCC Page command. 

DBCC Page (CurrentDBID, FileID, PageID, PrintOptions)

CurrentDBID - Zero (0) refers current database ID

FileID - If you have more than one MDF file to database then we need to specify that file ID, the default is 1 for mdf file

PageId - Get the page Id from DBCC IND command

PrintOption - 0– Only Page header information will be displayed

-1 – Page header plus row hex dumps and dump of page slot array
-2 – Page header and whole page hex dump
-3— Page header and detailed per row interpretation

dbcc TRACEON (3604)
go
dbcc ind('Credit','testPages',1)
go
dbcc page(0,1,3453,3) 
dbcc page(0,1,3453,3) with tableresults -- displays result in tabular format

Extents – The SQL Server doesn’t manage space at the level of page (as 8kb is very little space) so it uses EXTENTS, Extents are the higher level unit which space is managed; an extent is eight continuous pages or 64kb. 16 extents for MB. Initially the space will allocated page by page but once it reach size more than 1 extent then it starts allocating extent by extent not page by page. There are two types of extents

 1. Uniform Extents -- Extent belongs to one particular object (one table)

 2. Mixed Extents -- Extent shared across multiple objects (table1, table2, Index4)