Home » Blog » Page Type 3 – LOB Data Pages explained

Page Type 3 – LOB Data Pages explained

2014-07-23 - General, Series, SQL Server Internals, Storage Wednesday

SQL Server stores all data in 8192-byte sized blocks called pages. Several types of pages are in use within a typical database. One particularly interesting group is formed by the type-3 pages or Large Object Pages.

LOB_DATA Allocation Units

Most data types in SQL Server take up no more than 8000 bytes of storage. However, there are a few data types, which allow for larger pieces of information to be stored. Examples include the VARCHAR(MAX), VARBINARY(MAX) or XML data types.

Normal data pages that belong to a table are grouped in IN_ROW_DATA allocation units. However, if a value that is larger than 8000 bytes needs to be stored, SQL Server does not attempt to store it in those data pages anymore. It does not even store those values in the same allocation unit. Instead, Large Object data or LOB data is stored in special LOB_DATA allocation units.

When a table is created that has columns with LOB data types, a second allocation unit is created automatically. Every time a row containing a large value is stored, the non-LOB values end up in a normal data page. For every LOB value, a pointer is written with the in row data. That pointer points to the actual value in a page that is part of the LOB_DATA allocation unit.

Everything discussed in this article applies to single partitions. Every clustered or nonclustered index as well as every heap has at least one partition in SQL Server. If it is defined as "partitioned", it likely has more than one. Every partition has its own set of allocation units. (Check here for details.) So, when I talk about the allocation unit(s) of a table, I am really referring to the allocation units of every partition of every index or heap that is part of that table.

Let us look at an example. First, we need to create a table with a LOB column:

[sql] CREATE TABLE dbo.TableWithLob
(
id INT IDENTITY(1,1) CONSTRAINT [PK:dbo.TableWithLob] PRIMARY KEY CLUSTERED,
some_value INT,
some_lob VARCHAR(MAX)
)

GO
INSERT INTO dbo.TableWithLob(some_value)
VALUES(11),(13),(15),(17),(19);
[/sql]

The SQL snippet creates a simple table with a VARCHAR(MAX) column and inserts five rows into it. Note that in all those rows, the value of the some_lob column is NULL.

If we now take a look at the sys.allocation_units catalog view, we can see that two allocation units are assigned to this table.

[sql] SELECT OBJECT_SCHEMA_NAME(P.object_id) AS schema_name,
OBJECT_NAME(P.object_id) AS table_name,
I.name AS index_name,
P.partition_number,
P.rows,
AU.allocation_unit_id,
AU.type_desc AS allocation_unit_type,
AU.used_pages,
AU.data_pages,
AU.total_pages
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.index_id = I.index_id
AND P.object_id = I.object_id
WHERE P.object_id = OBJECT_ID('dbo.TableWithLob');
[/sql]

The first one is the IN_ROW_DATA allocation unit that is part of every table. The second is an allocation unit of type LOB_DATA. That allocation unit does not have any pages allocated to it yet, as all rows in the table have their some_lob column set to NULL.

The Story of the TEXT MIX PAGE

Once there is actual LOB data to store, it will get stored in that additional allocation unit in special pages. Those pages that hold the LOB values are pages of type 3. They are called TEXT MIX pages or just LOB pages. To confirm that, let us first look at the pages that belong to the example table in its current state. We can use the following query to get a list of all pages in our table:

[sql] SELECT OBJECT_SCHEMA_NAME(P.object_id) AS schema_name,
OBJECT_NAME(P.object_id) AS table_name,
I.name AS index_name,
P.partition_number,
DDDPA.allocation_unit_id,
DDDPA.allocation_unit_type_desc,
DDDPA.allocated_page_file_id AS file_id,
DDDPA.allocated_page_page_id AS page_id,
DDDPA.page_type,
DDDPA.page_type_desc,
DDDPA.page_level
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.TableWithLob'),NULL,NULL,'DETAILED') AS DDDPA
JOIN sys.allocation_units AS AU
ON DDDPA.allocation_unit_id = AU.allocation_unit_id
JOIN sys.partitions AS P
ON P.partition_id = AU.container_id
JOIN sys.indexes AS I
ON P.index_id = I.index_id
AND P.object_id = I.object_id
ORDER BY DDDPA.allocation_unit_id,DDDPA.allocated_page_file_id,DDDPA.allocated_page_page_id;
[/sql]

The query is using the undocumented sys.dm_db_database_page_allocations DMF to get a list of all pages that belong to the different allocation units in our table. There are currently only two:

One is a normal data page and the other one is an IAM page that is used by SQL Server to keep track of all pages that belong to this allocation unit.

If we now change the value of the some_lob column in a single row we can see that the value is stored in the LOB_DATA allocation unit. For that, we are going to use this update statement:

[sql] UPDATE dbo.TableWithLob SET
some_lob = REPLICATE(CAST('X' AS VARCHAR(MAX)),8001)
WHERE id = 5;
[/sql]

After executing this statement, you can run the above query again to look at the pages that are now part of this table:

The table now consists of four pages: The two pages that were there before, but also two new pages. Both new pages are in the LOB_DATA allocation unit. SQL Server needs at least one IAM page in every allocation unit, to catalog the pages that are part of it. Therefore, one of the new pages is an IAM page. The other page is, as we expected, a TEXT_MIX_PAGE.

The reason the page type is listed as a TEXT_MIX_PAGE instead of a lob data page is likely to be found in the history of this data type. Before SQL Server 2005, the only two data types that could store LOB data were IMAGE and TEXT.

Summary

In SQL Server, Large Object data is stored in special LOB data pages that live in separate LOB data allocation units. Those pages are of type 3 (TEXT_MIX_PAGE). Any partition of any table or index that holds a column with a LOB data type automatically receives a LOB_DATA allocation unit. That allocation unit stays empty until an actual large value is stored.