I'm attempting to get a list of pages for a table that has rows with ROW_OVERFLOW_DATA. I can get the list of allocated pages from the undocumented DMV, sys.db_db_database_page_allocations, however, there appears to be no ROW_OVERFLOW_DATA pages listed in the output of that DMV. Is there some other DMV that I simply cannot locate?
Minimal, complete, and (hopefully!) verifiable example:
USE tempdb;
IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t;
GO
CREATE TABLE dbo.t
(
rownum int NOT NULL IDENTITY(1,1)
PRIMARY KEY CLUSTERED
, on_row_data varchar(30) NOT NULL
DEFAULT ('on_row_data')
, off_row_data varchar(MAX) NOT NULL
DEFAULT REPLICATE('A', 20000) --PLENTY BIG ENOUGH!
) WITH (DATA_COMPRESSION = NONE); --not compressing those pages!
INSERT INTO dbo.t DEFAULT VALUES;
DECLARE @ObjectID int = (SELECT o.object_id FROM sys.objects o WHERE o.name = 't');
DECLARE @PageID int;
DECLARE @PageTypeDesc varchar(100);
SELECT FileID = dpa.allocated_page_file_id
, PageID = dpa.allocated_page_page_id
, PageTypeDesc = dpa.page_type_desc
FROM sys.dm_db_database_page_allocations(DB_ID(), @ObjectID, NULL, NULL, 'DETAILED') dpa
Output looks like:
╔════════╦════════╦══════════════╗ ║ FileID ║ PageID ║ PageTypeDesc ║ ╠════════╬════════╬══════════════╣ ║ 1 ║ 1598 ║ IAM_PAGE ║ ║ 3 ║ 105368 ║ DATA_PAGE ║ ║ 3 ║ 105369 ║ NULL ║ ║ 3 ║ 105370 ║ NULL ║ ║ 3 ║ 105371 ║ NULL ║ ║ 3 ║ 105372 ║ NULL ║ ║ 3 ║ 105373 ║ NULL ║ ║ 3 ║ 105374 ║ NULL ║ ║ 3 ║ 105375 ║ NULL ║ ╚════════╩════════╩══════════════╝
Which makes sense, other than the missing ROW_OVERFLOW_DATA page. We have a single index allocation map page, and a full extents-worth of 8KB data pages, with only a single one of those pages actually allocated.
Similarly, if I use the undocumented sys.fn_PhysLocCracker function to show the page where each row exists, as in:
SELECT *
FROM dbo.t
CROSS APPLY sys.fn_PhysLocCracker(%%PHYSLOC%%)
I only see the DATA_PAGE listed:
╔════════╦═════════════╦═════════════════════╦═════════╦═════════╦═════════╗ ║ rownum ║ on_row_data ║ off_row_data ║ file_id ║ page_id ║ slot_id ║ ╠════════╬═════════════╬═════════════════════╬═════════╬═════════╬═════════╣ ║ 1 ║ on_row_data ║ AAAAAAAAAAAAAAAAAAA ║ 3 ║ 105368 ║ 0 ║ ╚════════╩═════════════╩═════════════════════╩═════════╩═════════╩═════════╝
Likewise, if I use DBCC IND(database, table, index) I only see the two pages listed:
DBCC IND (tempdb, t, 1);
Output:
╔═════════╦═════════╦════════╦════════╦════════════╦═════════╦═════════════════╦═════════════════════╦════════════════╦══════════╦════════════╦═════════════╦═════════════╦═════════════╦═════════════╦══╗ ║ PageFID ║ PagePID ║ IAMFID ║ IAMPID ║ ObjectID ║ IndexID ║ PartitionNumber ║ PartitionID ║ iam_chain_type ║ PageType ║ IndexLevel ║ NextPageFID ║ NextPagePID ║ PrevPageFID ║ PrevPagePID ║ ║ ╠═════════╬═════════╬════════╬════════╬════════════╬═════════╬═════════════════╬═════════════════════╬════════════════╬══════════╬════════════╬═════════════╬═════════════╬═════════════╬═════════════╬══╣ ║ 1 ║ 1598 ║ NULL ║ NULL ║ 2069582411 ║ 1 ║ 1 ║ 6989586877272752128 ║ In-row data ║ 10 ║ NULL ║ 0 ║ 0 ║ 0 ║ 0 ║ ║ ║ 3 ║ 105368 ║ 1 ║ 1598 ║ 2069582411 ║ 1 ║ 1 ║ 6989586877272752128 ║ In-row data ║ 1 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ ║ ╚═════════╩═════════╩════════╩════════╩════════════╩═════════╩═════════════════╩═════════════════════╩════════════════╩══════════╩════════════╩═════════════╩═════════════╩═════════════╩═════════════╩══╝
If I look at the actual page contents, using DBCC PAGE, it looks like I still don't see anything about which page contains the ROW_OVERFLOW_DATA – I'm sure it must be there, I probably just don't know what to look at:
DBCC PAGE (tempdb, 3, 105368 , 3) WITH TABLERESULTS;
The results are too big to fit here, if I include the memory dump rows, but this is the header output:
╔══════════════╦════════════════════════════════╦═══════════════════════════════╦═══════════════════════════════╗ ║ ParentObject ║ Object ║ Field ║ VALUE ║ ╠══════════════╬════════════════════════════════╬═══════════════════════════════╬═══════════════════════════════╣ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bpage ║ 0x000002431A8A2000 ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bhash ║ 0x0000000000000000 ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bpageno ║ (3:105368) ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bdbid ║ 2 ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ breferences ║ 0 ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bcputicks ║ 0 ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bsampleCount ║ 0 ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bUse1 ║ 63172 ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bstat ║ 0x10b ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ blog ║ 0x212121cc ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bnext ║ 0x0000000000000000 ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bDirtyContext ║ 0x000002435DA77160 ║ ║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bstat2 ║ 0x0 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_pageId ║ (3:105368) ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_headerVersion ║ 1 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_type ║ 1 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_typeFlagBits ║ 0x0 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_level ║ 0 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_flagBits ║ 0xc000 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_objId (AllocUnitId.idObj) ║ 3920762 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_indexId (AllocUnitId.idInd) ║ 512 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ Metadata: AllocUnitId ║ 144115445026914304 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ Metadata: PartitionId ║ 6989586877272752128 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ Metadata: IndexId ║ 1 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ Metadata: ObjectId ║ 2069582411 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_prevPage ║ (0:0) ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_nextPage ║ (0:0) ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ pminlen ║ 8 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_slotCnt ║ 1 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_freeCnt ║ 66 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_freeData ║ 8124 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_reservedCnt ║ 0 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_lsn ║ (36:47578:1) ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_xactReserved ║ 0 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_xdesId ║ (0:0) ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_ghostRecCnt ║ 0 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ m_tornBits ║ 0 ║ ║ PAGE HEADER: ║ Page @0x000002431A8A2000 ║ DB Frag ID ║ 1 ║ ║ PAGE HEADER: ║ Allocation Status ║ GAM (3:2) ║ ALLOCATED ║ ║ PAGE HEADER: ║ Allocation Status ║ SGAM (3:3) ║ NOT ALLOCATED ║ ║ PAGE HEADER: ║ Allocation Status ║ PFS (3:105144) ║ 0x40 ALLOCATED 0_PCT_FULL ║ ║ PAGE HEADER: ║ Allocation Status ║ DIFF (3:6) ║ NOT CHANGED ║ ║ PAGE HEADER: ║ Allocation Status ║ ML (3:7) ║ NOT MIN_LOGGED ║ ║ PAGE HEADER: ║ Slot 0 Offset 0x60 Length 8028 ║ Record Type ║ PRIMARY_RECORD ║ ║ PAGE HEADER: ║ Slot 0 Offset 0x60 Length 8028 ║ Record Attributes ║ NULL_BITMAP VARIABLE_COLUMNS ║ ║ PAGE HEADER: ║ Slot 0 Offset 0x60 Length 8028 ║ Record Size ║ 8028 ║ ╚══════════════╩════════════════════════════════╩═══════════════════════════════╩═══════════════════════════════╝
Best Answer
Your demo is being hit by a limitation of REPLICATE:
If I do this:
And then run your DMV query from above against dm_db_database_page_allocations, I get pages with a PageTypeDesc of
TEXT_MIX_PAGE.I can then run DBCC PAGE with trace flag 3604 enabled in order to see the details of that off-row page:
The output is large, but near the beginning you'll see:
And then, you know, a bunch of A's.