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.
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.
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.
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.
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.
You must be logged in to post a comment.
Pingback: Page Type 3 - LOB Data Pages explained - sqlity.net
Pingback: March towards SQL Server : Day 5 – SQL DBA Interview Questions Answers – Data File and Transaction Log File Architecture - DBA THINGS
Pingback: SQL DBA Interview QA «
Pingback: Data File and Transaction Log File Architecture | SQL DBA