Anyone who can interpret data and index pages will surely think about interpreting PFS, GAM and SGAM pages. I am no exception.
I thought of giving a try in a lazy weekend with little experience on examining data and index pages.
Agenda of this exercise is, to create a simple database and
1. Check space used by system objects in empty database (with no user objects)
2. Check content of PFS page (page 1:1) when a database is empty(with no user objects)
3. Try to interpret few bytes of GAM and SGAM
Note : I have used sql server 2005 instance for this exercise. Believe, there won’t be major difference in sql 2008.
To start with let’s create a test database named LearnSystemPages
create database LearnSystemPages
go
Name | db_size |
LearnSystemPages | 1.68 MB |
I have used below query to list all system objects and total pages allocated to each object.
-- Total pages used by system objects
select so.name, sp.index_id, sa.type_desc, sa.total_pages
from sys.objects so
inner join sys.partitions sp on so.object_id = sp.object_id
inner join sys.allocation_units sa on sa.container_id = sp.hobt_id
where sa.total_pages>0
order by so.name,sp.index_id
Output (formatted. Slno added for clarity):
Slno | name | index_id | type_desc | total_pages |
1 | sysallocunits | 1 | IN_ROW_DATA | 2 |
2 | sysbinobjs | 1 | IN_ROW_DATA | 2 |
3 | sysbinobjs | 2 | IN_ROW_DATA | 2 |
4 | sysclsobjs | 1 | IN_ROW_DATA | 2 |
5 | sysclsobjs | 2 | IN_ROW_DATA | 2 |
6 | syscolpars | 1 | IN_ROW_DATA | 16 |
7 | syscolpars | 2 | IN_ROW_DATA | 4 |
8 | sysdbfiles | 1 | IN_ROW_DATA | 2 |
9 | sysfiles1 | 0 | IN_ROW_DATA | 2 |
10 | syshobtcolumns | 1 | IN_ROW_DATA | 7 |
11 | syshobts | 1 | IN_ROW_DATA | 2 |
12 | sysidxstats | 1 | IN_ROW_DATA | 2 |
13 | sysidxstats | 2 | IN_ROW_DATA | 2 |
14 | sysiscols | 1 | IN_ROW_DATA | 2 |
15 | sysmultiobjrefs | 1 | IN_ROW_DATA | 2 |
16 | sysmultiobjrefs | 2 | IN_ROW_DATA | 2 |
17 | sysnsobjs | 1 | IN_ROW_DATA | 2 |
18 | sysnsobjs | 2 | IN_ROW_DATA | 2 |
19 | sysobjvalues | 1 | IN_ROW_DATA | 25 |
20 | sysobjvalues | 1 | LOB_DATA | 3 |
21 | sysowners | 1 | IN_ROW_DATA | 2 |
22 | sysowners | 2 | IN_ROW_DATA | 2 |
23 | sysowners | 3 | IN_ROW_DATA | 2 |
24 | sysprivs | 1 | IN_ROW_DATA | 2 |
25 | sysqnames | 1 | IN_ROW_DATA | 2 |
26 | sysqnames | 2 | IN_ROW_DATA | 2 |
27 | sysrowsetcolumns | 1 | IN_ROW_DATA | 7 |
28 | sysrowsets | 1 | IN_ROW_DATA | 2 |
29 | sysrts | 1 | IN_ROW_DATA | 2 |
30 | sysrts | 2 | IN_ROW_DATA | 2 |
31 | sysrts | 3 | IN_ROW_DATA | 2 |
32 | sysscalartypes | 1 | IN_ROW_DATA | 2 |
33 | sysscalartypes | 2 | IN_ROW_DATA | 2 |
34 | sysscalartypes | 3 | IN_ROW_DATA | 2 |
35 | sysschobjs | 1 | IN_ROW_DATA | 2 |
36 | sysschobjs | 2 | IN_ROW_DATA | 2 |
37 | sysschobjs | 3 | IN_ROW_DATA | 2 |
38 | sysschobjs | 4 | IN_ROW_DATA | 2 |
39 | sysserefs | 1 | IN_ROW_DATA | 2 |
40 | syssingleobjrefs | 1 | IN_ROW_DATA | 2 |
41 | syssingleobjrefs | 2 | IN_ROW_DATA | 2 |
42 | sysxmlcomponent | 1 | IN_ROW_DATA | 2 |
43 | sysxmlcomponent | 2 | IN_ROW_DATA | 2 |
44 | sysxmlfacet | 1 | IN_ROW_DATA | 2 |
45 | sysxmlplacement | 1 | IN_ROW_DATA | 2 |
46 | sysxmlplacement | 2 | IN_ROW_DATA | 2 |
| | | Total pages | 142 |
1. Total of 142 pages are utilized by 46 system object partitions.
2. This 142 pages includes IAM, data and index pages of all system objects
3. Key system pages like PFS, GAM, and SGAM are not included in this 142.
To understand more on page allocations, let’s examine output of PFS page.
As PFS can manage close to 8000 pages and the database size is 1.68 MB, I believe there will be only one PFS page in this database.
Here is the DBCC command to print PFS page content.
-- PFS Page. page number 1:1
DBCC TRACEON(3604)
DBCC PAGE('LearnSystemPages',1, 1,3) --WITH TABLERESULTS
DBCC TRACEOFF(3604)
PFS page (formatted)
PFS: Page Alloc Status @0x4B3DC000
(1:0) - (1:3) = ALLOCATED 100_PCT_FULL
(1:4) - (1:5) = NOT ALLOCATED 0_PCT_FULL
(1:6) - (1:7) = ALLOCATED 100_PCT_FULL
(1:8) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:9) - = ALLOCATED 100_PCT_FULL Mixed Ext
(1:10) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:11) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:12) - = ALLOCATED 100_PCT_FULL IAM Page Mixed Ext
(1:13) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:14) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:15) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:16) - (1:20) = ALLOCATED 0_PCT_FULL Mixed Ext
(1:21) - (1:22) = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:23) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:24) - (1:31) = ALLOCATED 0_PCT_FULL
(1:32) - = ALLOCATED 50_PCT_FULL Mixed Ext
(1:33) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:34) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:35) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:36) - (1:38) = ALLOCATED 0_PCT_FULL Mixed Ext
(1:39) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:40) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:41) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:42) - (1:44) = ALLOCATED 0_PCT_FULL Mixed Ext
(1:45) - = ALLOCATED 50_PCT_FULL Mixed Ext
(1:46) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:47) - = ALLOCATED 100_PCT_FULL Mixed Ext
(1:48) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:49) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:50) - (1:55) = ALLOCATED 0_PCT_FULL Mixed Ext
(1:56) - = ALLOCATED 0_PCT_FULL
(1:57) - = NOT ALLOCATED 0_PCT_FULL Mixed Ext
(1:58) - = NOT ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:59) - (1:61) = NOT ALLOCATED 0_PCT_FULL Mixed Ext
(1:62) - = NOT ALLOCATED 0_PCT_FULL Has Ghost Mixed Ext
(1:63) - = NOT ALLOCATED 0_PCT_FULL Mixed Ext
(1:64) - (1:70) = ALLOCATED 0_PCT_FULL Mixed Ext
(1:71) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:72) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:73) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:74) - (1:75) = ALLOCATED 0_PCT_FULL Mixed Ext
(1:76) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:77) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:78) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:79) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:80) - (1:81) = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:82) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:83) - (1:84) = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:85) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:86) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:87) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:88) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:89) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:90) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:91) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:92) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:93) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:94) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:95) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:96) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:97) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:98) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:99) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:100) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:101) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:102) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:103) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:104) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:105) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:106) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:107) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:108) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:109) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:110) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:111) - (1:116) = ALLOCATED 0_PCT_FULL Mixed Ext
(1:117) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:118) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:119) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:120) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:121) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:122) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:123) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:124) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:125) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:126) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:127) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:128) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:129) - (1:131) = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:132) - (1:134) = ALLOCATED 0_PCT_FULL Mixed Ext
(1:135) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:136) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:137) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:138) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:139) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:140) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:141) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:142) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:143) - = NOT ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:144) - (1:145) = ALLOCATED 0_PCT_FULL
(1:146) - (1:151) = NOT ALLOCATED 0_PCT_FULL Mixed Ext
Observation:
1. Total of 152 (1:0 to 1:151) pages are reported by PFS
2. As per previous observation, 142 pages used for system pages
3. First 8 pages (1:0 to 1:7) are used for system pages like GAM, SGAM (including unused pages 1:4 and 1:5)
While balancing total pages reported by PFS against system and data pages, I got some difference.
| Pages | Comments |
Pages reported by PFS | 152 | |
Allocated Pages | 137 | |
Unallocated pages | 15 | (including pages 1:4 and 1:5) |
System pages | 6 | (Header page, PFS, GAM, SGAM, BCM and DCM) |
Pages allocated for system objects | 142 | |
Total used pages | 148 | |
| | |
Difference b/w PFS allocated pages (137) vs. used pages (148) | 11 | |
I don’t have the answer for this difference right now. Leaving this to experts like Paul to clarify.
With this experience, let Us move on to understand how these 152 pages are managed by GAM and SGAM.
Recollecting details about GAM and SGAM
1. Both are Bitmap pages, can manage upto 64000 extents or 4 GB
2. 1 bit for each extent to tell about its type and free space
3. One set of GAM and SGAM covers 4 GB data space.
Here is a table from Paul on how GAM, SGAM and IAM are used to gether for extent management.
GAM | SGAM | Any IAM | Comments |
0 | 0 | 0 | Mixed extent with all pages allocated |
0 | 0 | 1 | Dedicated extent (must be allocated to only a single IAM page) |
0 | 1 | 0 | Mixed extent with >= 1 unallocated page |
0 | 1 | 1 | Invalid state |
1 | 0 | 0 | Unallocated extent |
1 | 0 | 1 | Invalid state |
1 | 1 | 0 | Invalid state |
1 | 1 | 1 | Invalid state |
Reference: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/07/08/under-the-covers-gam-sgam-and-pfs-pages.aspx?wa=wsignin1.0
lets print both GAM and SGAM pages and analyze the difference between both pages
GAM page:
Here is DBCC command to print GAM page (1:2) content
-- GAM page (1:2)
DBCC TRACEON(3604)
DBCC PAGE('LearnSystemPages',1, 2,1) --WITH TABLERESULTS
DBCC TRACEOFF(3604)
Output (filtered):
Slot 0, Offset 0x60, Length 94, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes =
Memory Dump @0x02C8C060
00000000: 00005e00 00000000 00000000 00000000 †..^.............
00000010: 00000000 00000000 00000000 00000000 †................
00000020: 00000000 00000000 00000000 00000000 †................
00000030: 00000000 00000000 00000000 00000000 †................
00000040: 00000000 00000000 00000000 00000000 †................
00000050: 00000000 00000000 00000000 0000††††††..............
Slot 1, Offset 0xbe, Length 7992, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes =
Memory Dump @0x02C8C0BE
00000000: 0000381f 0000f8ff ffffffff ffffffff †..8.............
00000010: ffffffff ffffffff ffffffff ffffffff †................
00000020: ffffffff ffffffff ffffffff ffffffff †................
00000030: ffffffff ffffffff ffffffff ffffffff †................
00000040: ffffffff ffffffff ffffffff ffffffff †................
00000050: ffffffff ffffffff ffffffff ffffffff †................
00000060: ffffffff ffffffff ffffffff ffffffff †................
00000070: ffffffff ffffffff ffffffff ffffffff †................
00000080: ffffffff ffffffff ffffffff ffffffff †................
00000090: ffffffff ffffffff ffffffff ffffffff †................
000000A0: ffffffff ffffffff ffffffff ffffffff †................
000000B0: ffffffff ffffffff ffffffff ffffffff †................
000000C0: ffffffff ffffffff ffffffff ffffffff †................
000000D0: ffffffff ffffffff ffffffff ffffffff †................
000000E0: ffffffff ffffffff ffffffff ffffffff †................
000000F0: ffffffff ffffffff ffffffff ffffffff †................
00000100: ffffffff ffffffff ffffffff ffffffff †................
00000110: ffffffff ffffffff ffffffff ffffffff †................
00000120: ffffffff ffffffff ffffffff ffffffff †................
00000130: ffffffff ffffffff ffffffff ffffffff †................
00000140: ffffffff ffffffff ffffffff ffffffff †................
00000150: ffffffff ffffffff ffffffff ffffffff †................
00000160: ffffffff ffffffff ffffffff ffffffff †................
00000170: ffffffff ffffffff ffffffff ffffffff †................
00000180: ffffffff ffffffff ffffffff ffffffff †................
00000090: ffffffff ffffffff ffffffff ffffffff †................
000000A0: ffffffff ffffffff ffffffff ffffffff †................
000000B0: ffffffff ffffffff ffffffff ffffffff †................
000000C0: ffffffff ffffffff ffffffff ffffffff †................
000000D0: ffffffff ffffffff ffffffff ffffffff †................
000000E0: ffffffff ffffffff ffffffff ffffffff †................
000000F0: ffffffff ffffffff ffffffff ffffffff †................
00000100: ffffffff ffffffff ffffffff ffffffff †................
00000110: ffffffff ffffffff ffffffff ffffffff †................
00000120: ffffffff ffffffff ffffffff ffffffff †................
00000130: ffffffff ffffffff ffffffff ffffffff †................
00000140: ffffffff ffffffff ffffffff ffffffff †................
00000150: ffffffff ffffffff ffffffff ffffffff †................
00000160: ffffffff ffffffff ffffffff ffffffff †................
00000170: ffffffff ffffffff ffffffff ffffffff †................
00000180: ffffffff ffffffff ffffffff ffffffff †................
00000190: ffffffff ffffffff ffffffff ffffffff †................
000001A0: ffffffff ffffffff ffffffff ffffffff †................
000001B0: ffffffff ffffffff ffffffff ffffffff †................
000001C0: ffffffff ffffffff ffffffff ffffffff †................
000001D0: ffffffff ffffffff ffffffff ffffffff †................
000001E0: ffffffff ffffffff ffffffff ffffffff †................
000001F0: ffffffff ffffffff ffffffff ffffffff †................
00000200: ffffffff ffffffff ffffffff ffffffff †................
00000210: ffffffff ffffffff ffffffff ffffffff †................
00000220: ffffffff ffffffff ffffffff ffffffff †................
00000230: ffffffff ffffffff ffffffff ffffffff †................
00000240: ffffffff ffffffff ffffffff ffffffff †................
00000250: ffffffff ffffffff ffffffff ffffffff †................
00000260: ffffffff ffffffff ffffffff ffffffff †................
00000270: ffffffff ffffffff ffffffff ffffffff †................
00000280: ffffffff ffffffff ffffffff ffffffff †................
00000290: ffffffff ffffffff ffffffff ffffffff †................
000002A0: ffffffff ffffffff ffffffff ffffffff †................
000002B0: ffffffff ffffffff ffffffff ffffffff †................
000002C0: ffffffff ffffffff ffffffff ffffffff †................
000002D0: ffffffff ffffffff ffffffff ffffffff †................
000002E0: ffffffff ffffffff ffffffff ffffffff †................
000002F0: ffffffff ffffffff ffffffff ffffffff †................
00000300: ffffffff ffffffff ffffffff ffffffff †................
00000310: ffffffff ffffffff ffffffff ffffffff †................
00000320: ffffffff ffffffff ffffffff ffffffff †................
00000330: ffffffff ffffffff ffffffff ffffffff †................
00000340: ffffffff ffffffff ffffffff ffffffff †................
00000350: ffffffff ffffffff ffffffff ffffffff †................
…
…
SGAM page
-- SGAM page (1:3)
DBCC TRACEON(3604)
DBCC PAGE('LearnSystemPages',1, 3,1) --WITH TABLERESULTS
DBCC TRACEOFF(3604)
SGAM Output (filtered):
Slot 0, Offset 0x60, Length 94, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes =
Memory Dump @0x4B2DC060
00000000: 00005e00 00000000 00000000 00000000 †..^.............
00000010: 00000000 00000000 00000000 00000000 †................
00000020: 00000000 00000000 00000000 00000000 †................
00000030: 00000000 00000000 00000000 00000000 †................
00000040: 00000000 00000000 00000000 00000000 †................
00000050: 00000000 00000000 00000000 0000††††††..............
Slot 1, Offset 0xbe, Length 7992, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes =
Memory Dump @0x4B2DC0BE
00000000: 0000381f 00000200 00000000 00000000 †..8.............
00000010: 00000000 00000000 00000000 00000000 †................
00000020: 00000000 00000000 00000000 00000000 †................
00000030: 00000000 00000000 00000000 00000000 †................
00000040: 00000000 00000000 00000000 00000000 †................
00000050: 00000000 00000000 00000000 00000000 †................
00000060: 00000000 00000000 00000000 00000000 †................
00000070: 00000000 00000000 00000000 00000000 †................
00000080: 00000000 00000000 00000000 00000000 †................
00000090: 00000000 00000000 00000000 00000000 †................
000000A0: 00000000 00000000 00000000 00000000 †................
000000B0: 00000000 00000000 00000000 00000000 †................
000000C0: 00000000 00000000 00000000 00000000 †................
000000D0: 00000000 00000000 00000000 00000000 †................
000000E0: 00000000 00000000 00000000 00000000 †................
000000F0: 00000000 00000000 00000000 00000000 †................
00000100: 00000000 00000000 00000000 00000000 †................
00000110: 00000000 00000000 00000000 00000000 †................
00000120: 00000000 00000000 00000000 00000000 †................
00000130: 00000000 00000000 00000000 00000000 †................
00000140: 00000000 00000000 00000000 00000000 †................
00000150: 00000000 00000000 00000000 00000000 †................
00000160: 00000000 00000000 00000000 00000000 †................
00000170: 00000000 00000000 00000000 00000000 †................
00000180: 00000000 00000000 00000000 00000000 †................
00000190: 00000000 00000000 00000000 00000000 †................
000001A0: 00000000 00000000 00000000 00000000 †................
000001B0: 00000000 00000000 00000000 00000000 †................
000001C0: 00000000 00000000 00000000 00000000 †................
000001D0: 00000000 00000000 00000000 00000000 †................
000001E0: 00000000 00000000 00000000 00000000 †................
000001F0: 00000000 00000000 00000000 00000000 †................
00000200: 00000000 00000000 00000000 00000000 †................
00000210: 00000000 00000000 00000000 00000000 †................
00000220: 00000000 00000000 00000000 00000000 †................
00000230: 00000000 00000000 00000000 00000000 †................
00000240: 00000000 00000000 00000000 00000000 †................
00000250: 00000000 00000000 00000000 00000000 †................
00000260: 00000000 00000000 00000000 00000000 †................
00000270: 00000000 00000000 00000000 00000000 †................
00000280: 00000000 00000000 00000000 00000000 †................
00000290: 00000000 00000000 00000000 00000000 †................
000002A0: 00000000 00000000 00000000 00000000 †................
000002B0: 00000000 00000000 00000000 00000000 †................
000002C0: 00000000 00000000 00000000 00000000 †................
000002D0: 00000000 00000000 00000000 00000000 †................
...
Observation:
1. First 4 bytes in Slot 0 and Slot 1 of both GAM and SGAM looks similar
Slot 0 of both GAM and SGAM starts with 00005e00
Slot 1 of both GAM and SGAM starts with 0000381f
while cross checking first 4 bytes of GAM/SGAM in other databases, I ‘ve confirmed that, Slot 0 and 1 of both GAM and SGAM starts with 00005e00 and 0000381f respectively.
Seem this first 4 bytes in 2 slots of GAM and SGAM are used for internal purpose.
2. Slot 0 of both GAM and SGAM look similar and most of the bitmap values are 0
00000000: 00005e00 00000000 00000000 00000000 †..^.............
00000010: 00000000 00000000 00000000 00000000 †................
00000020: 00000000 00000000 00000000 00000000 †................
00000030: 00000000 00000000 00000000 00000000 †................
00000040: 00000000 00000000 00000000 00000000 †................
00000050: 00000000 00000000 00000000 0000††††††..............
…
…
Meaning of 0 in both GAM and SGAM:
GAM | SGAM | Any IAM | Comments |
0 | 0 | 0 | Mixed extent with all pages allocated |
as per Pauls table, if bit is 0 in both GAM and SGAM, that indicates, respective extent is mixed extent.
3. Slot 1 of GAM is filled with ffffffff (all 1 in binary) and
Slot 1 of SGAM is filled with 00000000 (all 0 in binary)
Meaning of the combination of 1 in GAM and 0 in SGAM :
GAM | SGAM | Any IAM | Comments |
1 | 0 | 0 | Unallocated extent |
As per Pauls table, combination of bit 1 in GAM and 0 in SGAM indicates the respective extent is unallocated.
In this example, most of the extents are unallocated and to be used by user/system objects later.
4. First row in slot 1 of GAM is
00000000: 0000381f 0000f8ff ffffffff ffffffff †..8.............
and First row in slot 1 of SGAM is,
00000000: 0000381f 00000200 00000000 00000000 †..8.............
Interpretation:
0000381f is common for both GAM and SGAM. Could be system specific information
Remaining piece:
Only difference found between both GAM and SGAM (in empty database) is second byte in slot 1 of GAM and SGAM are different.
GAM : 00 00 f8 ff
SGAM : 00 00 02 00
With little experience of data page interpretation, lets byte swap both values and convert into binary.
(is this the way to interpret GAM/SGAM bytes? Experts can confirm)
GAM : 00 00 f8 ff
Bitmap : 11111111 11111000 00000000 00000000
SGAM : 00 00 02 00
Bitmap : 00000000 00000010 00000000 00000000
Lets use Pauls table as a quick reference to interpret meaning of different bit combinations
GAM | SGAM | Any IAM | Comments |
0 | 0 | 0 | Mixed extent with all pages allocated |
0 | 1 | 0 | Mixed extent with >= 1 unallocated page |
1 | 0 | 0 | Unallocated extent |
0 in both GAM and SGAM indicates mixed extent is full.
0 in GAM and 1 in SGAM indicates mixed extent have some free pages for allocation
1 in GAM and 0 in SGAM indicates unallocated extent
Conclusion:
At this moment, I cant say that I have leart interpretation of GAM and SGAM but this exercise made me comfortable for further analysis in this area.
Reference:
1. MS Press SQL Server 2008 Internals
2. Paul’s Blogs
