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: