SQL Server Extents, PFS, GAM, SGAM and IAM and related corruptions

-

 

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 2017 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

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

The page id is:(1:245).

 

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:

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.

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.

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

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.

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

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 8.

 

 

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.

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

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

(1:320) is the first page of last extent in this range. (1:327) 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. 

Takeaway: By checking GAM page, we can tell if the extents and pages are allocated, these extents maybe mixed extent or uniform extent.

 3)What does the entry ‘(1:328)        - (1:1016)      =  NOT   ALLOCATED’ stand for?

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

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

             All the extents within the range are not allocated yet.

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.

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

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

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

Please note: ‘NOT ALLOCATED’ is misleading, 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:240)      -              =     ALLOCATED’ stand for?

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

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

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

(1:248)   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:352)~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 misleading , 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.

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

Let’s talk a look at 00000000 00fe

It’s hex, Let me convert to binary:


Because the bit of the extent 0~ extent 40(totally 41 extents) are all 0, they are allocated, either in GAM or SGAM. so does the page (1:0)~page(1:327).

The bit of extent 41 is 1, so extent 41 is not allocated. The related page (1:328) 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.

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.

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

Let’s talk a look at 00000040 

It’s hex, now convert to binary:

Because the bit of the extent 0~extent 29(totally 30 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~29 are (1:0)~(1:239).

The first page of extent 29 is (1:232).

The bit of extent 30 is 1, so the extent is used as a mixed extent, so does the page(1:240)~page(1:247)

The bit of extent 31 is 0, so the extent is used as a mixed extent, so the extent is not used as a mixed extent, or it a mixed extent and all its pages are being used.

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

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 to observe how GAM and SGAM work.

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.

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

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

4)GAM also reflects the change.

5)Let’s check the SGAM

‘(1:328)      -              =     ALLOCATED’ is marked as mixed extent, which makes sense.

(1:0)   -   (1:320) is marked as 'NOT ALLOCATED', however we already now that the page:245,246,247 are allocated in mixed extent.(See Sample 4) 

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

Page 247,328,329,330,331,332 and 323 are almost full because every row in pages is more than 8KB.

Let’s check the PFS page.

Display again using the parameter 1 instead of 3.



You may restore the backup file and give it a try.

I'll talk about two corruption scenarios next time.

Comments

  1. LiweiAugust 23, 2020 at 9:27 AM

    Please note, restore database backup will consumes some pages, that will change the GAM and SGAM. You will get different views from mine.

    ReplyDelete

Post a Comment

popular posts

SQL Server Polybase in Failover cluster instance

-
Image
Since SQL Server 2017, you can install Polybase in SQL Server Failover cluster instance. SQL Server Polybase Dms and SQL Server Polybase Engine are created in Failover cluster, mapping to SQL Server Polybase Data Movement and SQL Server Engine respectively.     And following two registers keys are created in all the nodes. Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLPBENGINE$InstanceName Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLPBDMS$SQL19     Here are some cases that cause SQL Server Polybase engine fail to start. 1.TCP/IP Protocol is disabled. 2.Polybase engine service fail to connect SQL Server instance. 3.Polybase engine service account does not have permission to access database [DWConfiguration],[DWDiagnostics] and [DWQueue] 4.T he   SQL Server Polybase engine Roles does not exist in Failover cluster. 1)In this case, you need to manually add the role back to failover cluster...
Read more

Tutorial: Create SQL Cluster(FCI) on RHEL

-
Image
  In the Windows world, SQL Server integrates into Windows Server Failover Cluster (WSFC) natively, and we have a dedicated installation for SQL Server Cluster. However, on Linux, you need to install standalone SQL Server instance in the nodes first, and then configure the instance as a SQL Server cluster instance. I use SQL Server 2019 with RHEL 8.x in this tutorial, but it is possible to use SQL Server 2017 in RHEL 7.x or RHEL 8 to configure FCI. Here is the step by step  Video   Topology           1.ISCSI target server configuration   1. The two highlighted two disks will be the used as Shared Storage.     sdc is for the database files in /var/opt/mssql/data sdd is for the user databases files. If all your databases are stored /var/opt/mssql/data, feel free to  ignore all the steps link to device sdd.   2.Run fdisk to create partition. fdisk /dev/sdc fdisk /dev/sdd ...
Read more

Configure multiple-subnet Always On Availability Groups and failover cluster instances by modifying CIB

-
Image
In the Windows world, a Windows Server Failover Cluster (WSFC) natively supports multiple subnets and handles multiple IP addresses via an OR dependency on the IP address. On Linux, there is no OR dependency, but there is a way to achieve a proper multi-subnet natively with Pacemaker, as shown by the following. You cannot do this by simply using the normal Pacemaker command line to modify a resource. You need to modify the cluster information base (CIB). The CIB is an XML file with the Pacemaker configuration. Reference: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-multiple-subnet?view=sql-server-ver15 Here is an example to create a SQL Server Linux Availability group in 4 nodes in 3 subnets in RHEL 7.6   If you are already familiar with the AG Group setup process, please just jump to step 16. 1.Register your subscription on for all servers (red1,red2,red3 and red4 in this case) subscription-manager register   2.List all available subsc...
Read more

