This article is contributed. See the original author and article here.

I’m going to start a series of posts talking about SQL Server allocation pages and corruption. Each post will have samples showing how SQL Server use these pages and scenarios of corruption.

The first post will talk about the Extents, PFS, GAM, SGAM and IAM and related corruptions, you can find all concepts from following two pages:

Pages and Extents Architecture Guide

Under the covers: GAM, SGAM, and PFS pages

 

As there are many concepts and samples, I’m going to discuss the topics in two or three posts.

 

1.Extents

Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

SQL Server has two types of extents:

·         Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.

·         Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

Up to, and including, SQL Server 2014 (12.x), SQL Server does not allocate whole extents to tables with small amounts of data. A new table or index generally allocates pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents. However, starting with SQL Server 2016 (13.x), the default for all allocations in the database is uniform extents.

 

Sample 1: The first 8 pages are stored in mixed extent.

1)Create an empty database in SQL Server 2019 instance and enable the mixed extent allocation.

create database dbtest

go

alter database dbtest set MIXED_PAGE_ALLOCATION on

2)Create a heap table and insert one row.

create table heaptable1(c1 int, c2 varchar(8000))

insert heaptable1 values(1,REPLICATE(‘a’,1000))    

 

3)Let’s check how these rows and pages stored in SQL Server.

select ht1.c1,ht1.c2, p.file_id,p.page_id, is_mixed_page_allocation

From heaptable1 as ht1 CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) as p inner join sys.dm_db_database_page_allocations(db_id(),object_id(‘dbo.heaptable1′),null,null,’detailed’) as dddpa

on p.file_id=dddpa.allocated_page_file_id and

p.page_id=dddpa.allocated_page_page_id

Liwei_0-1598128693202.png

 

Because there is only 1 row, in one page, it’s stored in mixed extent.

The page id is:(1:305).

 

2.PFS

Page Free Space (PFS) pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page. The PFS has 1-byte for each page, recording whether the page is allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full, or 96 to 100 percent full.

After an extent has been allocated to an object, the SQL Server Database Engine uses the PFS pages to record which pages in the extent are allocated or free. This information is used when the SQL Server Database Engine has to allocate a new page. The amount of free space in a page is only maintained for heap and Text/Image pages. It is used when the SQL Server Database Engine has to find a page with free space available to hold a newly inserted row. Indexes do not require that the page free space be tracked, because the point at which to insert a new row is set by the index key values.

A new PFS, GAM or SGAM page is added in the data file for every additional range that it keeps track of. Thus, there is a new PFS page 8,088 pages after the first PFS page, and additional PFS pages in subsequent 8,088 page intervals. To illustrate, page ID 1 is a PFS page, page ID 8088 is a PFS page, page ID 16176 is a PFS page, and so on. There is a new GAM page 64,000 extents after the first GAM page and it keeps track of the 64,000-extents following it; the sequence continues at 64,000-extent intervals. Similarly, there is a new SGAM page 64,000 extents after the first SGAM page and additional SGAM pages in subsequent 64,000 extent intervals. The following illustration shows the sequence of pages used by the SQL Server Database Engine to allocate and manage extents.

 

Sample 2: PFS page detail, how does SQL Server map the value ‘1’,’2’,’3’,’4’ to ’50 Percent full’,’80 Percent full’,’95 Percent full’ and ‘100 Percent full’.

1)The first PFS page is (1:1).

2)Run DBCC PAGE to display the content:

Liwei_1-1598128693225.png

 

As the size of row is 1KB, the usage of the page is around 1000.0/8096=12%, so it’s marked as 50 percent full.

Let’s display the content again using the parameter 1 instead of 3.

Liwei_2-1598128693233.png

 

The value is 61.

3)Let’s keep inserting another 4 rows.

insert heaptable1 values(2,REPLICATE(‘b’,1000))    

insert heaptable1 values(3,REPLICATE(‘c’,1000))    

insert heaptable1 values(4,REPLICATE(‘d’,1000))    

insert heaptable1 values(5,REPLICATE(‘e’,1000))  

