What Is sys.allocation_units view in SQL Server?

sys.allocation_units is a system view in a SQL Server Database that contains information about each allocation unit of a data partition to a physical file.

Each record in the sys.allocation_units table represents a single allocation of a data partition with the following key fields:

• allocation_unit_id - bigint: ID of the allocation unit. Is unique within a database.
• type - tinyint: Type of allocation unit.
• type_desc - nvarchar(60): Description of the allocation unit type.
• container_id - bigint: ID of the storage container associated with the allocation unit. In most cases, the container_id is the same as the allocation_unit_id, which is referenced by the hobt_id in the sys.partitions view.
• data_space_id - int: ID of the filegroup in which this allocation unit resides.
• total_pages - bigint: Total number of pages allocated or reserved by this allocation unit.
• used_pages - bigint: Number of total pages actually in use.
• data_pages - bigint: Number of used pages that have:

Here is a list of records from sys.allocation_units showing data allocation units used in a SQL Server database:

SELECT * FROM sys.allocation_units

allocation_unit_id  type  container_id  data_space_id  total_pages  used_pages  ...
397632              1     397632        1              166          125         ...
463168              1     463168        1              64           36          ...
528704              1     528704        1              74           54          ...
594240              1     594240        1              4            4           ...

sys.allocation_units contains key information to map the data partition to logical filegroup, and tracks the storage usage in units of pages (also called blocks). By the way, the page size is fixed to 8 KB.

⇒ Find the File IDs of a Given Table

⇐ What Is sys.partitions?

⇑ SQL Server System Views and Functions

⇑⇑ SQL Server Storage Tutorials