Physical Database Architecture

Database Files

              SQL Server maps a DB to a set of OS files visible to SQL Server. We can create 32767 files in a DB.

First page (Page 0) of every DB file is FHP file header page.

Second page (Page 1) of every DB file PFS (page free space) page. PFS uses 1 byte for each page. This byte keeps track of whether the page is allocated, whether it is empty, and, if it is not empty, how full the page is. 1 PFS keep track of 8000 contiguous pages. 

Third page (Page 2) of every DB file GAM (Global Allocation Map)page. GAM keep track of allocated extents. Each GAM page keep track of 64000 extents. It contain 1 bit for each extents which is set to 0 if the extent is allocated to an object and 1 if the  extent is free.

Forth page (Page 3) of every DB file S-GAM (Secondary-Global Allocation Map). S-GAM keep track of allocated mixed-extents. Each S-GAM keep track of 64000 mixed-extents. A bit set to 1 for an extents indicates to mixed-extent with page available.