4)The total size of 5 rows are around 5KB, the usage is around 5000.0/8096=61%, which is greater than 50% , but less than 80%, so it’s marked as 80 percent full.

Liwei_3-1598128693227.png

 

Let’s display the content again using the parameter 1 instead of 3.

Liwei_4-1598128693235.png

 

The value is 62.

 

5)Let’s keep inserting another 2 rows.

insert heaptable1 values(6,REPLICATE(‘f’,1000))    

insert heaptable1 values(7,REPLICATE(‘g’,1000))

 

6)The total size of 7 rows are around 7KB, the usage is around 7000.0/8096=86%, which is greater than 80% , but less than 85%, so it’s marked as 95 percent full.

Liwei_5-1598128693228.png

 

Let’s display the content again using the parameter 1 instead of 3.

Liwei_6-1598128693236.png

 

The value is 63.

Takeaway: Because SQL Server stop storing data in a page of heap when it’s 95%, you won’t see ‘100 percent full’ even if you continue inserting a row with size 1KB.

8)For 100 percent full, please see sample 9.

 

 

3.GAM

Global Allocation Map (GAM)
GAM pages record what extents have been allocated. Each GAM covers 64,000 extents, or almost 4 gigabytes (GB) of data. The GAM has 1-bit for each extent in the interval it covers. If the bit is 1, the extent is free; if the bit is 0, the extent is allocated, either in GAM or SGAM.

Sample 3: GAM explore

1)Let’s check the GAM.

Liwei_7-1598128693211.png

 

2)What does the entry ‘(1:0)        – (1:304)      =     ALLOCATED’ stand for?

    (1:0)   is the first page of first extent in this range.

    (1:304) is the first page of last extent in this range. (1:311) is the last page of this extent.

     All the extents within the range, including the first extent and last extent, are allocated as mixed extent or uniformed extent. So do the page(1:0)~page(1:311) are in allocated.

Takeaway: By check GAM page, we can tell if the extents and pages are allocated, but we can’ tell if they are mixed extent or uniformed extent.

 

4.SGAM

Shared Global Allocation Map (SGAM)
SGAM pages record which extents are currently being used as mixed extents and also have at least one unused page. Each SGAM covers 64,000 extents, or almost 4-GB of data. The SGAM has 1-bit for each extent in the interval it covers. If the bit is 1, the extent is being used as a mixed extent and has a free page. If the bit is 0, the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used.

Sample 4: SGAM explore

Let’s check the GAM.

Liwei_8-1598128693212.png

 

2)What does the first entry ‘(1:0)        – (1:296)      = NOT ALLOCATED’ stand for?

   (1:0)   is the first page of first extent in this range.

   (1:296) is the first page of last extent in this range. (1:303) is the last page in this extent.

   Please note: ‘NOT ALLOCATED’ is confusing, you see the ‘NOT ALLOCATED’ when the corresponding bit is 0: the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used. Please see the Sample 7 for more detail.

 

3)What does the second entry ‘(1:304)                    =     ALLOCATED’ stand for?

  (1:304)   is the first page of first extent in this range.

  At least this extent is allocated as mixed extent. So do the page(1:304)~(1:311).

 

4)What does the second entry ‘(1:312)      – (1:1016)     = NOT ALLOCATED’ stand for?

    (1:304)   is the first page of first extent in this range.

    (1:1016) is the first page of last extent in this range.

    All the extents within the range, including the first extent and last extent, are not allocated as mixed extent. So do the page(1:304)~page(1:1023).

 

Takeaway:

1) SGAM only shows a part of mixed extents, not all of them. Please see the Sample 7 for more detail.

2)‘NOT ALLOCATED’ is confusing, you see the ‘NOT ALLOCATED’ when the corresponding bit is 0: the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used. Please see the Sample 7 for more detail.

 

 

 

Sample 5: GAM detail

Let’s review the GAM page from different perspective.

Run DBCC PAGE again with the parameter 1 instead of 3.

Liwei_9-1598128693228.png

 

The 0000381f are reserved, the valid record starts from the fifth byte:00

Let’s talk a look at 00000000 80

