Looking Deep Into Storage Structure For SQL Server

This article is looking deep into storage structures starting with the Heap table. This is something to become familiar with as part of the basics of SQL Server internals.

Prerequiste Terms

The following are terms to be familiar with before looking deep into storage structures.

1. Page: is 8kb in size. A page is divided into 3 parts, the Header, Records and an Offset Array. The Header is 96 bytes and contains meta information about a page, like page number, owning object and type of page. At the end of the page is an Offset Array of 36 bytes providing pointers to a location. Between these two areas are 8060 bytes where records are stored.

Header -> 96 bytes

Records -> 8090 bytes

Offset Array -> 36 bytes

2. Extent: contains 8 pages or 64 KB in size. is a collection of 8 pages. The group called is called an extent. It can be of 2 types, either mixed or Uniform Extent. A Mixed Extent is pages allocated to multiple objects. For example, if a table has less than eight pages then they are put into a Mixed Extent. All ages of Uniform Extents are allocated to a single object. We can say they are a contiguous allocation of pages.

3. Page Free Space (PFS): It keeps the information regarding free space in pages. There will be a PFS for every 8000 pages.

4. Global Allocation Map (GAM): It's the third page in each data file. 1 GAM covers up to 4GB of data.

5. Shared Global Allocation Map (SGAM): It's the fourth page in each data file. It keeps information related to Extents, whether it is Mixed or not. 1 SGAM covers up to 4 GB of data.

Let's run the following code:

CREATE DATABASE IndexInternalsDeep
GO
USE IndexInternalsDeep
GO
CREATE TABLE HeapTable
(
id INT IDENTITY(1,1),
vchName VARCHAR(8000),
)
INSERT INTO HeapTable VALUES('Vikrant More')
INSERT INTO HeapTable VALUES('Suraj Sheikh')
SELECT * FROM HeapTable

id	vchName
1	Vikrant More
2	Suraj Sheikh

---- LOOKING FOR EXTENT INFORMATION.

DBCC EXTENTINFO('IndexInternalsDeep','HeapTable')

Description of columns in EXTENTINFO:

file_id and page_id: represents a file number where the pages reside and a page number within the file where the data lives.
pg_alloc: Number of pages allocated from the extent to the object.
ext_size: Size of the extents
object_id: Object id for the table.
index_id: Provides the id of the index; 0 for the heap, 1 for the clustered index and non-clustered ids >= 2.
partition_number: Partition Number for the heap or index.
iam_chain_type: Type of data stored (in row data, row overflow and so on)
pfs_bytes: byte array that identifies the number of free space.

-- LOOKING FOR INDEX INFORMATION

DBCC IND('IndexInternalsDeep','HeapTable',-1)

Description of columns in IND:

PageFID and PagePID: represents a file number where pages reside and a page number within the file where data lives.
IAMFID: Fileid of the file containing the page (refer to sysfiles).
ObjectID: Objectid of the table used.
IndexID: Provides the id of the index. 0 for heap, 1 for clustered index, non-clustered ids >= 2.
PartitionNumber: it's for the heap or index.
PartitionID: it's for the heap or index.
iam_chain_type: Type of data stored (in row data, row overflow and so on).
PageType: 1 refers to Data page, 2 -> Index page, 3 and 4 -> text pages.
IndexLevel: 0 refers to leaf. The highest value refers to the root of an index.
NextPageFID and PrevPageFID: refers to the next and previous file numbers.
NextPagePID and PrevPagePID: refers to the next and previous page numbers.

Script to check space allocation details using the following query.

SELECT obj.name
,obj.object_id
,p.index_id
,AU.total_pages
,AU.used_pages
,AU.data_pages
,first_iam_page
,first_page
,root_page
FROM
sys.objects obj
INNER JOIN sys.partitions p on obj.object_id = p.object_id
INNER JOIN sys.allocation_units AU on au.container_id = p.hobt_id
INNER JOIN sys.system_internals_allocation_units SIAU on SIAU.container_id =au.container_id
WHERE obj.object_id = object_id('Heaptable')

identifying First Page ID based on First_page

First_page value = 0x420100000100.

A set of 2 Hexadecimal numbers represent a byte.

Read the following number from right to left (excluding the "0x" prefix):

0x 42 01 00 00 01 00

After reading it from right to left it should be:

0x 00 01 00 00 01 42

The preceding hexadecimal number is divided into 2 groups, the first group is 2 bytes and the second group is 4 bytes.

The first group, 00 01, represents the file group number.
The second group, 00 00 01 42, represents the page number.

The decimal equivalent of the first group, in other words 00 01, is 1 so the File Group ID = 1.

Now let's convert 00 00 01 42 into the decimal equivalent,

The following is the way I to convert the hexadecimal number to decimal:

=> 1*16^2 +4*16^1 +2 * 16^0
=> 1 * 256 + 4 *16 + 2 * 1 (anything raise to 0 = 1)
=> 256 + 64 + 2
=> 322 --- PagePID