you're reading...
SQL Server

Heap Files

What is a heap? Quite simply a heap is a table without a clustered index. Data within a table is grouped together into allocation units based on the column data types. Data within these allocation units are stored in pages. Each page is of size 8KB. Group of 8 pages (64K) is stored together and they are referred as Extent. Depending on the data types in the heap, each heap structure will have one or more allocation units to store and manage the data for a specific partition. At a minimum, each heap will have one IN_ROW_DATA allocation unit per partition. If the table conatains any LOB data columns it will also have one LOB_DATA allocation unit per partition, and if the table conatins any variable length columsn that exceed 8k (8060 bytes) it will also have one ROW_OVERFLOW_DATA allocation unit per partition. To recap, if the data of table is not physically stored in a particular order i.e. (clustered index) it is known as a Heap Structure.

Heaps can be identified by querying the sys.partitions table for index_id=0. Heaps have one row in sys.partitions, with index_id = 0 for each partition used by the heap. By default, a heap has a single partition, however when a heap has multiple partitions, each partition has a heap structure that contains the data for that specific partition. For example, if a heap has three partitions, there are three heap structures.

The data pages and the rows within a heap are not in any specific order and are not linked. The only logical connection between data pages is the information recorded in the IAM pages. An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single file also known as GAM interval. The column first_iam_page in the sys.system_internals_allocation_units system view points to the first IAM page in the chain of IAM pages that manage the space allocated to the heap in a specific partition. SQL Server uses the IAM pages to move through the heap.

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


About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: