SQL Server Storage Engine (part 1) – SQL Server files and pages

I had been planning to complete this blog for a few weeks now,  finally it’s here my first blog & first in the series of ”SQL Server Data storage Engine”.

Let me start with SQL server files, then get into the pages and finally seeing all of them in action.

SQL server data base files
One of the primary part of the SQL Server database engine is the Data storage.

When we create a new database, by default one primary data file (.mdf) and a log file (.ldf) gets created.

We can optionally create secondary data files (.ndf) in addition to primary data file. Secondary data files contain only transactional data and does not contain meta data information like the primary data files.

We can specify the initial size of the file and specific autogrowth (in increments of percentage or megabyte) while creating a new database.

Please note that the file can use the complete available disk space if maximum size is not specified.

Data file (.mdf or .ndf) is logically divided into contiguously numbered (from 0 to n) pages.

Log files (.ldf) contains a series of records (not divided into pages), used to log changes made into the tables.

Pages:
Pages are the fundamental unit of storage in SQL server.  IO operations are performed at the page level.

Each page is of 8KB size (128 pages per MB), out of which 96 bytes is used as page header and 8060 for data rows.  Page header stores metadata information of a page like the page number,  file number, page type, count of records,  free space, ghostrecords  and allocation status of the page.

Data rows is stored serially between the page header and the offset array. Offset array contains one entry (a two byte pointer) for each row, ordered in the reverse sequence. Offset array gets reshuffled every time a record is inserted and deleted from the page ensuring the right sequence is maintained for the recorded rows.

Data rows cannot spill over to multiple pages, the maximum amount of data that can be stored in a single row on  a page is 8060 bytes. Tables can have columns with large variable length data type (varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type) which are stored on the same data pages. If the total size of the row exceeds 8060 bytes, the column with the largest width will be dynamically moved to row_overflow_data allocation unit. This is dynamically done whenever there is an inserts or update operation resulting in row size increasing beyond 8060 bytes. Clustered index cannot be created on a column with variable length whose data is on  row_overflow_data allocation unit.

Based on the type of data SQL server stores data in different types of pages. In addition to storing data; information required for managing data like free space, tracking extents, allocating new pages and extents are also stored in pages. Listed are different page types and the content they store.

Type	Content
File Header Page	Stores metadata info about the file. File Header page is the first page (page 0) in every data file. File header stores information like bindingid, filegroupid, size, maxsize, growth, status, etc. If the File header page of the primary file is corrupt, the only way to restore the database is through the backup.
Page Free Space (PFS)	Stores information on space availability of each pages. One byte for each page. There will be one PFS page for every 8000 pages
Global Allocation Map (GAM)	Stores information if the extent is allocated or not (bit is 1 if it’s free; 0 if it’s allocated). Each GAM approximately covers upto 64,000 extents (4GB of data file).
Shared Global Allocation Map (SGAM)	Stores information if the extents is a mixed extent or not (bit is 1 if the extent is used as a mixed extent and has atleast one free data page; bit is 0 if the extent is not a mixed extent or if the mixed extent is full). Each SGAM can cover upto 64,000 extents (4GB of data file)
Index Allocation Map (IAM)	Stores information if the extents is used by specific table/index within a single allocation unit. Each IAM can cover upto 64,000 extents (4GB of data file).
Differential Change Map (DCM)	Stores information about all extents that have been modified since the last full database backup was taken, per allocation unit. Differential backups scan the DCM pages and copy only those extents that were altered since the last full backup making the differential database backups considerably faster than full database backups.
Bulk Change Map (BCM)	Stores information about which extents were altered by Bulk Insert or Bulk Copy Program (BCP) per allocation unit. BCM only tracks the extents modified since the last BACKUP LOG statement. BCM pages are only relevant for databases that use bulk-logged recovery model because with this model BACKUP LOG statement includes the modified extents in the log backup. If the database uses the simple recovery model, then bulk operations aren’t logged.
Boot Page	Stores critical information about the file. Boot page stores information like dbi_status, dbi_dbname, dbi_version, dbi_createVersionm, dbi_dbccLastKnownGood, dbi_LastLogBackupTime, dbi_differentialBaseGuid. If the Boot page of the file is corrupt, the only way to restore the database is through the backup.
Data Page	Stores data rows except for data types stored in Text and Image page type
Index Page	Stores Index data
Text and Image page	Stores text, ntext, image, nvarchar(max), varchar(max), varbinary(max) and xml data. Text and Image pages also store variable length columns (varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type) that exceeds the row size limit of 8060 bytes

Sequence of pages in physical file

Seeing in action:
Let’s create a sample data base without specifying the file names and size
- – Make sure we are in Master database
use Master
go
- – create sample database sql_trial
create database sql_trial
go

Query to verify the created sample data base
select database_id as [Database ID], file_id as [File ID], type as [File Type], type_desc as [File Type Description], name as [Logical Name], state as [File State], size as [Size], size*1.0/128 as [Size in MB]
from sys.master_files
where DB_NAME(database_id) = ‘sql_trial’

We can see the database (sql_trial) created with the mdf file in the same name as specified for the database and a log file to store the log information for the database. The result also lists the file type (type: 0 = Rows; 1 = Log; 2 = filestream) and File State (state: 0 = Online; 1 = Restoring; 2 = Recovering; 3 = Recovery_Pending; 4 = Suspect; 6 = Offine).

Now let’s create a new database by specifying file names, location, size, maxsize and growth
use Master
go
- – create sample database sql_trial
create database sql_trial
on primary
(name=’sqltrial_Primary’,
filename=’C:\SQL\…\sqltrial_Primary.mdf’,
size=2MB,
maxsize=4MB,
filegrowth=1MB)
log on
(name = sqltrial_Log,
filename = ‘C:\SQL\…\sqltrial_Log.ldf’,
size = 3MB,
maxsize = 5MB,
filegrowth = 1MB)