It’s hex, Let me convert to binary:

Hex

00         00         00         00         80

Binary(little-endian):

0000`0000  0000`0000  0000`0000  0000`0000  1000`0000

Binary(normal :(

0000`0000  0000`0000  0000`0000  0000`0000  0000`0001

If the bit is 1, the extent is free; if the bit is 0, the extent is allocated, either in GAM or SGAM.

Because the bit of the extent 0~38(totally 39 extents) are all 0, are allocated, either in GAM or SGAM. so does the page 0~311(304+7).

The bit extent 39 is 1, so extent 39 is not allocated. The related page (1:312) is not allocated at all. As the rest of bits/bytes are 1,(f stands for 1111)

It exactly matches the content of DBCC Page with parameter 3.

 

Liwei_10-1598128693213.png

 

Sample 6: SGAM detail

Let’s review the SGAM page from a different perspective.

Run DBCC PAGE again , but with the parameter 1 instead 3.

Liwei_11-1598128693229.png

 

The 0000381f are reserved, the really record starts from the fifth byte:00

Let’s talk a look at 00000000 80

It’s hex, now convert to binary:

Hex

00        00        00        00        40

Binary(little-endian):

00000000  00000000  00000000  00000000  01000000

Binary(normal :(

00000000  00000000  00000000  00000000  00000010

Because the bit of the extent 0~37(totally 38 extents) are all 0, these extents are not used as a mixed extent, or it is a mixed extent and all its pages are being used.

The pages in extent 0~37 are (1:0)~(1:303).

The first page of extent 37 is (1:296).

The bit of extent 38 is 1, so the extent is used as a mixed extent, so does the page(1:304)~page(1:311)

It exactly matches the content of DBCC Page with parameter 3.

Liwei_12-1598128693215.png

 

Takeaway: You can tell if the extent is allocated by checking the GAM and SGAM page, but you can’t tell if it’s mixed extent or uniformed extent. You need to combine the info with IAM page. I’m going to cover the topic in next post.

 

Sample 7: Insert more rows to make the heaptable1 has 8 pages.

1)Insert another 7 rows, the size of each row is around 8KB.

insert heaptable1 values(8,REPLICATE(‘i’,8000))      –page 2

insert heaptable1 values(9,REPLICATE(‘i’,8000))      –page 3

insert heaptable1 values(10,REPLICATE(‘j’,8000))     –page 4

insert heaptable1 values(11,REPLICATE(‘k’,8000))     –page 5

insert heaptable1 values(12,REPLICATE(‘l’,8000))     –page 6

insert heaptable1 values(13,REPLICATE(‘m’,8000))     –page 7

insert heaptable1 values(14,REPLICATE(‘n’,8000))     –page 8

2)There are 8 pages now.

Liwei_13-1598128693230.png

 

3)Extent and page type

select allocated_page_file_id as [FileID],allocated_page_page_id as [PageID],page_type_desc,extent_page_id/8 as ExtentID, is_mixed_page_allocation,

extent_page_id as [First Page in Extent],extent_page_id+7 as [LastPage in Extent],is_allocated

From  sys.dm_db_database_page_allocations(db_id(),object_id(‘dbo.heaptable1′),null,null,’detailed’)  order by allocated_page_page_id

Liwei_14-1598128693238.png

 

These pages belong to two different mixed extents, which are expected.

4)GAM also reflects the change.

Liwei_15-1598128693217.png

 

5)Let’s check the SGAM

Liwei_16-1598128693218.png‘(1:320)                    =     ALLOCATED’ is marked as mixed extent, which makes sense.

However, we already know that the page(1:304)~page(1:311) are in mixed extent, but it’s marked ‘NOT ALLOCATED’. (Please review the Sample 4)

 

Sample 8: 100 Percent full. (Please review sample 2)

Liwei_17-1598128693219.png

 

Page 310,311,320,321,322,323 and 324 are almost full because every row in pages is more than 8KB.

Let’s check the PFS page.

Liwei_18-1598128693231.png

 

Display again using the parameter 1 instead of 3.

 

Liwei_20-1598129191276.png

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.