Home » Blog » The Allocation Unit

The Allocation Unit

2014-03-26 - DMVs & CVs, General, Series, SQL Server Internals, Storage Wednesday

Introduction

SQL Server organizes all the data in pages. That includes the actual table data, index data, large object data and row overflow data. The pages that make up a table are however not all assigned to the table itself. Instead, they are grouped in logical units called allocation units.

The Allocation Unit

A table can have many indexes. Each index or heap, including the base table can in turn have many partitions. In fact, in SQL Server every table or index is partitioned. However, if you do not specify an explicit partition scheme, all the data of the index or heap goes into a single partition.

Each partition stores the table or index rows that belong to that partition. All the pages required to store that information are grouped into an allocation unit. If the partition also contains row overflow data, another allocation unit is created to contain all pages with row overflow data. If large binary objects are present, all the pages for that type of data make up yet another allocation unit. That means, in SQL Server versions up to 2012, a single partition of an index or heap can contain up to three separate allocation units.

sys.allocation_units

All allocation units of the current database can be found in the catalog view sys.allocation_units. Because the allocation units are organized under the partitions, sys.allocation_units does not contain an object_id or index_id column but rather a partition_id. So, to get from an allocation unit in sys.allocation_units to the table it belongs to, we need to first join to sys.partitions. That catalog view contains the object_id as well as the index_id that can be used to identify the index and table that the allocation unit belongs to.

[sql] SELECT OBJECT_NAME(P.object_id) AS object_name,
I.name AS index_name,
i.type_desc AS index_type,
P.partition_number,
AU.type_desc AS allocation_unit_type
FROM sys.allocation_units AS AU
JOIN sys.partitions AS P
ON P.partition_id = AU.container_id
JOIN sys.indexes AS I
ON P.object_id = I.object_id
AND P.index_id = I.index_id;
[/sql]

This query returns information about all allocation units in the current database, including those of system tables. You will get an output that looks like this:

The image shows a system table that has all three types of allocation unit present for the clustered index.

Summary

SQL Server groups all pages that belong to a single partition of a single index or heap into logical units. These units are called allocation units. You can see all allocation units of the current database using the sys.allocation_units catalog view.