In this article I have explained below topics
1. key points on HEAP tables.
2. What happens when a table is created.
3. How to list IAM, data pages allocated to a heap table.

As all data, index and system pages stores data in hexadecimal format, knowledge of Hexadecimal to decimal conversion is important to interpret values stored within pages.

Warm up exercise:

Key notes on HEAP structures:

1.       A heap is a table without a clustered index.

2.       IAM is the master page to keep track of all pages allocated to a heap.

3.       For most of the small heap tables, there will be only one IAM page to manage all pages owned by a heap.

4.       There can be more than one IAM page if a heap table size is greater than 4 GB and/or LOB data type is present

5.       When a query is fired to fetch all records in a heap table,  SQL Server uses IAM page to move
throw the heap

6.       Unlike Clustered/Non Clustered Indexes, heap pages are not interlinked, meaning m_prevPage and m_nextPage header values of all heap pages will have the neutral value 0:0)

When a table is created without clustered index, its entry will reflect in sys.indexes table with index_id as 0.

Here is the query to list all heap tables in a database.

select OBJECT_NAME(object_id), index_id, type_desc
from sys.indexes
where index_id = 0

to understand heap architecture better,

First let’s create a table named tExample3

CREATE TABLE tExample3(

strEmplCode int identity(1001,1),

strFirstName varchar(100),

strDeptCode char(6) NOT NULL,

strAddress varchar(500),

intSalary int)

GO

Note:
When a table is created by default no page is allocated to it. When first record is inserted, initially page is allocated for a table in mixed extend. When a table grows, uniform extend are allocated later.

Here is the query to display initial page assignment status.

select so.name, so.object_id, sp.index_id, internals.total_pages, internals.used_pages, internals.data_pages, first_iam_page, first_page, root_page

      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

      inner join sys.system_internals_allocation_units internals on internals.container_id = sa.container_id

where so.object_id = object_id('tExample3')

go

Output:  (all values are 0 as no space is allocated to heap)

Now let’s insert 1000 records in heap and analyze space allocation for tExample3 heap table.

INSERT INTO tExample3(strFirstName, strDeptCode, strAddress, intSalary)

VALUES('AAAAA', 'DEPT01', 'CHENNAI', '12500')

GO 1000

Above statement (GO 1000) fires Insert statement 1000 times.

Now we have 1000 records in heap. Let’s check space allocation details using below query

select so.name, so.object_id, sp.index_id, internals.total_pages, internals.used_pages, internals.data_pages, first_iam_page, first_page, root_page

      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

      inner join sys.system_internals_allocation_units internals on internals.container_id = sa.container_id

where so.object_id = object_id('tExample3')

go

Output: (please note that 7 pages are allocated and first IAM and data page details are updated)

You may notice that first_iam_page and first_page values are in hexa decimal format.

Here are the steps to interpret hexadecimal page number:

Let’s interpret first_page value 0xEA0000000100

Step1: each set of two hexadecimal digits represents a byte.
0x EA 00 00 00 01 00

Step2: you have to read hexadecimal value from right to left.

0x EA 00 00 00 01 00
<-----<-----------<-----

becomes,
0x 00 01 00 00 00 EA

Step3: first 2 bytes represents file group number. Remaining 4 bytes represent page number

File group number In hexadecimal: 00 01
decimal equivalent of File group number is 1

Page number in hexadecimal: 00 00 00 EA
decimal equivalent of 00 00 00 EA is 234

finally we got the first page number of a heap : (1:234)

If you find this hard to interpret, my answer is : practice make things perfect. Try to find page number of
few heap tables to be familiar with this.

There is a DBCC command named IND to list all pages allocated to a heap

DBCC TRACEON(3604)

DBCC IND('LearningInternals', 'tExample3', -1)

DBCC TRACEOFF(3604)

Output with explanation:

IAM Page for tExample3 table:  (1: 235)
Data pages holds record for tExample3 table: (1:234), (1:236), (1:237), (1:238), (1:239) and (1:240)

The following illustration shows how the SQL Server Database Engine uses IAM pages to retrieve data rows in a single partition heap.

Note: purpose of this picture is only to demonstrate how IAM page is used to find right extends of a table.  
 

Summary:

In this part, we have seen what happens when a heap table is created and how to view allocated page details, role of IAM page in a heap architecture.  And also we have seen index_id for a heap table is 0 and pages in heap tables are not linked with each other.

In coming articles, im going to cover:
1. Anatomy of a Heap page and IAM page
2. Anatomy of different levels and types of Index pages
3. Anatomy of Header, bitmap and PFS pages
4. what happens at page level when DML is fired.