Realcase: Failed to upgrade SQL Server 2016 SP2 CU11. (Installation success or error status: 1648)

-
Image
  One of my customer failed to upgrade SQL Server 2016 SP2 CU11. Here is the troubleshooting steps 1.Check SQL Server setup log(C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\) Detailed results: Feature: Database Engine Services Status: Failed: see logs for details Reason for failure: An error occurred for a dependency of the feature causing the setup process for the feature to fail. Next Step: Use the following information to resolve the error, and then try the setup process again. Component name: SQL Server Common Files Component error code: 1648 Component log file: C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20191217_193009\SQLAG\sql_common_core_Cpu64_1.log Error description: No valid sequence could be found for the set of updates. Error help link: http://go.microsoft.com/fwlink?LinkId=20476...
Read more

How to find SQL Server Replication related jobs and T-SQL statements

-
Image
Verbose log is heavily used   in replication troubleshooting. You need to find the right job to enable to verbose log. However, it's not easy to find the   jobs when you have hundreds replication jobs in one server.     Here is how: 1.Distribution agent Following queries list all the distribution agent jobs, including push subscriptions and pull subscriptions. (You may need add more clause to customize your queries). By default, the SQL Server agent job names equal to the agent names for push subscription, unless you explicitly modify the job names. use distribution---in distributor server if not exists(select 1 from sys.tables where name ='MSreplservers') begin select job.name JobName,a.name AgentName, a.publisher_db,a.publication as publicationName,sp.name as publisherName ,ss.name as subscriber,a.subscriber_db, a.local_job From MSdistribution_agents a inner join sys.servers sp on a.publisher_id=sp.server_id--publisher inner join sys.servers ss...
Read more

Tutorial: Add a node to SQL cluster on RHEL

-
Image
  In t his video , I demonstrated how to create a two nodes SQL Cluster instance.   I’m going to show you how to add another node to existing SQL Cluster.   Topology   1.ISCSI target server configuration   1.Add the new node (node3) to the ACLs list. This is the existing setting.     Here are the commands I used.   2. Enable and restart the target service. systemctl enable target.service systemctl restart target.service   2.ISCSI initiator configuration.   Configure ISCSI initiator in the new node (node3)   1.Install iscsi-initiator-utils   in all nodes. sudo yum install iscsi-initiator-utils -y   2.Edit the /etc/iscsi/initiatorname.iscsi , replace the existing value with following keywords, the one I used in step 4 of section[ISCSI target server configuration] InitiatorName=iqn.2020-08.com.contoso:node3             3.Discov...
Read more

SQL Server GAM corruption samples

-
Image
In previous post , we discussed the PFS, GAM and SGAM pages. Today, I’m going show you two related corruptions on these pages.   SQL Server checks the bit in PFS, GAM, SGAM and IAM are checked when new pages are allocated. It guarantees that it does not store data to a wrong page/extent. These pages are crucial to SQL Server, SQL Server considers it as a corruption if they are messed up. I’m going to use the same database I used in previous article . You may restore the   backup  f ile and give it a try. Please note, restoring database will consume some pages and change the PFS, GAM, SGAM and IAM. You may get a different view from my mine.   Let me show you some of the content of GAM and SGAM before the corruption samples. Here are the GAM and SGAM page result: 1.GAM 2.SGAM 3.We can tell that the extent of page(1:352) and extents greater than that have not been allocated. 4.Let’s take a dive into the GAM page. 5.‘ 00000000 00f0 ’ inter...
Read more

You may fail to backup log or restore log after TDE certification/key rotation

-
Image
  When TDE is enabled, all the records written to transaction log files are encrypted as well. During the TDE certification/key rotation, these log records are not touched. On changing certificate/keys, the current active VLF file, which is encrypted by old key, will be closed. New log records will be encrypted by new key and write to next available VLF or a new created VLF. At this stage, the transaction log file has both log records encrypted by old certificate, and the log records encrypted by new certificate. When you do the log backup, the log records that have been encrypted by old certificate, may be decrypted and then encrypted by new certificate to the backup file, or just be flushed to the backup file without doing any decryption/encryption, depending on how you run the log backup. This behavior may cause two different issues if the old certificate is removed. 1. When you do log backup with ‘COMPRESSION+MAXTRANSFERSIZE’ combination , SQL Server will decrypt records, which...
Read more

Password is required when adding a database to AG group if the database has a master key

-
Image
Moved this post from my old blog.   When you are trying add a database having a ‘master key’ to a AG group, you will see the message in SSMS. You can’t move to next step until you put the correct password of the ‘master key’. This is an requirement of SSMS to help you manage SQL Server database master key password in secondary replicas. Why we have this requirement? When a master key is created, it’s encrypted by both password and service master key. create MASTER KEY ENCRYPTION BY PASSWORD = ‘Password1’; This master key will be open automatically when it’s need for decryption or encryption. In this case, it is not necessary to use the ‘OPEN Master Key’ T-SQL statement. However, when a database is first attached or restored to a  new  instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the  OPEN MASTER KEY  statement to decrypt the database master key (DMK). Once the DMK ha...
Read more