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

SQL Server Storage Internals Part 3 - Architecture of Heaps

 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:

Hexadecimal value

Decimal Equivalent

A

10

F

15

10

16

FF

255

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.

Index_id in sys.indexes

Meaning

0

Heap

1

Clustered Index

>1

Non Clustered Index

 

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)

3_1

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)

3_2

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:

3_3

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.  
 3_4

 

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.

Comments

Posted by Anonymous on 19 December 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, SQL Server Storage Internals Part 3 - Architecture of Heaps - LivingForSqlServer         [sqlservercentral.com]        on Topsy.com

Posted by Anonymous on 19 December 2010

Pingback from  You unexpected weight loss methods | World Health Life

Posted by Anonymous on 19 December 2010

Pingback from  1 comment(s) &#8211; SQL Server Central - sql

Posted by Anonymous on 19 December 2010

Pingback from  1 comment(s) &#8211; SQL Server Central - sql

Posted by Anonymous on 19 December 2010

Pingback from  Aluratek LIBRE AEBK01F eBook Reader PRO Digital Text Reader - PDFAEBK01F - Wordpress Video Tutorials

Posted by Anonymous on 21 December 2010

Pingback from  Ramkumar (LivingForSqlServer) posts SQL Server Storage Internals Part 3 &#8211; Architecture of Heaps | SQL Server Central | sqlmashup

Posted by Anonymous on 13 July 2011

Pingback from  SQL Server Storage Internals Part 3 -... | SQL Server | Syngu

Posted by lilu007_snow-1135881 on 21 September 2011

Hi, Ramkumar. First thanks for your awsersome articles.

As you mentioned Data pages holds record for tExample3 table: (1:234), (1:236), (1:237), (1:238), (1:239) and (1:240). So these pages should be fall in the extend no 9(234/8), but you said the extend 4 hold these pages in the last figure, is it right?

Posted by Ramkumar on 21 September 2011

You are right lilu007_snow-1135881

tExample3 pages will be in extent no 29 and 30.

I didnt create last picture with technical accuracy. My intention was only to show that heap pages has to refer IAM pages.

and sure. let me avoid this in coming days.

Leave a Comment

Please register or log in to leave a comment.