Muokkaa

Jaa

sys.allocation_units (Transact-SQL)

  • Artikkeli

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Contains a row for each allocation unit in the database.

Column name Data type Description
allocation_unit_id bigint ID of the allocation unit. Is unique within a database.
type tinyint Type of allocation unit:

0 = Dropped

1 = In-row data (all data types, except LOB data types)

2 = Large object (LOB) data (text, ntext, image, xml, large value types, and CLR user-defined types)

3 = Row-overflow data
type_desc nvarchar(60) Description of the allocation unit type:

DROPPED

IN_ROW_DATA

LOB_DATA

ROW_OVERFLOW_DATA
container_id bigint ID of the storage container associated with the allocation unit.

If type = 1 or 3 in a rowstore index container_id = sys.partitions.hobt_id.

If type = 1 or 3 in a columnstore index, container_id = sys.column_store_row_groups.delta_store_hobt_id.

If type is 2, then container_id = sys.partitions.partition_id.

0 = Allocation unit marked for deferred drop
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:

In-row data

LOB data

Row-overflow data



Note that the value returned excludes internal index pages and allocation-management pages.

Note

When you drop or rebuild large indexes, drop large tables, or truncate large tables or partitions, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.allocation_units immediately after dropping or truncating a large object may not reflect the actual disk space available.

When Accelerated Database Recovery is enabled, deferred drop is used regardless of object size.

Permissions

Requires membership in the public role. For more information, see Metadata Visibility Configuration.

Examples

Determine space used by object and type of an allocation unit

The following query returns all the user tables in a database and the amount of space used in each, by allocation unit type.

SELECT t.object_id AS ObjectID,
       OBJECT_NAME(t.object_id) AS ObjectName,
       SUM(u.total_pages) * 8 AS Total_Reserved_kb,
       SUM(u.used_pages) * 8 AS Used_Space_kb,
       u.type_desc AS TypeDesc,
       MAX(p.rows) AS RowsCount
FROM sys.allocation_units AS u
JOIN sys.partitions AS p ON u.container_id = p.hobt_id
JOIN sys.tables AS t ON p.object_id = t.object_id
GROUP BY t.object_id,
         OBJECT_NAME(t.object_id),
         u.type_desc
ORDER BY Used_Space_kb DESC,
         ObjectName;

See Also

sys.partitions (Transact-SQL)
Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)