How to determine which row in a table a LOB Page is connected to?

John Couch 181

I have a table with Lob Data Pages, using DBCC IND and DBCC PAGE to look at a page, I am curious if there is a way to determine which record in the table the data on that page is tied too?

1 answer

Greg Low 1,670 Reputation points Microsoft Regional Director

2024-03-13T03:29:12.38+00:00

It's described quite well here: https://sqlity.net/en/2496/text-mix-page/

In that article, he uses sys.dm_db_database_page_allocations (undocumented) to get a list of pages for the allocation units for a table.

Keep in mind that LOB pages are now often "overflow" pages and there may be multiple, even for a single column in the source table. DBCC PAGE for these shows the slot number and offset for the data on the page.

There is also a pretty good article here that shows how to wander through using DBCC IND and DBCC PAGE: https://www.sqlservercentral.com/articles/understanding-the-internals-of-a-data-page#:~:text=LOB%20(Large%20OBject)%20page,depending%20on%20the%20column%20size.
Please sign in to rate this answer.
John Couch 181 Reputation points

2024-03-13T03:41:48.68+00:00

So I had read through that article earlier, and I understand how to see the LOB pages, but I am trying to determine which record is being stored on the LOB page. Using DBCC IND and DBCC PAGE I can see the details, but I want to know how to link that back into the table to say its this record.

Greg Low 1,670 Reputation points Microsoft Regional Director

2024-03-13T03:45:34.5566667+00:00

Won't the slot number and column ID get you back to the slot that's holding the row in the original page? It also has a timestamp that links to the blob IDs.

If I get a chance later, I'll try to do a worked example.

Greg Low 1,670 Reputation points Microsoft Regional Director

2024-03-13T03:46:55.97+00:00

(deleted)

John Couch 181 Reputation points

2024-03-13T04:11:59.3466667+00:00

So, I used DBCC IND, and it returned 1.1 million rows, which are mostly in-row data. There are about 30k which are LOB data.

I am looking at the LOB Data Page and trying to work backward.

Greg Low 1,670 Reputation points Microsoft Regional Director

2024-03-13T04:18:12.9233333+00:00

So from DBCC IND, you have the object id and the index id. If it's index 0 or 1 then it's the HoBT (heap or binary tree) for the table i.e., the table itself. Otherwise, it's pages for some index.

In the row data from DBCC IND, you then also have the column number. You can map that and the object id to sys.columns to get the column name and sys.tables to get the table name. The offset then just shows you which part of the column data that you're seeing. The slot number will map to the original slot on the table page, and the timestamp maps to a ID that's a blob ID that represents the entire blob for the column.

Is there something else you're trying to map back to?
Sign in to comment

Share via

How to determine which row in a table a LOB Page is connected to?

1 answer