Monday, August 26, 2013

SQL Server Storage Architecture III


In continuation with previous part

Database consists of one ore more data files, and each file is divided into extents and each extent is divided into pages (8 pages). The storage unit is extent in the file, SQL Server has to know what are the free extents available or on which extent it has free pages etc., those information will be managed by again three different pages.

For any database file first 4 pages 1) PageHeader, 2)GAM, 3)SGAM, and 4)PFS are fixed. Using those pages SQL Server will came to know free pages, free extents etc.,


GAM – Global Allocation Map

The GAM page has a flag what are the available extents. When the first time file is created it breaks into extents and creates GAM Page and marks all the flags as empty. If you start using then it marks flags as reserved. One GAM Page will hold information about 64000 extents ie., approximately 4GB, In other words for 7GB data file it maintains 2 GAM Pages. The status of GAM shown as below

In simple words GAM and SGAM will helps database engine to identify free pages and free extents. This helps in extent management. In any data file first page (page 0 is header page), second page (page 1 is PFS), third page (page no 2 is GAM) and forth page (page no 3 is SGAM).

To check the GAM Allocation Status on GAM Page, Earlier we discussed about DBCC PAGE command which shows information about the pages


DBCC PAGE (0, 1, 2, 3) -- 0 –Indicates current database, 1-Indicates FileId, 2-Indicates PageNumber( GAM Page number is fixed) 3 –Indicated displays complete information

 GAM : Extent Allocation Status

(1:0) - (1:20) - Allocated
(1:22) – (1:44) - Not Allocated

The above status indicates in file Id-1, 0 to 20 are allocation and 22 to 44 are not allocated.
SGAM – Shared Global Allocation Map

The SGAM has a flag which represents where the free pages are available in mixed extents. Like GAM it also holds information about 64000 extents ie., approximately 4GB, In other words for 7GB data file it maintains 2 SGAM Pages.

To view the SGAM Allocation status
DBCC PAGE (0, 1, 3, 3) -- 0 –Indicates current database, 1-Indicates FileId, 2-Indicates PageNumber( SGAM Page number is fixed) 3 –Indicated displays complete information

SGAM – Extent Allocation Status

 (1:0) - (1:3448) = NOT ALLOCATED                            
 (1:3456) - (1:3464) = ALLOCATED                               
 (1:3472) - (1:19952) = NOT ALLOCATED                               

The above not allocated mixed extents has one free page and which is Allocated doesn’t have any free pages

PFS -- Page free space

It records allocation status of each page whether the page has been allocated or how much free percentage of space left on that page. Every PFS page covers 8088 pages. It is the 1st page in datafile.

No comments: