Special Storage Series – III Unrestricted-Length Object Data I

Read Previous Article:

• Special Storage Series – I (Checking the 8K (8192) size of page)
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.B One row can span many row-overflow pages.
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.C Update statement on row-overflow pages data.

There are mainly three kind of LOB type

• Text
• Ntext
• Image

And if your table consists of any of these LOB type for your field, then by default the actual data is not stored on regular data pages, because for LOB data type SQL Server has separate pages for storing the data. Like row-overflow data, LOB data are stored in their own set of pages.

The Pages where the data of LOB data get stored are

PageType 3, LOB or row-overflow page, TEXT_MIXED

PageType4, LOB or row-overflow page, TEXT_DATA

For LOB column, SQL Server stores a 16 byte pointer in the data row that indicates where the actual data can be found i.e either PageType3 or PageType4. Although default behavior of SQL Server is to store the LOB data off the data row but SQL Server also allows you to change the default behavior of LOB data storage mechanism and allow LOB data to get stored in the data row itself if it is small enough.  

The collection of 8K pages that form the LOB data, not necessary located next to each other, they might be scattered but logically they are organized in a B-Tree Structure, so operation on LOB data type is very efficient.

Let’s take a look at how data of text type i.e. LOB type get stored in separate LOB Page, for this we are creating a table Tab_VKM_LOBTable with col1 as text data type.

create table Tab_VKM_LOBTable

(col1 text)

Command(s) completed successfully.

Let’s insert a record to the table.

insert into Tab_VKM_LOBTable

select REPLICATE('a',3000)

(1 row(s) affected)

Now we will introspect the table storage detail, ie in what all kind of pages, data of table are getting stored by querying System Catalog   sys.partitions and sys.allocation_units.

select OBJECT_NAME(p.object_id) as TableName,

p.partition_id,p.partition_number,p.rows,

au.total_pages as Pages,

au.type_desc as PageType

from sys.partitions p, sys.allocation_units au

where p.partition_id=au.container_id

and p.object_id=OBJECT_ID('Tab_VKM_LOBTable')

TableName        partition_id      partition_number rows Pages PageType

--------------------------------------------------- ----------------------

Tab_VKM_LOBTable 72057594041008128 1                1    2     IN_ROW_DATA

Tab_VKM_LOBTable 72057594041008128 1                1    2     LOB_DATA

Now from the output we can see there are two pages for each Page Type. We can fire DBCC IND query to and see the detail of each pages. Here we are using our custom table which we created in our last post (Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A) and store the output from DBCC IND to the custom table.

Here we are executing DBCC IND for ‘Tab_VKM_LOBTable' and storing the output in out custom made TAB_VKM_TablePages table.

insert into TAB_VKM_TablePages

exec ('dbcc ind (''master'',''Tab_VKM_LOBTable'',-1)')

select PageFID,PagePID,OBJECT_NAME(objectId) as [Table],

iam_chain_type,PageType from TAB_VKM_TablePages

PageFID PagePID Table            iam_chain_type PageType

------- ------------------------ -------------- --------

1       184     Tab_VKM_LOBTable In-row data    10

1       183     Tab_VKM_LOBTable In-row data    1

1       182     Tab_VKM_LOBTable LOB data       10

1       181     Tab_VKM_LOBTable LOB data       3

(4 row(s) affected)

From the output we can observe two pages for LOB Data and two pages for In-row data. The Data page hold a 16byte pointer to LOB Page. The descriptions for page type are

PageType =  1, Data Page

PageType =  2, Index Page

PageType =  3, LOB, Row-Overflow page, TEXT_MIXED

PageType =  4, LOB, Row-Overflow page, TEXT_DATA

PageType =  10, IAM Page

Here we can make out there are one data page and one IAM page for both in-row-data and LOB-data.

It is also possible to store LOB data in in-row data pages; we will see this in coming article from this series.

Code confidently in the course of your logic and other will bow their head to the direction of your code triumph.

Post Reference: Vikram Aristocratic Elfin Share