11

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                          ║
╚══════════════╩════════════════════════════════╩═══════════════════════════════╩═══════════════════════════════╝
10

Your demo is being hit by a limitation of REPLICATE:

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

If I do this:

INSERT INTO dbo.t (off_row_data) VALUES (REPLICATE(CAST('A' as varchar(max)), 20000));

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:

DBCC TRACEON (3604);
GO
DBCC PAGE (TestDB, 1, 20696 , 3) -- your page will be different :)

The output is large, but near the beginning you'll see:

Blob row at: Page (1:20696) Slot 0 Length: 3934 Type: 3 (DATA)

And then, you know, a bunch of A's.

  • 4
    Wouldn't it be nice, though, if there was a truncation warning, or something. – Max Vernon Sep 15 '18 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.