Query to verify the created sample data base
select database_id as [Database ID], file_id as [File ID], type as [File Type], type_desc as [File Type Description], name as [Logical Name], state as [File State], size as [Size], size*1.0/128 as [Size in MB]
from sys.master_files
where DB_NAME(database_id) = ‘sql_trial’

The results lists the names of the files, size of the fields as mentioned in the sample.

Now lets create a scenario to see the row-overflow.
Please note – I have used two undocumented and unsupported commands dbcc ind and dbcc page. dbcc ind command can be used to get information about the pages associated.
Syntax: dbcc ind(database, table, index)
dbcc page can be used to look into the contents of file pages.
Syntax: dbcc page ({dbname | dbid}, filenum, pagenum, printopt={0|1|2|3})
Where 0 – print only the page header; 1 – print page header plus per-row hex dumps and a dump of the page slot array; 2 – print page header plus whole page hex dump; 3 – print page header plus detailed per-row interpretation.

Let’s create a sample table in sql_trial database with two nvarchar(max) columns.
use sql_trial
go
create table ut_trial1(id nchar(10), sname nchar(50), saddress nvarchar(max), paddress nvarchar(max)) on [primary]

Populate the table with values(length not crossing the 8000 bytes size)
use sql_trial
go
insert into ut_trial1 values(1,’VJ’, REPLICATE (‘a’, 50), REPLICATE (‘a’, 50))

Query – dbcc ind command to check the details
use sql_trial
go
- – turn on the trace flag 3604 to get the results to your current connection
dbcc traceon (3604)
dbcc ind (‘sql_trial’, ‘ut_trial1′, 1)
The result shows two pages are added an index page and a data page (page type: 1 – Data page, 10 – IAM page).

Populate the table with data > 8060 bytes
use sql_trial
go
insert into ut_trial1 values(2,’VJ1′, REPLICATE (‘b’, 9000), REPLICATE (‘b’, 50))

Query – dbcc ind command to check the details
use trial
go
dbcc traceon (3604)
dbcc ind (‘sql_trial’, ‘ut_trial1′, 1)
If you notice the iam_chain_type has changed from in-row data to LOB data and the page type has changed to 3 (3/4 page type – Text pages). Which means the new row is moved to the new page 154 (LOB allocation unit).

Let’s look into some page details using the dbcc page command
use sql_trial
Go
dbcc traceon (3604)
dbcc page (‘sql_trial’,1,152,0)

Query result:
PAGE: (1:152)
(Removed the buffer information)

PAGE HEADER:

Page @0x05B4A000

m_pageId = (1:152) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0×4 m_level = 0 m_flagBits = 0×8008
m_objId (AllocUnitId.idObj) = 67 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594042318848
Metadata: PartitionId = 72057594038321152 Metadata: IndexId = 0
Metadata: ObjectId = 2073058421 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 124 m_slotCnt = 1 m_freeCnt = 8094
m_freeData = 1733 m_reservedCnt = 333 m_lsn = (22:397:2)
m_xactReserved = 333 m_xdesId = (0:657) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = NOT ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0×20 MIXED_EXT 0_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
Page header provides some useful information about the page meta data like m_type – type of page (page type values like 1 – data page, 2 – index page, 3 – text mix page, 4 – tree page, 7 – sort page, 11 – PFS page, 13 – boot page, 15 – file header page), m_slotcnt – count of records on the page, m_freecnt – bytes of free space on the page, m_ghostRecCnt – count of ghost records.

Let’s look into different page types using dbcc page.
- – look into the contents of the File Header page which is the at the page 0 in the file
use sql_trial
GO
dbcc page (‘sql_trial’, 1, 0, 3)

Query result:
PAGE: (1:0)

File Header Data:

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
BindingID = 1beb12c4-5383-4e52-ba8e-e2e00c862620 FileGroupId = 1
FileIdProp = 1 Size = 256 MaxSize = 512
Growth = 128 Perf = 0 BackupLsn = (0:0:0)
MaxLsn = (0:0:0) FirstLsn = (0:0:0) OldestRestoredLsn = (0:0:0)
FirstUpdateLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL] CreateLsn = (0:0:0)
DifferentialBaseLsn = (0:0:0) DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000
MinSize = 256 Status = 0
Listed result (limiting only to file header specific information) provides the attributes of the file stored in the file header page. Attributes of the file like FileIdProp (value is 1 if the file id is in sys.masterfiles), growth (if the file growth is in percent the number is the percentage; if the file growth is in Megabytes the number is in number of pages) & other attribute details.

Similarly you can use the dbcc page command to look into other pages
- -dbcc page to look into the contents of the PFS page which is the at the page 1 in the file.
use sql_trial
GO
dbcc page(‘sql_trial’, 1, 1, 3)

- -dbcc page to look into the contents of the GAM page which is the at the page 2 in the file.
use sql_trial
GO
dbcc page(‘sql_trial’, 1, 2, 3)

- -dbcc page to look into the contents of the SGAM page which is the at the page 3 in the file.
use sql_trial
GO
dbcc page(‘sql_trial’, 1, 3, 3)

- -dbcc page to look into the contents of the DCM page which is the at the page 6 in the file.
use sql_trial
GO
dbcc page(‘sql_trial’, 1, 6, 3)

- -dbcc page to look into the contents of the BCM page which is the at the page 7 in the file.
use sql_trial
GO
dbcc page(‘sql_trial’, 1, 7, 3)

- -dbcc page to look into the contents of the Boot page which is the at the page 9 in the file.
use sql_trial
GO
dbcc page(‘sql_trial’, 1, 9, 3)