Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL Server Storage Internals 4
T-SQL 1

Archive · View All
August 2011 5

LivingForSqlServer

SQL Server Storage Internals Part 3 - Architecture of Heaps

Aug 9 2011 1:12AM by Ramkumar   

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 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, I am 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

Tags: SQL Server Storage Internals,


Ramkumar
366 · 0% · 112
1 Readers Liked this
Jacob Sebastian Liked this on 8/20/2011 8:29:00 AM
Profile · Blog · Facebook · Twitter
1
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"SQL Server Storage Internals Part 3 - Architecture of Heaps" rated 5 out of 5 by 1 readers
SQL Server Storage Internals Part 3 - Architecture of Heaps , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]