Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

LivingForSqlServer

I am Ramkumar, 34 years old Consultant, trainer, blogger and speaker at SQL Server user group in India. I have more than 10 years of experience as a developer and SQL Server DBA. I love reading, teaching and blogging about SQL Server internals and performance tuning. My Facebook page: https://www.facebook.com/LivingForSqlServer

Examining PFS, GAM and SGAM pages

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

 
Database size once its created:

Name

db_size

LearnSystemPages

1.68 MB

 1. Check space used by system objects created in empty database (with no user objects)

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

 Observation:

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
(total_pages of
sys.allocation_units)

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   

Comments

Posted by Anonymous on 13 February 2011

Pingback from  Twitter Trackbacks for                 SQL Server Central, Examining PFS, GAM and SGAM pages - LivingForSqlServer         [sqlservercentral.com]        on Topsy.com

Leave a Comment

Please register or log in to leave a comment.